I have a table that stores employees (id, name, and gender). I need to randomly get two men and two women.
CREATE TABLE employees
(
id INT,
name VARCHAR (10),
gender VARCHAR (1),
);
INSERT INTO employees VALUES (1, 'Mary', 'F');
INSERT INTO employees VALUES (2, 'Jake', 'M');
INSERT INTO employees VALUES (3, 'Ryan', 'M');
INSERT INTO employees VALUES (4, 'Lola', 'F');
INSERT INTO employees VALUES (5, 'Dina', 'F');
INSERT INTO employees VALUES (6, 'Paul', 'M');
INSERT INTO employees VALUES (7, 'Tina', 'F');
INSERT INTO employees VALUES (8, 'John', 'M');
My attempt is the following:
SELECT TOP 2 *
FROM employees
WHERE gender = 'F'
ORDER BY NEWID()
UNION
SELECT TOP 2 *
FROM employees
WHERE gender = 'M'
ORDER BY NEWID()
But it doesn't work since I can't put two order by
in the same query.
CodePudding user response:
Why not just use row_number()
? One method without a subquery is:
SELECT TOP (4) WITH TIES e.*
FROM employees
WHERE gender IN ('M', 'F')
ORDER BY ROW_NUMBER() OVER (PARTITION BY gender ORDER BY newid());
This is slightly less performant than using ROW_NUMBER()
in a subquery.
Or, a fun method would use APPLY
:
select e.*
from (values ('M'), ('F')) v(gender) cross apply
(select top (2) e.*
from employees e
where e.gender = v.gender
order by newid()
) e;
CodePudding user response:
You cannot put an ORDER BY
in the combinable query (the first one) of the UNION
. However, you can use ORDER BY
if you convert each one into a table expression.
For example:
select *
from (
SELECT TOP 2 *
FROM employees
WHERE gender = 'F'
ORDER BY newid()
) x
UNION ALL
select *
from (
SELECT TOP 2 *
FROM employees
WHERE gender = 'M'
ORDER BY newid()
) y
Result:
id name gender
--- ----- ------
5 Dina F
4 Lola F
2 Jake M
3 Ryan M
See running example at SQL Fiddle.