Geek asks and answers

SQL Query to find contest winners

I have a query that returns me the results as follows:

  Race   | Candidate | Total Votes | MaxNoOfWinners
    ---------------------------------------------------
    1      | 1         | 5000        | 3
    1      | 2         | 6700        | 3
    2      | 1         | 100         | 3
    2      | 2         | 200         | 3
    2      | 3         | 300         | 3
    2      | 4         | 400         | 3
    ...

I was wondering if there is a query that can be written to return only the winners (based on MaxNoOfWinners and TotalVotes) for a particular race. So, for the foregoing, I will only return

Race   | Candidate | Total Votes | MaxNoOfWinners
---------------------------------------------------
1      | 1         | 5000        | 3
1      | 2         | 6700        | 3
2      | 2         | 200         | 3
2      | 3         | 300         | 3
2      | 4         | 400         | 3
...
+3
source share
3 answers

Here is the solution ... I have not tested, so there may be typos. The idea is to use the RANK () function of SQL Server to rate a race based on votes and not include those that do not meet the criteria. Note. Using RANK () rather than ROW_NUMBER () will contain links as a result.

WITH RankedResult AS
(
  SELECT Race, Candidate, [Total Votes], MaxNoOfWinners, RANK ( )  OVER (PARTITION BY Race ORDER BY [Total Votes] DESC) AS aRank
  FROM Results
)
SELECT Race, Candidate, [Total Votes], MaxNoOfWinners
FROM RankedResult
WHERE aRANK <= MaxNumberOfWinners
+6
source

,

Create Table #Race(Race_id int , MaxNoOfwinners int ) 

INSERT INTO #Race (Race_id , MaxNoOfwinners)
VALUES (1,3), 
       (2,3),
       (3,1)


CREATE TABLE #Candidate (CandidateID int , Race_ID int , Total_Votes int )
INSERT INTO #Candidate (CandidateID  , Race_ID  , Total_Votes  )
VALUES (1,1,5000),
        (2,1,6700),
        (1,2,100),
        (2,2,200),
        (3,2,300),       
        (4,2,400),        
        (1,3,42),
        (2,3,22)


;WITH CTE as (
SELECT 
    RANK() OVER(PARTITION BY race_id ORDER BY  race_id, total_votes DESC ) num,
    CandidateID  , Race_ID  , Total_Votes
From 
    #Candidate)
SELECT * FROM cte inner join #Race r
on CTE.Race_ID = r.Race_id
and num <= r.MaxNoOfwinners

DROP TABLE #Race
DROP TABLE #Candidate


num                  CandidateID Race_ID     Total_Votes Race_id     MaxNoOfwinners
-------------------- ----------- ----------- ----------- ----------- --------------
1                    2           1           6700        1           3
2                    1           1           5000        1           3
1                    4           2           400         2           3
2                    3           2           300         2           3
3                    2           2           200         2           3
1                    1           3           42          3           1
+2
WITH q0 AS (SELECT qry.*, rank() AS r 
   FROM qry OVER (PARTITION BY race ORDER BY total_votes DESC))
SELECT q0.race, q0.candidate, q0.total_votes FROM q0 WHERE r<=q0.max_winners;
0

More articles:


All Articles