CODEWITHSUNDEEP
sqlgreatest-n-per-group
iteng
iteng

Reputation: 15

SQL -- Display 2nd highest salary for each dept, if employee having same salary then display salary with least empno

I need to display 2nd highest salary for each dept, if employee having same salary then display salary with least empno .

I have emp table as below in which deptno 20 have 2nd highest salary 3000 for two empno 7788 and 7902 .

EMPNO   ENAME   JOB  SAL    DEPTNO
7698    BLAKE   MANAGER  2850   30
7844    TURNER  SALESMAN 1500   30
7499    ALLEN   SALESMAN 1600   30
7654    MARTIN  SALESMAN 1250   30
7521    WARD    SALESMAN 1250   30
7900    JAMES   CLERK    950    30
7788    SCOTT   ANALYST  3000   20
7566    JONES   MANAGER  2975   20
7369    SMITH   CLERK    25000  20
7876    ADAMS   CLERK    1100   20
7902    FORD    ANALYST  3000   20
7839    KING    PRESIDENT 5000  10
7934    MILLER  CLERK    1300   10
7782    CLARK   MANAGER  2450   10

I have written below code:

select * from (
  select e.*, row_number() over (partition by deptno order by sal desc ) rn 
  from emp e  
)where rn = 2;

I got below result. But as per my requirement, if any employee having same salary for that department, then salary with least employee id should be display but in my case empno 7902 is displayed. But I need to display salary with empno 7788:

EMPNO   ENAME   JOB       SAL   DEPTNO
7782    CLARK   MANAGER   2450  10
7902    FORD    ANALYST   3000  20
7499    ALLEN   SALESMAN  1600  30

How to achieve this?

Upvotes: 1

Views: 387

Answers (3)

Nicholas Humphrey
Nicholas Humphrey

Reputation: 1250

you can use RANK(). The difference between RANK() and ROW_NUMBER() is that RANK() will give same rank for same numbers, so you will see two 2 for department 20. Then you wrap it up with another ROW_NUMBER() to grab the least EMPNO:

SELECT * FROM (
SELECT
  subq.EMPNO,
  subq.DEPTNO,
  ROW_NUMBER() OVER(PARTITION BY subq.DEPTNO ORDER BY EMPNO ASC) AS empno_rownum
FROM (  
  SELECT 
    salary.EMPNO,
    salary.DEPTNO,
    RANK() OVER(PARTITION BY salary.DEPTNO ORDER BY salary.SAL DESC) AS salary_rank
  FROM salary
) AS subq
WHERE subq.salary_rank = 2
) AS subq2
WHERE subq2.empno_rownum = 1

And here is the result:

EMPNO   DEPTNO  salary_rank
7782    10  2
7788    20  2
7499    30  2

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51892

This query gives the correct result on MySql

select * from (
    select e.*, row_number() over (partition by deptno order by sal desc, empno asc ) rn 
    from emp e  
) s where rn = 2;

Result

EMPNO   ENAME   JOB         SAL     DEPTNO  rn  
7782    CLARK   MANAGER     2450    10      2  
7788    SCOTT   ANALYST     3000    20      2   
7499    ALLEN   SALESMAN    1600    30      2

dbfiddle using Oracle

Upvotes: 1

Jeet Kumar
Jeet Kumar

Reputation: 547

Try this..

You have to add empno in order by clause with asc

    select * from ( select e.*, row_number() over (partition by deptno order by 
    sal desc,empno asc ) rn 
    from emp e) where rn = 2;

Upvotes: 1

Related Questions