'SQL Union with fixed number of rows as result
This is a simple representation of my student table.
| Student | Year | Class |
|---|---|---|
| 1 | 2010 | A |
| 2 | 2010 | A |
| 3 | 2010 | C |
| 4 | 2010 | B |
| 5 | 2011 | B |
| 6 | 2012 | B |
I want to compose a random group of 5 students. In the group there will be 2 students of year 2010 and 3 students of group B. These values are user specified.
A simple union doesn't always work, because of the duplicate values. Sometimes there are 4 students, sometimes there are 5 students. I always want 5 records.
SELECT * FROM (SELECT TOP 2 * FROM [Student] WHERE YEAR = 2010 ORDER BY NEWID()) A
UNION
SELECT * FROM (SELECT TOP 3 * FROM [Student] WHERE Class = 'B' ORDER BY NEWID()) B
Is this possible with a SQL query?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|