How to Choose Two Persons from Each Department As a Pair to Play Games?

As the China's Lunar new year is coming, nearly all the enterprises will hold 2012 year end party to celebrate last year's achievements, and look into a new start in 2013. In order to make the party more interesting, each department will play games or performance, how to select the person, with one male and one female from each department with SQL?

WITH A AS
(SELECT name,dept,
row_number() OVER(PARTITION BY dept ORDER BY 1) sequence_no.
FROM employee WHERE gender='male'),
B AS
(SELECT name,dept,
row_number() OVER(PARTITION BY dept ORDER BY 1) sequence_no.
FROM employee WHERE gender='female')
SELECT name,dept FROM A
WHERE dept IN ( SELECT DISTINCT dept FROM B ) AND sequence_no.=1
UNION ALL
SELECT name,dept FROM B
WHERE dept IN ( SELECT DISTINCT dept FROM A ) AND sequence_no.=1

But, I have another solution for you that's much more simple. Download a tool, esProc. Do it like this:

Popular White Paper On This Topic

I have done this using an oracle analytic function to add sequentail numbers to employees within department and gender. There are similar functions in most databases. Alternatively one could make a subquery for each department and gender and use a row number function.

I also used an oracle packaged function to get random numbers, typically this is available in most databases. Alternatively you can make your own stored procedure to provide random numbers.

INSERT INTO emp
SELECT *
FROM (
SELECT 'Finance' AS dept, 'Mike' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Bill' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'John' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Mary' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Beth' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Jane' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Fred' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Stan' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Lulu' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Ian' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Wiliam' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Bob' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Ella' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Francine' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Finance' AS dept, 'Molly' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Matt' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Mark' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Ava' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Julie' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Edgar' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Stewaet' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Denise' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Marget' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Estel' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Will' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Antony' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Marketing' AS dept, 'Ginger' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Axel' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Alex' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Curt' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Mary' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Betty' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Camilla' AS emp, 'F' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Donald' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Evan' AS emp, 'M' AS gender FROM dual UNION ALL
SELECT 'Sales' AS dept, 'Faith' AS emp, 'F' AS gender FROM dual
) ;

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.