Limiting SQL results in Rails using Postgres -

i have app includes music charts showcase top tracks (it shows top 10).

however, i'm trying limit charts particular user cannot have more 2 tracks on top charts @ same time. if artist have 4 of top 10 slots, top 2 tracks artist shown (and #11 , #12 on list bumped 2 spots each, presuming aren't artist of course).

so, let's top charts section right now:

1. song artist a
2. song b artist b
3. song c artist a
4. song d artist c
5. song e artist d
6. song f artist e
7. song g artist f
8. song h artist a
9. song artist a
10. song j artist g

i limit sql results #8 , #9 aren't included (because 2 tracks per artist allowed in query results) , list instead become:

1. song artist a
2. song b artist b
3. song c artist a
4. song d artist c
5. song e artist d
6. song f artist e
7. song g artist f
8. song j artist g
9. song k artist h (previously #11)
10. song l artist (previously #12)

fyi, i'm using postgres, , have right now. counts plays per track in last 14 days generate top 10 list. modify desired limitation noted above.

def self.top_tracks_past14(max=3)   track.find_by_sql(["select coalesce(sum(plays.clicks), 0), tracks.*     tracks     left join plays     on tracks.id = plays.track_id     , 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 tracks.id     order 1 desc limit ?", max]) end 

select trackid, userid, totalclicks  ( select *,  row_number() over(partition userid order totalclicks desc) rn  ( select coalesce(sum(plays.clicks), 0) totalclicks,plays.track_id trackid, users.id userid tracks left join plays on tracks.id = plays.track_id , 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 plays.track_id, users.id   ) t ) t1 t1.rn <= 2 order 1 desc limit 10;  

you can use row_number function select 2 rows per user amongst top tracks.

edit: per op's request

all columns tracks, albums, users, plays available in outer query. sure select columns need these tables if need exclude calculated rn selection.

def self.top_tracks_past14(max=3) track.find_by_sql(["select t1.trackid, t1.userid, t1.totalclicks  ( select t.trackid, t.userid, t.totalclicks,  row_number() over(partition t.userid order t.totalclicks desc) rn  ( select coalesce(sum(plays.clicks), 0) totalclicks,plays.track_id trackid ,users.id userid tracks left join plays on tracks.id = plays.track_id , 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 plays.track_id, users.id   ) t ) t1 t1.rn <= 2 order t1.totalclicks desc limit ?", max]) end