Oracle SQL GROUP BY "не выражение GROUP BY"

У меня есть таблица 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

Однако это исключает исключение "not a GROUP BY expression". Я попытался добавить все остальные поля (т.е. id, value) в предложение GROUP BY, но это просто возвращает все, точно так же, как если бы не было предложения GROUP BY. (Ну, это тоже имеет смысл.)

Итак... Я читаю руководство Oracle SQL, и все, что я могу найти, - это несколько примеров, связанных только с запросами с двумя или тремя столбцами и некоторыми функциями группировки i-have-never-seen-before. Как мне пойти и вернуться

+--------+----------+---------------------+-------+
| 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)? Спасибо.

Ответы

Ответ 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

Ответ 2

Вы не можете выбрать любой столбец, который не является агрегатом или не вычисляется только из столбцов, используемых в предложении GROUP BY.

Однако есть три способа сделать это:

  • Вы можете использовать аналитические функции

    SELECT id, other_id, date_value, value
  FROM ( SELECT id, other_id, date_value, MAX(date_value) OVER (partition by other_id) max_date, value
           FROM some_table )
 WHERE max_date = date_value;

  • Вы можете использовать самостоятельное объединение с предложением "больше, чем" и определить свой максимальный путь

    SELECT t1.id, t1.other_id, t1.date_value, t1.value
  FROM some_table t1
  LEFT OUTER JOIN some_table t2
               ON ( t1.other_id = t2.other_id AND t2.date_value > t1.date_value )
 WHERE t2.other_id IS NULL

  • Вы можете использовать подзапрос

      WITH max AS ( SELECT other_id, MAX(date_value) FROM some_table GROUP BY other_id )
SELECT id, other_id, date_value, value
  FROM some_table
 WHERE ( other_id, date_value ) IN ( SELECT * FROM max )