Joe Celko's SQL for Smarties, 3rd Edition

27.4. Picking a Representative Subset

This problem and solution for it are due to Ross Presser. The problem is to find a subset of rows such that each value in each of two columns appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a club_name column and an ifc column; I want a set of samples that contains at least one of each club_name and at least one of each ifc, but no more than necessary.

CREATE TABLE Memberships (member_id INTEGER NOT NULL PRIMARY KEY, club_name CHAR(7) NOT NULL, ifc CHAR(4) NOT NULL); CREATE TABLE Samples (member_id INTEGER NOT NULL PRIMARY KEY, club_name CHAR(7) NOT NULL, ifc CHAR(4) NOT NULL); INSERT INTO Memberships VALUES (6401715, 'aarprat', 'ic17'), (1058337, ...