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

Question

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'.

Answer 1

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+:

• What is the difference between LATERAL and a subquery in PostgreSQL?

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:

• Select first row in each GROUP BY group?

SQL Fiddle with valid test case.

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

• PostgreSQL sort by datetime asc, null first?

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);

• Optimize GROUP BY query to retrieve latest record per user

Aside: Never use reserved words as identifiers.

Answer 2

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.

Answer 3

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';