How to exclude rows with similar columns?

Question

There is a table:

| date        | action | issue_id |
| 2020-12-14  | close  | 1        |
| 2020-12-15  | close  | 1        |
| 2020-12-16  | close  | 1        |
| 2020-12-14  | close  | 2        |

How can I select only the last row with an action == close for each issue_id in one query?

SELECT action, issue_id
FROM table
WHERE action = 'close'
AND ???

All columns `NOT NULL`? – Erwin Brandstetter Dec 14 '20 at 17:19 — Erwin Brandstetter, Dec 14 '20 at 17:19
@ErwinBrandstetter no, they might be null – Konstantin Komissarov Dec 14 '20 at 17:20 — Konstantin Komissarov, Dec 14 '20 at 17:20

score 1 · Accepted Answer · answered Dec 14 '20 at 17:02

For these three columns only, aggregation is sufficient:

select issue_id, max(date) as date
from mytable 
where action = 'close'
group by issue_id

If you have more columns that you need to display, then use distinct on:

select distinct on (issue_id) t.*
from mytable t
where action = 'close'
order by issue_id, date desc

score 1 · Answer 2 · answered Dec 14 '20 at 17:02

1

You can use distinct on :

select distinct on (issue_id) t.*
from table t
where action = 'close'
order by issue_id, date desc;

answered Dec 14 '20 at 17:02

Gordon Linoff

1,242,037
58
646
786

Erwin Brandstetter · Answer 3 · 2020-12-14T17:31:25.320

... columns might be null.

So be sure to use DESC NULLS LAST to avoid surprising results:

SELECT DISTINCT ON (issue_id) *
FROM   tbl
WHERE  action = 'close'
ORDER  BY issue_id, date DESC NULLS LAST;

And you may want to append another unique expression (like tbl_id) to the ORDER BY list to break ties among equal dates if that can happen. Else you get an arbitrary pick that can change with every execution.

See:

How to exclude rows with similar columns?

3 Answers3