Home > Net >  Union two queries ordered by newid
Union two queries ordered by newid

Time:09-26

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.

  • Related