3

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.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
ChrisHigs
  • 53
  • 1
  • 7
  • 1
    https://stackoverflow.com/q/3800551/905902 – wildplasser Nov 18 '18 at 16:12
  • I looked at that post and tried the Postgres answer in the second answer but that didn't work as I wanted. I've just tried the partition answer in the first, though, and that did work – ChrisHigs Nov 18 '18 at 17:37

1 Answers1

2

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
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks. That worked. So did the partition answer in the post that was linked to in the comment to my first post. Got two different ways to do it now. Thanks – ChrisHigs Nov 18 '18 at 17:38