Search code examples
sqlpostgresqldatetimegreatest-n-per-group

How to exclude rows with similar columns?

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 ???
Solution

  • 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