Oracle SQL GROUP BY "not a GROUP BY expression" help

Question

I have a table some_table like

+--------+----------+---------------------+-------+
| 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   |
| ...    | ...      | ...                 | ...   |
+--------+----------+---------------------+-------+

And I want the latest records for the other_id 1, 2, and 3. The obvious query I came up with is

SELECT id, other_id, MAX(date_value), value
  FROM some_table 
 WHERE other_id IN (1, 2, 3) 
 GROUP BY other_id

However it spits a "not a GROUP BY expression" exception. I tried adding all other fields (i.e. id, value) in the GROUP BY clause, but that just returns everything, exactly as if there was no GROUP BY clause. (Well, it does make sense too.)

So... I'm reading the Oracle SQL manual, and all I can find are some examples involving only queries with two or three columns and some i-have-never-seen-before grouping functions. How do I go and return

+--------+----------+---------------------+-------+
| 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   |
+--------+----------+---------------------+-------+

(the latest entries for each other_id) ? Thank you.

Answer 1

 select id, other_id, date_value, value from
 (
   SELECT id, other_id, date_value, value, 
   ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
   FROM some_table 
   WHERE other_id IN (1, 2, 3) 
 )
 where r = 1