CODEWITHSUNDEEP
sqlpostgresqljoingreatest-n-per-group
Hommer Smith
Hommer Smith

Reputation: 27852

Select rows where the last row of associated table has a specific value

I have two tables:

User (id, name)
UserEvent (id, user_id, name, date)

How can I get all the users where the last (ordered by date) UserEvent.name has a value of 'played'?

I wrote an example on SQLFiddle with some specific data: http://sqlfiddle.com/#!9/b76e24 - For this scenario I would just get 'Mery' from table User, because even though 'John' has associated events name of the last one is not 'played'.

Upvotes: 0

Views: 72

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

This is probably fastest:

SELECT u.*
FROM   usr u  -- avoiding "User" as table name
JOIN   LATERAL (
   SELECT name
   FROM   userevent
   WHERE  user_id = u.id
   ORDER  BY date DESC NULLS LAST
   LIMIT  1
   ) ue ON ue.name = 'played';

LATERAL requires Postgres 9.3+:

Or you could use DISTINCT ON (faster for few rows per user):

SELECT u.*
FROM   usr u  -- avoiding "User" as table name
JOIN   (
   SELECT DISTINCT ON (user_id)
          user_id, name
   FROM   userevent
   ORDER  BY user_id, date DESC NULLS LAST
   ) ue ON ue.user_id = u.id
       AND ue.name = 'played';

Details for DISTINCT ON:

SQL Fiddle with valid test case.

If date is defined NOT NULL, you don't need NULLS LAST. (Neither in the index below.)

Key to read performance for both but especially the first query is a matching multicolumn index:

CREATE INDEX userevent_foo_idx ON userevent (user_id, date DESC NULLS LAST, name);

Aside: Never use reserved words as identifiers.

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

Here it is:

First i get the MAX ID from each user and join it to the ROW with this ID to test if the status are 'played' if so, i get the username of them.

SELECT
  ids.*,
  u.name,
  ue.*
FROM (
  SELECt max(id) AS id from UserEvent
  GROUP by  user_id
) as ids
LEFT JOIN UserEvent ue ON ue.id = ids.id
LEFT JOIN User u ON u.id  = ue.user_id
WHERE ue.name = 'played';

Upvotes: 0

Rabbit
Rabbit

Reputation: 507

Return the max date from user event grouping by user id. Take that result set and join it back to user event by the user id and max date and filter for just the played records.

Upvotes: 0

Related Questions