Click here to Skip to main content
15,304,433 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Hi Guys,

Can any of you tell me how to get the Nth highest salary using recursive CTE?

Updated 4-Jan-22 18:51pm
King Fisher 31-Jan-14 1:19am
hi ,its not clear,can you explain it
srikanthakasapu 31-Jan-14 7:22am

Its simple, i have a table like this

ABC 12200
BBB 25463
CCV 5555
EEE 8568
JJK 42668
LLL 6599
MKK 56985
QWE 25698
RRR 5445
RTR 12586
RTY 16565
SSS 96887
YUY 545

Now, i need to find for example 5th highest salary or 6th highest salary or 3rd what ever it is from the table.

This, i have to do in a recursive CTE.

Please do reply if it is still not clear.


1 solution

You don't need recursive CTE for that:

with ordered_salary as
SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) rn
FROM salary_table
select name, salary
from ordered_salary
where rn = 5
srikanthakasapu 3-Feb-14 4:23am
Hi Tomas,

Thanks for the reply. I apologize that i have told some of the iffs and buts...

I dont not want to use any of the rank functions or the top operators and all. I just only recursive CTE.

There might be multiple ways to write the query but i need only recursive CTE.

Srikanth A.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900