CODEWITHSUNDEEP
sqlgreatest-n-per-grouppostgresql
John
John

Reputation: 79

Return first x results from each group in SQL query

I have a very complicated SQL query that returns a few hundred results broken down as follows:

    user 1    id: ##      stuff
    user 1    id: ##      stuff
    user 1    id: ##      stuff
     ....
    user 2    id: ###     stuff
    user 2    id: ###     stuff
    user 2    id: ###     stuff
    ....

for about 6 users. It's already sorted exactly the way I want, I just want the first 5 for each user. Is there an easy way to do this? I'm using PostgreSQL 8.4 btw, and my SQL knowledge is limited so try to make any explanations not too complicated if possible :D

Upvotes: 1

Views: 233

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Something like this should do it.

SELECT t.user, t.id, t.stuff
    FROM (SELECT user, id, stuff,
                 ROW_NUMBER() OVER (PARTITION BY user ORDER BY id) AS RowNum
              FROM YourTable) t
    WHERE t.RowNum <= 5

Upvotes: 3

Related Questions