The "first past the post election" query problem
- by MPelletier
This problem may seem like school work, but it isn't. At best it is self-imposed school work. I encourage any teachers to take is as an example if they wish.
"First past the post" elections are single-round, meaning that whoever gets the most votes win, no second rounds.
Suppose a table for an election.
CREATE TABLE ElectionResults (
DistrictHnd INTEGER NOT NULL,
PartyHnd INTEGER NOT NULL,
CandidateName VARCHAR2(100) NOT NULL,
TotalVotes INTEGER NOT NULL,
PRIMARY KEY DistrictHnd, PartyHnd);
The table has two foreign keys: DistrictHnd points to a District table (lists all the different electoral districts) and PartyHnd points to a Party table (lists all the different political parties). I won't bother with other tables here, joining them is trivial. This is just a wee bit of context.
The question: What SQL query will return a table listing the DistrictHnd, PartyHnd, CandidateName and TotalVotes of the winners (max votes) in each District?
This does not suppose any particular database system. If you wish to stick to a particular implementation of SQL, go the way of SQLite and MySQL. If you can devise a better schema (or an easier one), that is acceptable too. Criteria: simplicity, portability to other databases.
