Postgres - group rows by user, return one row per user in each group

user_5610949 0
Viewed 420

I have a purchases table:

-----------------
user_id | amount
-----------------
1       | 12                     
1       | 4                     
1       | 8     
2       | 23                    
2       | 45                    
2       | 7

I want a query that will return one row per user_id, but the row that I want for each user_id is where the amount is the smallest per user_id. So I should get as my result set:

-----------------
user_id | amount
-----------------                   
1       | 4                                        
2       | 7

Using DISTINCT on the user_id column ensures I don't get duplicate user's, but I don't know how to make it so that returns the user row with the fewest amount.

postgresql sql
2 Answers

You can use distinct on:

select distinct on (user) t.*
from t
order by user, amount;

Note: If you just want the smallest amount, then group by would be the typical solution:

select user, min(amount)
from t
group by user;

Distinct on is a convenient Postgres extension that makes it easy to get one row per group -- and it often performs better than other methods.

user_11440350

If your requirement requires ouput of a row that equates to the smallest amount, e.g. the table includes a transaction date and you need this in the output, then a convenient method is to use row_number() over() to select the wanted rows. e.g.

CREATE TABLE mytable(
   user_id  INTEGER  NOT NULL
  ,amount   INTEGER  NOT NULL
  ,trandate DATE   NOT NULL
);
INSERT INTO mytable(user_id,amount,trandate) VALUES (1,12,'2020-09-12');
INSERT INTO mytable(user_id,amount,trandate) VALUES (1,4,'2020-10-02');
INSERT INTO mytable(user_id,amount,trandate) VALUES (1,8,'2020-11-12');
INSERT INTO mytable(user_id,amount,trandate) VALUES (2,23,'2020-12-02');
INSERT INTO mytable(user_id,amount,trandate) VALUES (2,45,'2021-01-12');
INSERT INTO mytable(user_id,amount,trandate) VALUES (2,7,'2021-02-02');

select
user_id, amount, trandate
from (
    select user_id, amount, trandate
        , row_number() over(partition by user_id order by amount) as rn
    from mytable
    ) t
where rn = 1

result:

+---------+--------+------------+
| user_id | amount |  trandate  |
+---------+--------+------------+
|       1 |      4 | 2020-10-02 |
|       2 |      7 | 2021-02-02 |
+---------+--------+------------+

demonstartion of this at db<>fiddle here

user_20677530
Related