Oracle SQL: Show n rows for each id

Question

I have a table with 2 columns, ID and URLS, for each ID there are several URLS. I want to extract only 5 maximum URLS per ID. How do I go about doing this in Oracle SQL?

Answer 1

Try this one,

SELECT ID, URL
FROM
    (
        SELECT ID, URL,
               ROW_NUMBER() OVER (PARTITION BY ID ORDER BY URL DESC) RN
        FROM   tableName
    ) a
WHERE RN <= 5

SQLFiddle Demo