How to find the nth largest salary by department using SQLite

Question

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

Answer 1

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;