PostgreSQL: Returning the most recent rows grouping by a foreign key column

Question

I have a table that looks similar to this:

credit
+---------+----------------+-------------+--------------+-------------------------+
| id (PK) | person_id (FK) | transaction | total credit |        date_time        |
+---------+----------------+-------------+--------------+-------------------------+
|     345 |              1 |       -1.00 |        34.50 | 2018-08-29 12:00:00.000 |
|     897 |              1 |        5.45 |        39.95 | 2018-08-29 12:34:00.000 |
|     378 |              2 |        0.01 |         0.01 | 2018-08-29 08:00:00.000 |
|     789 |              2 |       20.00 |        20.01 | 2018-08-29 09:00:00.000 |
+---------+----------------+-------------+--------------+-------------------------+

How would I write a query in Postgres to return only the most recent (by date_time DESC) row grouped by each unique person_id in the table, like this?

+---------+----------------+-------------+--------------+-------------------------+
| id (PK) | person_id (FK) | transaction | total credit |        date_time        |
+---------+----------------+-------------+--------------+-------------------------+
|     897 |              1 |        5.45 |        39.95 | 2018-08-29 12:34:00.000 |
|     789 |              2 |       20.00 |        20.01 | 2018-08-29 09:00:00.000 |
+---------+----------------+-------------+--------------+-------------------------+

Possible duplicate of Select first row in each GROUP BY group? — PM 77-1
– PM 77-1, Commented Aug 29, 2018 at 22:22

Gordon Linoff · Accepted Answer · 2018-08-29 22:21:01Z

2

Use distinct on:

select distinct on (person_id) t.*
from t
order by person_id, date_time desc

answered Aug 29, 2018 at 22:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D-Shih · Accepted Answer · 2018-08-29 22:21:30Z

2

You can try this. use ROW_NUMBER with window function to make row number split by person_id and order by date_time in subquery then get row number is 1

CREATE TABLE credit(
   id int,
   person_id int,
   transaction float,
   "total credit" float,
   date_time timestamp
);

INSERT INTO credit values (345,1 ,-1.00,34.50, '2018-08-29 12:00:00.000');
INSERT INTO credit values (897,1 ,5.45 ,39.95, '2018-08-29 12:34:00.000');
INSERT INTO credit values (378,2 ,0.01 ,0.01 , '2018-08-29 08:00:00.000');
INSERT INTO credit values (789,2 ,20.00,20.01, '2018-08-29 09:00:00.000');

Query 1:

SELECT * FROM (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY person_id  ORDER BY date_time DESC) rn
    FROM credit
) t1
where rn = 1

Results:

|  id | person_id | transaction | total credit |            date_time | rn |
|-----|-----------|-------------|--------------|----------------------|----|
| 897 |         1 |        5.45 |        39.95 | 2018-08-29T12:34:00Z |  1 |
| 789 |         2 |          20 |        20.01 | 2018-08-29T09:00:00Z |  1 |

answered Aug 29, 2018 at 22:21

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

2 Comments

ty2k Over a year ago

Thanks very much for the detailed example!

D-Shih Over a year ago

@ty2k No problem glad to help

Collectives™ on Stack Overflow

PostgreSQL: Returning the most recent rows grouping by a foreign key column

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related