CODEWITHSUNDEEP
sqlgroup-by
aj1988
aj1988

Reputation: 17

Oracle SQL query - Display max only?

I have the code below in an oracle SQL query. This code finds the username of a member and how many times they voted each year. How can I modify this so that it only shows the user/s that have voted the highest number of times? 

SELECT username, count(username), extract(year from voteDate) as vote_year,
max(count(*)) over (partition by extract(year from voteDate)) as Max_votes
FROM rankingInfo NATURAL JOIN memberinfo
GROUP BY username, extract(year from voteDate);

Upvotes: 0

Views: 131

Answers (1)

sgeddes
sgeddes

Reputation: 62831

If I'm understanding your query correctly, your max_votes returns the max votes you want and your count(username) returns the votes for each user. If so, you can put the results in a subquery and then just add WHERE criteria:

SELECT * 
FROM (
   SELECT username, count(username) votes, extract(year from voteDate) as vote_year,
       max(count(*)) over (partition by extract(year from voteDate)) as Max_votes
   FROM rankingInfo NATURAL JOIN memberinfo
   GROUP BY username, extract(year from voteDate)
) T
WHERE votes = max_votes

Upvotes: 1

Related Questions