How to get the top n entries for each column grouped by another column?

Question

Let's say I have this table called Employees:

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

And for each department id, I want the top three salaries. So the result should be:

DepartmentId	Name	Salary
1	Max	90000
1	Joe	85000
1	Randy	85000
1	Will	70000
2	Henry	80000
2	Sam	60000

So for each department id, the top three salaries are returned, and if there are duplicate salaries in the top three, the duplicates are returned too and the limiting factor is top three unique salaries. How to implement this?

*I want the top three salaries. So the result should be* ROW_NUMBER in CTE. *if there are duplicate salaries in the top three, the duplicates are returned too and the limiting factor is top three unique salaries.* Imagine that 4 employees have the same max. salary. What 3 of them must be returned and why? — Akina, Jun 17 '21 at 08:14
In that case all four will be returned, as in the example result four are returned, but the max salaries are only three, 90000, 85000 and 70000. — zendevil.eth, Jun 17 '21 at 08:17

Gordon Linoff · Accepted Answer · 2021-06-17T12:31:21.163

3

You would use dense_rank():

select e.*
from (select e.*,
             dense_rank() over (partition by departmentid order by salary desc) as seqnum
      from employees e
     ) e
where seqnum <= 3;

edited Jun 17 '21 at 12:31

answered Jun 17 '21 at 11:19

Gordon Linoff

1,242,037
58
646
786

Akina · Answer 2 · 2021-06-17T08:49:13.307

-1

WITH cte AS ( 
    SELECT DepartmentId, 
           Salary, 
           ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) rn
    FROM Employees 
)
SELECT *
FROM Employees
WHERE Salary >= ( SELECT MIN(Salary)
                  FROM cte
                  WHERE Employees.DepartmentId = cte.DepartmentId 
                    AND rn <= 3 )

it is well described in manuals and references, but you haven't even linked any of the manuals or provided any information at all in your answer. – Martin

MySQL 8.0 Reference Manual / ... / WITH (Common Table Expressions)

MySQL 8.0 Reference Manual / ... / Window Function Descriptions # ROW_NUMBER() function

MySQL 8.0 Reference Manual / ... / Subqueries # Correlated Subqueries

edited Jun 17 '21 at 08:49

answered Jun 17 '21 at 08:20

Akina

39,301
5
14
25

2

Looks like a sensible answer, but as OP doesn't understanding partitioning at all, maybe it would be helpful to explain the answer and why\how it works? – Martin Jun 17 '21 at 08:24
1

@Martin If OP don't understand something he may ask. And I don't see the point in writing a studying article. – Akina Jun 17 '21 at 08:25
1

Of course it's your answer and your choice to make but _generally_ code-only answers add little value. If OP understood the concept then they could have easily written this query themselves. – Martin Jun 17 '21 at 08:29
1

@Martin This is "questions and answers", not "studyings and explanations", is it? Participants comes there primarily to find "how to do this", not "i do this, how it works". *If OP understood the concept* I cannot predict does there is something unclear to OP, and if it is then what is unclear precisely. And I do not see the reason for to describe window functions concept which is well-described in any SQL reference manual. – Akina Jun 17 '21 at 08:38
You're right, it is well described in manuals and references, but you haven't even linked any of the manuals or provided any information at all in your answer. I can sense that my comment has caused some frustration but it was merely meant as a helpful suggestion - I apologise for any offence caused. – Martin Jun 17 '21 at 08:43
@Martin Well, links added. – Akina Jun 17 '21 at 08:49
1

I agree with Martin, providing solutions is great but giving steps or references on how it's done would be more helpful to the community, as it promotes learning experience for others, and not just copy paste it. – Samir Selia Jun 17 '21 at 08:58
@Samir Maybe... but the most part of Qs from novices are trivial duplicates. They do not search and do not learn in common, they really want in "copy and forget". Whereas the participants who want to know/understand always ask. Some of my answers have 2-3-4 or even more additional blocks with such explanations. – Akina Jun 17 '21 at 09:03
`ROW_NUMBER` should be `DENSE_RANK` because there are two employees with same salary. – Salman A Jun 17 '21 at 09:13

How to get the top n entries for each column grouped by another column?

2 Answers2