Db2 - Returning the top 5 of each category

问题:

I want to be able to return 5 menuitem per menu. Here are the tables

menus
-------
menuid int()
profileName varchar(35)

menuitems
-----------
itemid int()
name varchar(40)
menuid int()

I do see a solution for MySQL in this thread - mySQL Returning the top 5 of each category, looking for similar solution for DB2. Any suggestion is great appreciated.

回答1:

something like:

select ...
from (
    select ..., row_number() over ( partition by m.menuid
                                    order by ? ) as rn 
    from menus m
    join menuitems mi
        m.menuid = mi.menuid
)
where rn <= 5;

Troels Arvin has a comparision of different DBMS at:

http://troels.arvin.dk/db/rdbms/

Amongst other things quota queries (such as the one you are asking) are discussed