Counting Ranked-Choice Votes With a Loop and a Table

Counting ranked-choice votes takes a table of ballots and a loop that removes the weakest candidate round after round. Each ballot moves to its highest-ranked candidate who is still in the race, until someone holds a majority.

Pick the rules before you write the loop

Your team votes on the topic for the next lunch-and-learn. Four people rank their top two of three topics. Topic B has the most first choices, but not more than half. So who wins?

Ranked-choice counting has several variations, so write the rules down first. In this demo, each round removes one candidate with the fewest votes. A winner needs more than half of the ballots still active. If two candidates tie for last, the one with the higher ID goes. That tie rule is arbitrary on purpose. A real election needs a real rule, agreed before anyone counts.

DROP TABLE IF EXISTS #Ballots;
DROP TABLE IF EXISTS #Candidates;

CREATE TABLE #Candidates
(CandidateId int PRIMARY KEY, CandidateName nvarchar(80), Eliminated bit NOT NULL DEFAULT 0);

CREATE TABLE #Ballots
(BallotId int NOT NULL, RankNumber tinyint NOT NULL, CandidateId int NOT NULL,
 PRIMARY KEY (BallotId, RankNumber), UNIQUE (BallotId, CandidateId));

INSERT #Candidates (CandidateId, CandidateName) VALUES (1, N'Topic A'), (2, N'Topic B'), (3, N'Topic C');

INSERT #Ballots VALUES
    (1, 1, 1), (1, 2, 2),
    (2, 1, 2), (2, 2, 1),
    (3, 1, 3), (3, 2, 2),
    (4, 1, 2), (4, 2, 3);

Keep every block in one query window, because temp tables vanish when the session ends. The two keys on #Ballots mean a ballot cannot rank the same candidate twice or use one rank twice.

Find each ballot’s current choice

Join the ballots to the candidates still in the race. ROW_NUMBER over each ballot’s ranks picks its top remaining choice. A ballot with no remaining candidate is exhausted and drops out of the count.

Then LEFT JOIN from the candidate list. Without it, a candidate with zero votes disappears from the tally and can never be eliminated.

WITH Choices AS
(
    SELECT b.BallotId, b.CandidateId,
           ROW_NUMBER() OVER (PARTITION BY b.BallotId ORDER BY b.RankNumber) AS PreferenceOrder
    FROM #Ballots AS b
    JOIN #Candidates AS c ON c.CandidateId = b.CandidateId
    WHERE c.Eliminated = 0
)
SELECT c.CandidateId, c.CandidateName, COUNT(ch.BallotId) AS VoteCount
FROM #Candidates AS c
LEFT JOIN Choices AS ch ON ch.CandidateId = c.CandidateId AND ch.PreferenceOrder = 1
WHERE c.Eliminated = 0
GROUP BY c.CandidateId, c.CandidateName
ORDER BY c.CandidateId;

First choices are 1, 2, and 1 votes for A, B, and C. B leads with 2 of 4. That is exactly half, and half is not a majority.

Run the elimination with a round log

The loop repeats the same tally every round and saves it in a log table. It stops when a candidate has more than half of the active ballots, or when no active ballots remain. Otherwise it removes the weakest candidate and goes again.

DROP TABLE IF EXISTS #Rounds;
DROP TABLE IF EXISTS #Tally;
CREATE TABLE #Rounds (RoundNumber int, CandidateId int, VoteCount int);
CREATE TABLE #Tally (CandidateId int PRIMARY KEY, VoteCount int);

DECLARE @Round int = 0, @Active int, @Winner int = NULL, @Remove int;

WHILE EXISTS (SELECT 1 FROM #Candidates WHERE Eliminated = 0)
BEGIN
    SET @Round += 1;
    TRUNCATE TABLE #Tally;

    WITH Choices AS
    (SELECT b.BallotId, b.CandidateId,
            ROW_NUMBER() OVER (PARTITION BY b.BallotId ORDER BY b.RankNumber) AS PreferenceOrder
     FROM #Ballots AS b JOIN #Candidates AS c ON c.CandidateId = b.CandidateId
     WHERE c.Eliminated = 0)
    INSERT #Tally (CandidateId, VoteCount)
    SELECT c.CandidateId, COUNT(ch.BallotId)
    FROM #Candidates AS c
    LEFT JOIN Choices AS ch ON ch.CandidateId = c.CandidateId AND ch.PreferenceOrder = 1
    WHERE c.Eliminated = 0
    GROUP BY c.CandidateId;

    INSERT #Rounds SELECT @Round, CandidateId, VoteCount FROM #Tally;
    SELECT @Active = SUM(VoteCount) FROM #Tally;
    IF @Active = 0 BREAK;

    SELECT @Winner = CandidateId FROM #Tally WHERE VoteCount * 2 > @Active;
    IF @Winner IS NOT NULL BREAK;

    SELECT TOP (1) @Remove = CandidateId FROM #Tally ORDER BY VoteCount, CandidateId DESC;
    UPDATE #Candidates SET Eliminated = 1 WHERE CandidateId = @Remove;
END;

SELECT @Winner AS WinningCandidateId;

SELECT RoundNumber, CandidateId, VoteCount FROM #Rounds ORDER BY RoundNumber, CandidateId;

Round 1 is the tally you just saw, with no majority. A and C tie at 1 vote, so the tie rule removes C. Ballot 3 had ranked C first and B second, so its vote moves to B. In round 2, B has 3 votes, A has 1, and B wins.

Check the ballots as data

A loop can count perfectly and still count bad data. Check that every ballot names a real candidate, and look at how many ranks each ballot used.

SELECT b.BallotId, b.RankNumber, b.CandidateId
FROM #Ballots AS b
LEFT JOIN #Candidates AS c ON c.CandidateId = b.CandidateId
WHERE c.CandidateId IS NULL;

SELECT BallotId, MIN(RankNumber) AS FirstRank, MAX(RankNumber) AS LastRank, COUNT(*) AS PreferenceCount
FROM #Ballots
GROUP BY BallotId
ORDER BY BallotId;

No orphan ballots come back, and every ballot ranked positions 1 and 2. In a permanent schema, a foreign key would enforce the first check for you. Also prevent the same ballot being loaded twice under a new ID, because that quietly adds votes.

Report the rules with the result

Publish the winner, the round tallies, the tie rule, and how many ballots stayed active. In this data, all four stayed active in both rounds. If your policy counts a majority of the original ballots instead, change the denominator and the stopping rule. Test cases like an immediate majority, a zero-vote candidate, and an elimination tie will catch most mistakes.

DROP TABLE IF EXISTS #Tally;
DROP TABLE IF EXISTS #Rounds;
DROP TABLE IF EXISTS #Ballots;
DROP TABLE IF EXISTS #Candidates;

Write down the policy first, and let the loop follow it.

A vote loop is not a voting policy, it is a policy implementation.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.