The "first past the post election" query problem

Question

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.

Answer 1

Select DistrictHnd, PartyHnd, CandidateName, TotalVotes
From ElectionResults As ER
Where TotalVotes = (
                    Select Max(ER1.TotalVotes)
                    From ElectionResults As ER1
                    Where ER1.DistrictHnd = ER.DistrictHnd
                    )

In this query, if there is a tie (two people in the same district with the same number of total votes), they will both show.

Answer 2

To do this using the windowing functions in SQL Server 2005 or higher:

;WITH Results_CTE AS
(
    SELECT
        DistrictHnd, PartyHnd, CandidateName, TotalVotes,
        ROW_NUMBER() OVER
        (
            PARTITION BY DistrictHnd
            ORDER BY TotalVotes DESC
        ) AS RowNum
    FROM ElectionResults
)
SELECT DistrictHnd, PartyHnd, CandidateName, TotalVotes
FROM Results_CTE
WHERE RowNum = 1

Probably faster than a correlated subquery, only needs a sort and a scan.

Note: In the case of ties, this will only get you the first entry which will be arbitrarily chosen. If you want to retrieve all rows in case of ties (which probably makes sense here), change ROW_NUMBER() to RANK().