CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
gizarmaluke
gizarmaluke

Reputation: 55

PostgreSQL: Getting date associated with max value

I have a table in the form of

USER  |VALUE |DATE
-------------------------
user1 | 1337 | 2019-11-01
user1 | 1338 | 2019-03-28
user2 | 1234 | 2019-04-23
user2 | 4567 | 2019-05-05

and want to get the maximum value of every user with the associated date. When I do something like

SELECT max(VALUE) FROM table
GROUP BY USER

I have the problem that I can neither aggregate nor group by the DATE. How can I tell PostgreSQL that I just wanna have the date associated with the row where the max. value is?

Thanks!

Upvotes: 0

Views: 639

Answers (2)

S-Man
S-Man

Reputation: 23766

Click: demo:db<>fiddle

You can use DISTINCT ON: This gives you the first record of an ordered group. In your case the groups is your user column. This you have to order by value DESC to get the max value to be the first record. This will be taken - including the associated date value.

SELECT DISTINCT ON (user)
    *
FROM 
    mytable
ORDER BY user, value DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

If you want other aggregations in the query, you can also use array_agg() to mimic a "first" function:

select user, max(value), count(*),
       (array_agg(date order by value desc))[1] as date_at_max
from t
group by user;

Upvotes: 0

Related Questions