Limiting MySQL select based on repeating column values?

Question

Consider this schema:

create table Employee(
    id integer, 
    name varchar(100), 
    position varchar(100), 
    id_department integer
);

create table Salary(
    id_employee integer, 
    year integer, 
    month integer, 
    salary float
);

create table Department(
    id integer, 
    name varchar(100)
);

I want to gather Employees that are ranked between 3rd and 5th place based on their average yearly salary per department, but I am a bit stumped. After some searching, I couldn't quite find a solution that worked for me.

Here's what I have as of now:

SELECT Employee.*, avg_salary
FROM Employee
INNER JOIN (
    SELECT id_employee, sum(salary) / count(distinct(year)) as avg_salary 
    FROM Salary 
    GROUP BY id_employee
)
AS T on Employee.id = id_employee 
ORDER BY Employee.id_department, avg_salary;

This gets me correctly ranked Employees, but how do I get only the ones that are ranked 3-5 in their department?

I'm using MySQL 5.x, but upgrading to 8.x is not an issue.

Have you tried appending `LIMIT 2,3` your query to get the 3d, 4th, and 5th rows? — O. Jones, Mar 15 '20 at 19:59
Indeed, this was my first instinct. But this yields 3 rows in total, not 3 rows per department_id. — jello jey, Mar 15 '20 at 20:01
Which version of MySQL do you use? Please [edit] your question. — O. Jones, Mar 15 '20 at 20:02
Note that money is rarely float; it's why decimal was invented — Strawberry, Mar 15 '20 at 20:03
This may help with pre-version 8 mysql. https://stackoverflow.com/questions/3333665/rank-function-in-mysql Version 8 has windowing functions including `RANK()` https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank — O. Jones, Mar 15 '20 at 20:29
@O.Jones regarding the first link -- if you could explain in greater detail how to re-initialize this variable for each `department_id` and `LIIMIT` based on it -- it would be much, much appreciated :) `RANK()`, as far as I understood, yields (kinda? sorta?) the same result. Still, I have no clue as how to filter as precise as the OP mentions. — jello jey, Mar 15 '20 at 20:34

score 1 · Accepted Answer · answered Mar 15 '20 at 23:27

In MySQL 8.0, one option is to use a ranking function; this requires turning your existing query to a subquery:

select *
from (
    select 
        e.*, 
        s.avg_salary, 
        row_number() over(partition by e.id_department order by s.avg_salary desc) rn
    from employee e
    inner join (
        select id_employee, sum(salary) / count(distinct(year)) as avg_salary 
        from salary 
        group by id_employee
    ) s on s.employee.id = e.id
) t
where rn between 3 and 5
order by id_department, avg_salary;

That's exactly whan I was looking for, kudos to you. – jello jey Mar 16 '20 at 06:13 — jello jey, Mar 16 '20 at 06:13

Limiting MySQL select based on repeating column values?

1 Answers1