Home SQL - Selecting last event for each user that matches a given criteria
Reply: 1

SQL - Selecting last event for each user that matches a given criteria

aoven
1#
aoven Published in 2017-09-13 16:26:16Z

I've prepared the following simplified example of my real case: SQL Fiddle.

As evident, I've come up with two ways to get to the same information, which is: the set of latest events for each user given some additional constraints on event type.

Both ways get me the information about user and time occurred of the latest event. However, I need to know the id and type of that event as well.

How would I go about getting that?

Ideally, I'd like the solution to work for any SQL database, not just PostgreSQL.

Gordon Linoff
2#
Gordon Linoff Reply to 2017-09-13 16:28:59Z

In Postgres, I would use distinct on:

select distinct on (username) e.*
from events e
where type > 0
order by username, "time" desc;

(This does not order by time for all users; that would require another subquery.)

For a more general solution, I would use row_numbe():

select e.*
from (select e.*,
             row_number() over (partition by username order by "time" desc) as seqnum
      from events e
     ) e
where seqnum = 1
order by "time" desc
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300816 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO