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()
Advertisement
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;