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

Get Joe Celko's SQL for Smarties, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.