Oracle SQL GROUP BY "not a GROUP BY expression" help
You cannot SELECT any column that is not either an aggregate or computed from only the columns used in the GROUP BY clause.
However there are three ways to do it:
You can use analytic functions
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;
You can use a self join with a “greater than ” clause and detect your max this way
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
You can use a subquery
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 )
Probably this is the simplest way
SELECT id, other_id, date_value, valueFROM some_tableWHERE date_value in (SELECT MAX(date_value) from some_table GROUP BY other_id HAVING other_id in (1,2,3));