我有一张table some_table
+--------+----------+---------------------+-------+ | id | other_id | date_value | value | +--------+----------+---------------------+-------+ | 1 | 1 | 2011-04-20 21:03:05 | 104 | | 2 | 2 | 2011-04-20 21:03:04 | 229 | | 3 | 3 | 2011-04-20 21:03:03 | 130 | | 4 | 1 | 2011-04-20 21:02:09 | 97 | | 5 | 2 | 2011-04-20 21:02:08 | 65 | | 6 | 3 | 2011-04-20 21:02:07 | 101 | | ... | ... | ... | ... | +--------+----------+---------------------+-------+
我想要最新的记录为other_id 1,2和3.我想出的明显的查询是
SELECT id,other_id,MAX(date_value),value FROM some_table WHERE other_id IN (1,2,3) GROUP BY other_id
但是它会吐出“不是GROUP BY表达式”异常.我尝试在GROUP BY子句中添加所有其他字段(即id,value),但是只返回所有内容,就好像没有GROUP BY子句一样. (嗯,这也有道理.)
所以…我正在阅读Oracle sql手册,所有我可以找到的是一些例子,仅涉及到两列或三列的查询和一些我以前从未看过的分组功能.我该怎么去回去
+--------+----------+---------------------+-------+ | id | other_id | date_value | value | +--------+----------+---------------------+-------+ | 1 | 1 | 2011-04-20 21:03:05 | 104 | | 2 | 2 | 2011-04-20 21:03:04 | 229 | | 3 | 3 | 2011-04-20 21:03:03 | 130 | +--------+----------+---------------------+-------+
(每个other_id的最新条目)？谢谢.
select id,date_value,value from ( SELECT id,value,ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r FROM some_table WHERE other_id IN (1,3) ) where r = 1
