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;