Union two queries ordered by newid-CodePudding

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.