CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Urchboy
Urchboy

Reputation: 865

Postgres Query: Select the row with maximum value on a column from two distinct rows

 user_id | sum  | app_id | app_count
---------+------+--------+-----------
       1 |  100 |      3 |         1
       2 |  300 |      2 |         1
       4 | 1100 |      1 |         2
       4 | 1100 |      4 |         1

How do I write the query such that distinct user_id is selected based on the rank of app_count?

Here is the result I want:

     user_id | sum  | app_id | app_count
    ---------+------+--------+-----------
           1 |  100 |      3 |         1
           2 |  300 |      2 |         1
           4 | 1100 |      1 |         2

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

In Postgres, you would use distinct on:

select distinct on (user_id) t.*
from t
order by user_id, app_count desc;

Upvotes: 1

Related Questions