Samuel Ondrek Samuel Ondrek - 26 days ago 8
SQL Question

Select rows with max value from groups grouped by two columns (PSQL)

I would like to select rows with max value from groups grouped by two columns.

shared_id age_range average_date
001 01-21 2016-10-25 22:00
001 01-21 2016-10-25 21:00
001 21-40 2016-10-25 20:00
001 21-40 2016-10-25 19:00
002 01-21 2016-10-25 18:00
002 01-21 2016-10-25 17:00
002 21-40 2016-10-25 16:00
002 21-40 2016-10-25 15:00
003 01-21 2016-10-25 14:00
003 01-21 2016-10-25 13:00
003 21-40 2016-10-25 12:00
003 21-40 2016-10-25 11:00


And the result should be:

shared_id age_range average_date
001 01-21 2016-10-25 22:00
001 21-40 2016-10-25 20:00
002 01-21 2016-10-25 18:00
002 21-40 2016-10-25 16:00
003 01-21 2016-10-25 14:00
003 21-40 2016-10-25 12:00


I tried to get result with
DISTINCT ON
and
max()
but got only many errors.

Answer
SELECT
    share_id
    ,age_range
    ,MAX(average_date) as average_date
FROM
    TableName
GROUP BY
    share_id
    ,age_range