Return first x results from each group in SQL quer

问题:

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

回答1:

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