CODEWITHSUNDEEP
databasepostgresqlgreatest-n-per-group
ChrisHigs
ChrisHigs

Reputation: 63

Postgres get first item of a group by

I've seen similar questions to this but none seem to quite cover the query I'm trying to build.

I've got a database of request logs to a server, which includes inbound and outbound connections, and I'm trying to get the first item in the log for each request.

The database looks a little like this: id, requestid, timestamp, message

I want to group by requestid, but get the lowest id from each requestid, which will be the first log item for a request.

I've found similar requests to group by requestid then order by requestid but that doesn't give what I want. I tried adding orderby requestid, id, but that also gives id 2 as the first item that is returned.

This is the closest I've come: select id, requestid from logs where id in (select distinct on (requestid) id from (select id, requestid from logs order by id) as foo) order by id limit 5; but the first item returned is id 2, not id 1.

Any tips for this would be really helpful, thanks.

Upvotes: 4

Views: 2124

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

you could use an inner join on group by request id 

select  id, requestid, timestamp, message
from logs 
inner join  (
    select min(id) as min_id , requestid 
    from logs 
    group by requestid
) t on t.min_id = logs.id and t.requestid = logs.requestid

Upvotes: 3

Related Questions