Oracle SQL query - Display max only?

Question

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);

sgeddes · Accepted Answer · 2013-06-03 13:52:11Z

1

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

answered Jun 3, 2013 at 13:52

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL query - Display max only?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related