20.5. Grouping into Pairs
The idea is easier to show than to say: you are given a table of people and want to generate a list that has pairs of men and women for a dinner party.
CREATE TABLE People (name VARCHAR(15) NOT NULL, gender INTEGER DEFAULT 1 NOT NULL -- iso gender codes CHECK (gender IN (1, 2)); INSERT INTO People VALUES ('Bob', 1), ('Ed', 1), ('Joe', 1), ('Dave', 1); INSERT INTO People VALUES ('Sue', 2), ('Joan', 2), ('Kate', 2), ('Mary', 2), ('Petra', 2), ('Nancy', 2);
A solution from Steve Kass:
SELECT A.name, A.gender, COUNT(*) AS rank FROM People AS A, People AS B WHERE B.name <= A.name AND B.gender = A.gender GROUP BY A.name, A.gender;
For each name, COUNT(*) is the alphabetical “rank” of the name in the table, counting only names ...
Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.