CODEWITHSUNDEEP
sqlpostgresql
Sameera K
Sameera K

Reputation: 1428

How to group by one column, aggregate by another column and get another column as result in postgresql?

This seems something simple, but couldn't find an answer for this question last few hours.

I have a table request_state, where "id" is primary key, it can have multiple entries with same state_id. I want to get the id after grouping by state_id using max datetime.

So I tried this, but it gives error "state_id" must appear in the GROUP BY clause or be used in an aggregate function

select id, state_id, max(datetime) 
    from request_state 
    group by id

but when I use following query, I get multiple entries with same state_id.

select id, state_id, max(datetime) 
    from request_state 
    group by id, state_id

My table:

id    state_id  date_time
cef       1       Jan 1

ter       1       Jan 2

ijk       1       Jan 3

uuu       2       Feb 1

rrr       2       Feb 2

This is what I want as my result,

id   state_id date_time 

__   ________ _________ 

ijk       1       Jan 3 

rrr       2       Feb 2

Upvotes: 2

Views: 1480

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated suqbuery :

select t.*
from table t
where date_time = (select max(date_time) from table t1 where t1.state_id = t.state_id);

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try this query:

select id, state_id, date_time from (
  select id, state_id, date_time,
         row_number() over (partition by state_id order by date_time desc) rn
  from tbl
) a where rn = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You seem to want:

select max(id) as id, state_id, max(datetime) 
from request_state 
group by state_id;

If you want the row where datetime is maximum for each state, then use distinct on:

select distinct on (state) rs.*
from request_state rs
order by state, datetime desc;

Upvotes: 1

Related Questions