How to get the Nth highest salary using recursive CTE

Question

1.00/5 (3 votes)

See more:

SQL-Server

Hi Guys,

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

Thanks
Srikanth

Posted 30-Jan-14 16:00pm

srikanthakasapu

Updated 4-Jan-22 18:51pm

Add a Solution

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

2 solutions

Add a Solution

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

Tomas Takac · Answer 1 · 2014-01-31T20:57:00

Solution 1

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

Posted 31-Jan-14 20:57pm

Tomas Takac

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.

Deeps1414 · Answer 2 · 2024-02-07T20:11:00

Solution 7

In place of ROW_NUMBER() use DENSE_RANK()

with ordered_salary as
(
SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) rn
FROM salary_table
)
select name, salary
from ordered_salary
where rn = 5

Posted 7-Feb-24 20:11pm

Deeps1414

Comments

Richard Deeming 8-Feb-24 4:40am

Read the OP's comment to the solution posted ten years ago:
"I dont not want to use any of the rank functions or the top operators and all. I just only recursive CTE."

If you're going to resurrect an ancient question, make sure you have read and understood everything that has been posted so far.

How to get the Nth highest salary using recursive CTE

2 solutions

Solution 1

Solution 7

Add your solution here

Preview 0

How to get the Nth highest salary using recursive CTE

2 solutions

Solution 1

Solution 7

Add your solution here

Preview 0

Existing Members

...or Join us