CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Andrew
Andrew

Reputation: 472

Limit query results

I have an app that includes music charts to showcase the top tracks (it shows the top 10).

However, I'm trying to limit the charts so that any particular user cannot have more than one track on the top charts at the same time.

If you need any more info, just let me know.

Upvotes: 1

Views: 183

Answers (1)

Simo Kivistö
Simo Kivistö

Reputation: 4463

You can use the row_number() function which gives a running number that resets when the user id changes. Then you can use that in a WHERE clause to create a per-user-limit: 

SELECT * FROM (
    SELECT COALESCE(sum(plays.clicks), 0),
        row_number() OVER (PARTITION BY users.id ORDER BY COALESCE(sum(plays.clicks), 0) DESC),
        users.id AS user_id,
        tracks.*
        FROM tracks
            JOIN plays
            ON tracks.id = plays.track_id
            AND plays.created_at > now() - interval '14 days'
            INNER JOIN albums
            ON tracks.album_id = albums.id
            INNER JOIN users
            ON albums.user_id = users.id
            GROUP BY users.id, tracks.id
            ORDER BY 1 desc) sq1
    WHERE row_number <= 2 LIMIT 10;

Upvotes: 1

Related Questions