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

 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

CodePudding user response：

In Postgres, you would use distinct on:

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