1

I am using PostgreSQL 9.4.

I have workout lists and an associated join table enumerating which workouts are in that list.

I'd like to return the 5 most recent results for each workout in the list.

The below returns every result, and if I append LIMIT 5, I only get a total of 5 results, rather than 5 per workout. How do I do this in SQL? 

SELECT "results".* FROM "results" 
WHERE "results"."user_id" = 1
  AND workout_id IN (SELECT workout_id FROM workout_list_join_table
                     WHERE workout_list_id = 5) 
ORDER BY "results"."done_at" DESC
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ben Smith
  • 851
  • 2
  • 10
  • 22
  • Best performance for this kind of query: http://stackoverflow.com/questions/25536422/optimize-group-by-query-to-retrieve-latest-record-per-user/25536748#25536748 – Erwin Brandstetter Mar 07 '15 at 18:03

2 Answers2

3

You can do this with a window function:

select *
from (
    SELECT results.*, 
           row_number() over (partition by workout_id order by done_at desc) as rn
    FROM results 
    WHERE results.user_id = 1
      AND workout_id IN (SELECT workout_id 
                         FROM workout_list_join_table 
                         WHERE workout_list_id = 5) 
) t
where rn <= 5
ORDER BY done_at DESC;
  • 2
    It works, but it might become slow on large result sets. In 9.4 you could use LATERAL to limit the amount of data the database has to process – Frank Heikens Mar 01 '15 at 08:55
2

As of version 9.4 you could also use LATERAL:

SELECT  r.*
FROM    workout_list_join_table w
    JOIN LATERAL(SELECT * FROM results r WHERE r.workout_id = w.workout_id AND user_id = 1 ORDER BY r.done_at DESC LIMIT 5) r ON (true)
WHERE   workout_list_id = 5
ORDER BY 
    r.done_at DESC;

When using large tables, this could be much faster than a window function because of a better query plan. LATERAL doesn't need all data, it can limit the amount of rows within the subquery. Check these slides from Markus Winand for more information: Modern SQL in PostgreSQL

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135