Display 3 Maximum column values ​​include duplicate values

Question

I am trying to display the highest 3 tags on a page, I am using the following code.

Select * from myTable Marks Desc Limit 3. 

It will work fine if there are no duplicate entries.

My table

 Name Mark ------------ S1 90 S2 55 S3 60 S4 90 S5 55 S6 60 S7 88 S8 45 S9 88 

As in the above table, I want to show 3 records, including duplicate records

So my final result looks like

 Name Marks ---------------- S1 90 S4 90 S7 88 S9 88 S3 60 S6 60 

Please help me.

Answer 1

First select the unique 3 best characters, then find all entries that have one of these marks:

 SELECT name, mark FROM mytable AS t1 INNER JOIN (SELECT DISTINCT(mark) AS best_marks FROM mytable ORDER BY mark DESC LIMIT 3) AS t2 ON t1.mark = t2.best_marks ORDER BY mark DESC, name ASC; 

Answer 2

You can try to have a temporary table to store the top three records and display the result by joining this table.
Other than that, getting this in one request would be difficult, and there would be a huge impact on performance.

However, if you insist, you can try this

 SELECT * FROM myTable WHERE mark IN (SELECT MAX(mark) FROM myTable) OR mark IN (SELECT MAX(mark) FROM myTable WHERE mark < (SELECT MAX(mark) FROM myTable)) OR mark IN (SELECT MAX(mark) FROM myTable WHERE mark < (SELECT MAX(mark) FROM myTable WHERE mark < (SELECT MAX(mark) FROM myTable))); 

This will only work for top 3. If you need more, you need to increase or conditions in the where clause. But again, not bad for performance