db2 select x random rows for a given id

问题:

If I have two columns - an ID field and a score field that can take 10 possible values, how can I select 5 random rows per ID? I know I can select 5 random rows from a table by using the following:

select *, rand() as idx
from mytable 
order by idx fetch first 5 rows only

but how about 5 rows per ID?

回答1:

You can do this using row_number():

select t.*
from (select t.*,
             row_number() over (partition by idx order by rand()) as seqnum
      from mytable t
     ) t
where seqnum <= 5;