Find nth max salary for a employee table db2 mainframes

Question

i am unable to find the query and i tried bellow query but it's not working

SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC LIMIT n,1

how to find the nth value.

for eg: i have 7 records and i want 4th highest salary.

EID -- ELOC --ESAL
A001 TNP10 500000
A002 KAP10 700000
A003 TNP20 600000
A004 TNP10 500000
A005 KAP20 800000
A006 KAP10 900000
A007 APP10 700000

Do you mean ...`order by salary desc fetch first n rows only` ? Or do you mean you *only* want the n'th value ? — mao, Nov 15 '18 at 16:21
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Nov 15 '18 at 23:51

score 0 · Answer 1 · answered Nov 15 '18 at 16:48

2-nd salary (30), for example:

with mytab (salary) as (
          select 10 from sysibm.sysdummy1
union all select 10 from sysibm.sysdummy1
union all select 20 from sysibm.sysdummy1
union all select 30 from sysibm.sysdummy1
union all select 30 from sysibm.sysdummy1
union all select 40 from sysibm.sysdummy1
)
select distinct salary
from (
select salary, dense_rank() over (order by salary desc) r_
from mytab
) where r_=2;

score 0 · Answer 2 · answered Nov 19 '18 at 14:42

0

/* forth highest anything */

with cte as (
select a.* , row_number() over (order by salary desc) rn from mytable a
) select * from cte where rn = 4

answered Nov 19 '18 at 14:42

danny117

5,581
1
26
35

Find nth max salary for a employee table db2 mainframes

2 Answers2