Home > Software design >  Find top 3 values but also include duplicates in SQL
Find top 3 values but also include duplicates in SQL

Time:11-07

If I have a table like,

Names Marks
Liam 50
Jade 55
John 55
Pern 60
Lopa 20

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,

Names Marks
Pern 60
Jade 55
John 55
Liam 50

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;
  • Related