If I have a table like,

I want to find the top 3 marks but if there are ties I want to include them and the number of people can be more than 3. So this table will become,

Any suggestions how to take care of the ties part?

CodePudding user response：

You can do something like this. Let 'tblMarks' be your table name. Replace @N with your desired value, eg. 3

select * from tblMarks where 
Marks between
(SELECT Marks from tblMarks e1 where 
        @N-1 = (SELECT COUNT(DISTINCT Marks)from tblMarks e2 where e2.Marks > e1.Marks))
        
and
(SELECT Marks from tblMarks e1 where 
        1-1 = (SELECT COUNT(DISTINCT Marks)from tblMarks e2 where e2.Marks > e1.Marks))
        
order by Marks desc
;

Explanation:

SELECT * from tblMarks e1 where 
        @N-1 = (SELECT COUNT(DISTINCT Marks)from tblMarks e2 where e2.Marks > e1.Marks) ;

Replace @N to 1 to get the first distinct highest value, @N to 2 to get the second distinct highest value and so on.

Now simply use Subquery with a between Operator between

1. The Highest Marks and
2. your desired marks

So if you give 3 in the first query it will be something like this

select * from tblMarks where 
Marks between 50 and 60
order by Marks Desc

As 60 is the highest and 50 is the 3rd highest marks.

CodePudding user response：

If you are using MySQL 8 or later, then the DENSE_RANK analytic function can be used here:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY Marks DESC) rnk
    FROM yourTable
)

SELECT Names, Marks
FROM cte
WHERE rnk <= 3;

Demo

To do this without using a CTE, just inline it:

SELECT Names, Marks
FROM
(
    SELECT *, DENSE_RANK() OVER (ORDER BY Marks DESC) rnk
    FROM yourTable
) t
WHERE rnk <= 3;