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?

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

Its simple, i have a table like this

NAME SALARY
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.

Thanks
Srikanth

1 solution

You don't need recursive CTE for that:

SQL
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
   
Comments
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.

Thanks
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