fmpsagara fmpsagara - 2 months ago 9
MySQL Question

Fetch first N rows including tie values MYSQL

+-----+-------+-----+
| id | Name |Votes|
+-----+-------+-----+
| 1 | Joe | 36 |
| 2 | John | 34 |
| 3 | Mark | 42 |
| 4 | Ryan | 29 |
| 5 | Jay | 36 |
| 6 | Shawn | 39 |
+-----+-------+-----+


For this example, what I want is to retrieve the rows with the first 3 highest votes. However, if you'll notice, there are two rows with the same vote count. So this should be the result:

+-----+-------+-----+
| id | Name |Votes|
+-----+-------+-----+
| 3 | Mark | 42 |
| 6 | Shawn | 39 |
| 1 | Joe | 36 |
| 5 | Jay | 36 |
+-----+-------+-----+


How to achieve this?

Answer

You will have to perform an INNER JOIN, using the table back on itself. First, you want to select the top 3 unique/distinct scores, and this can be done by using:

SELECT DISTINCT Votes FROM mytable ORDER BY Votes DESC LIMIT 3

Now that you have obtained the top 3 scores, you want to join it back to the original table:

SELECT t1.* FROM mytable AS t1
INNER JOIN
  (SELECT DISTINCT Votes FROM mytable ORDER BY Votes DESC LIMIT 3) AS topvotes
ON
  topvotes.Votes = t1.Votes
ORDER BY t1.Votes DESC

Refer to a simple diagram for the strategy:

INNER JOIN strategy

For this query to be efficient, you will want to index the Votes column so that the subquery can fish out distinct votes quickly ;)

Here is a proof-of-concept SQLfiddle: http://sqlfiddle.com/#!9/c78f0/10