Skip to content
Advertisement

How to use LIMIT to sample rows dynamically

I have a table as follows:

SampleReq Group ID
2 1 _001
2 1 _002
2 1 _003
1 2 _004
1 2 _005
1 2 _006

I want my query to IDs based on the column SampleReq, resulting in the following output:

Group ID
1 _001
1 _003
2 _006

The query should pick any 2 IDs from group 1, any 1 IDs from group 2 and so on (depending on the column SampleReq).

I tried the query using LIMIT, but this gives me an error saying column names cannot be parsed to a limit.

SELECT Group, ID
FROM Table
LIMIT SampleReq
ORDER BY RAND()

Answer

One method is row_number():

select t.*
from (select t.*,
             row_number() over (partition by samplereq order by random()) as seqnum
      from t
     ) t
where seqnum <= 2 and id = 1 or
      seqnum <= 1 and id = 2;