Postgres select all columns but group by one column

Question

I have a simple table with a unit_id oid, time timestamp, diag bytea. The primary key is a combination of both time and unit_id.

The idea behind this query is to get the latest row (largest timestamp) for each unique unit_id. However the rows for each unit_id with the latest time are not always returned.

I really want to group by just the unit_id, but postgres makes me use diag also, since I am selecting that.

SELECT DISTINCT ON(unit_id) max(time) as time, diag, unit_id 
FROM diagnostics.unit_diag_history  
GROUP BY unit_id, diag

mu is too short · Accepted Answer · 2012-08-07 08:08:02Z

32

Any time you start thinking that you want a localized GROUP BY you should start thinking about window functions instead.

I think you're after something like this:

select unit_id, time, diag
from (
    select unit_id, time, diag,
           rank() over (partition by unit_id order by time desc) as rank
    from diagnostics.unit_diag_history
) as dt
where rank = 1

You might want to add something to the ORDER BY to consistently break ties as well but that wouldn't alter the overall technique.

edited Aug 7, 2012 at 8:08

answered Sep 30, 2011 at 6:29

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

metdos Over a year ago

Can we do this without using sub select ?

mu is too short Over a year ago

@metdos: I can't think of anything off the top of my head but there might be a way. What's wrong with derived tables?

SingleNegationElimination · Accepted Answer · 2011-09-30 06:26:27Z

28

You can join the grouped select with the original table:

SELECT d.time, d.diag, d.unit_id
FROM(
    SELECT unit_id, max(time) as max_time
    FROM diagnostics.unit_diag_history
    GROUP BY unit_id
) s JOIN diagnostics.unit_diag_history d
ON s.unit_id = d.unit_id AND s.max_time = d.time

answered Sep 30, 2011 at 6:26

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

2 Comments

Adrian Over a year ago

This is the only answer I have found in several days of Googling that helped me in any way (coming from MySQL).

Piotr Sobuś Over a year ago

Very old comment but still very helpful :)

Collectives™ on Stack Overflow

Postgres select all columns but group by one column

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related