CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2860285
user2860285

Reputation: 31

Which row has the highest value?

I have a table of election results for multiple nominees and polls. I need to determine which nominee had the most votes for each poll.

Here's a sample of the data in the table:

PollID  NomineeID   Votes
1       1           108
1       2           145
1       3           4
2       1           10
2       2           41
2       3           0

I'd appreciate any suggestions or help anyone can offer me.

Upvotes: 3

Views: 99

Answers (4)

hasha
hasha

Reputation: 294

select Pollid, Nomineeid, Votes from Poll_table 
where Votes in ( 
    select max(Votes) from Poll_table 
    group by Pollid
);

Upvotes: 0

Pellared
Pellared

Reputation: 1292

SELECT 
  t.NomineeID,
  t.PollID
FROM 
  ( SELECT
      NomineeID,
      PollID, 
      RANK() OVER (PARTITION BY i.PollID ORDER BY i.Votes DESC) AS Rank
    FROM SampleData i) t
WHERE 
  t.Rank = 1

Upvotes: 2

Lloyd Banks
Lloyd Banks

Reputation: 36648

SELECT PollID, NomineeID, Votes
FROM 
table AS ABB2
    JOIN
       (SELECT PollID, MAX(Votes) AS most_votes
        FROM table) AS ABB1 ON ABB1.PollID = ABB2.PollID AND ABB1.most_votes = ABB2.Votes

Please note, if you have 2 nominees with the same number of most votes for the same poll, they'll both be pulled using this query

Upvotes: 0

Bill Gregg
Bill Gregg

Reputation: 7147

This will match the highest, and will also bring back ties.

select sd.*
from sampleData sd
    inner join (
        select PollID, max(votes) as MaxVotes
        from sampleData
        group by PollID
    ) x on
        sd.PollID = x.PollID and
            sd.Votes = x.MaxVotes

Upvotes: 3

Related Questions