Search code examples
mysqlwindow-functionsranking

mysql select rows with limit but also include the users with same votes beyond limit

The table name is user_votes and we get the top five users according to vote count with this query

select * from user_votes order by votes desc limit 5

it works fine but now let's say the 5th user and the 6th user have the same vote count. Then this query will select only TOP 5 but will neglect the 6th user. We want to include the 6th user too as he has the same number of votes as the 5th

As in the attached image, the bottom 2 users with user_id [1,5] has the same votes, there is one more user with the same votes but query won't show it

The goal is to include all the users who have vote count 2 even though limit is 5. So minimum row count returned is 5 in case all the users have different votes but maximum depends on the last row votes column value. In this case its 2

enter image description here

Mysql version is Ver 8.0.25

Solution

  • This is a job for the DENSE_RANK() windowing function. Here's a fiddle.

    WITH ranking AS (
  SELECT user_id, 
         votes, 
         DENSE_RANK() OVER (ORDER BY votes DESC) ranking
  FROM vote
)
SELECT * 
  FROM ranking 
 WHERE ranking <= 5
 ORDER BY ranking;

    These windowing functions allow you to specify various things like ranking easily.