Select a specific row from a table with duplicated entries based on one field

Question

I have a table which holds data in the following format, however I would like to be able to create a query that checks whether the reference number is duplicated and only return the entry with the latest date_issued.

ref_no          name      gender place      date_issued

xgb/358632/p    John Smith  M   London      02.08.2016

Xgb/358632/p    John Smith  M   London      14.06.2017

Rtu/638932/k    Jane Doe    F   Birmingham  04.09.2017

The result from the query should be; 

ref_no          name    gender  place       date_issued

Xgb/358632/p    John Smith  M   London      14.06.2017

Rtu/638932/k    Jane Doe    F   Birmingham  04.09.2017

Is there a fairly straightforward solution for this?

Possible duplicate of [PostgreSQL Selecting Most Recent Entry for a Given ID](https://stackoverflow.com/questions/9472731/postgresql-selecting-most-recent-entry-for-a-given-id) — pepperjack, Jul 22 '18 at 09:41

Jasen · Answer 1 · 2018-07-22T10:35:47.743

0

assuming the date column is type date or timestamp

select distinct on(ref_no) * from tablename order by refno,date desc;

this works beacuse distinct on supresses rows with duplicates of the expression in parenthese.

edited Jul 22 '18 at 10:35

answered Jul 22 '18 at 10:21

Jasen

11,837
2
30
48

Select a specific row from a table with duplicated entries based on one field

1 Answers1