Joe Celko's SQL for Smarties, 3rd Edition

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 ...