选择最后一组数据

Question

我有一张这样的桌子

测量

id    object_id   generated_at (v)    
110   4           2012-11-13 10:29:00   
109   4           2012-11-13 10:28:00   
108   4           2012-11-13 10:27:00
107   3           2012-11-13 10:26:00
106   3           2012-11-13 10:25:00
105   4           2012-11-13 10:24:00
104   4           2012-11-13 10:23:00
103   1           2012-11-13 10:22:00
102   1           2012-11-13 10:21:00
...

按generated_at列排序。

我的问题是:有没有能力选择

id    object_id   generated_at (v)    
110   4           2012-11-13 10:29:00   
109   4           2012-11-13 10:28:00   
108   4           2012-11-13 10:27:00

最后一组(由generated_at排序)相同object_id的测量？

它可以是rails中用于度量模型的示例，或者只是SQL查询，谢谢您的帮助！

Answer 1

你最初的问题显然是想要对象在最近的组中，而不是所有的对象。为此，需要计算其他对象的最大对象id和最大生成时间：

select *
from t cross join
     (select max(object_id) maxobject_id from t) maxt
where object_id = maxt.maxobject_id and
      generated_at > (select max(generated_at) from t where object_id <> maxt.maxobject_id)

Answer 2

我相信这会成功的：SELECT * FROM table WHERE object_id = (SELECT MAX(object_id) FROM table)

Answer 3

以下查询将获得结果：

select * from Measurements
left join Measurements as M2
on
(M2.generated_at > Measurements.generated_at and M2.object_id <> Measurements.object_id)
where 
M2.id IS NULL
and
Measurements.object_id = 
(select object_id from Measurements where generated_at = (select MAX(generated_at) from Measurements))