CODEWITHSUNDEEP
postgresqlgroup-byaggregate
Alberto
Alberto

Reputation: 12909

Get entire record with max field for each group

There are a lot of answers about this problem, but none of them retrieves the entire record, but only the ID... and I need the whole record.

So, I have a table status_changes that is composed of 4 columns:

  1. issue_id : the issue the change refers to
  2. id: the id of the change, just a SERIAL
  3. status_from and status_to that are infact the status that the issue had before, and the status that the issue got then
  4. when that is a timestamp of when this happened

Nothing too crazy, but now, I would like to have the "most recent status_change" for each issue.

I tried something like:

select id
from change 
group by issue_id
having when = max(when)

But this has obviously 2 big problems:

  1. select contains fields that are not in the group by 2 having can't contains aggregate function in this way

I thought of "ordering every group by when and using something like top(1), but I can't figure out how to do it...

Upvotes: 0

Views: 19

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246298

Use PostgreSQL's DISTINCT ON:

SELECT DISTINCT ON (issue_id)
       id, issue_id, status_from, statue_to, when
FROM change
ORDER BY issue_id, when DESC;

This will return the first result (the one with the greatest when) for each issue.

Upvotes: 1

Related Questions