How to find the nth largest salary by department using SQLite-CodePudding

Say all the employees are listed in the same table employees along with their departments. How would you find the nth largest salary by department?

I know this gets you pretty close.

select SAL from EMPLOYEE E1 where 
 (N - 1) = (select count(distinct(SAL)) 
            from EMPLOYEE E2 
            where E2.SAL > E1.SAL )

But adding groupby still wouldn't get you there unless you did a join on the condition?

select SAL from EMPLOYEE E1

where 
 (N - 1) = (select count(distinct(SAL)) 
            from EMPLOYEE E2 
            inner join EMPLOYEE E3 ON department_id
            where E2.SAL > E3.SAL )
groupby department

CodePudding user response：

If this is homework and you have to use a correlated subquery then you must correlate also the department:

SELECT DISTINCT e1.department, e1.SAL 
FROM EMPLOYEE e1 
WHERE (N - 1) = (
  SELECT COUNT(DISTINCT e2.SAL) 
  FROM EMPLOYEE e2 
  WHERE e2.department = e1.department AND e2.SAL > e1.SAL 
);

A better solution would be to use DENSE_RANK() window function:

SELECT DISTINCT department, SAL
FROM ( 
  SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY SAL DESC) dr
  FROM EMPLOYEE 
)
WHERE dr = N;