我有一张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