Retrieving a nth highest row

Question

Retrieving a nth highest row

jsheikabdullah

SSC Veteran

Points: 276
More actions
September 4, 2009 at 4:36 am

#210290

I have a table Personnel with Two Columns ID and Salary I want to retrieve the record with nth Highest salary.
how to go about this?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can use Row_Number.

If you want more specific help, please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jsheikabdullah SSC Veteran Points: 276 More actions · Answer 2

How can i use row_number the 5th row need not neccessarily be the 5th highest salary??

Consider This

ID SALARY

== =======

1 1000

2 6000

3 4000

4 2000

5 6000

6 1000

In this I want to first fetch the row(s) with third highest salary( that is 2000)

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

with cteSalary(Id,Salary,RowN)

as

(

Select Id,Salary,row_number() over (order by salary)

)

Select * from cteSalary where Rown = 3

Clear Sky SQL
My Blog[/url]

jsheikabdullah SSC Veteran Points: 276 More actions · Answer 4

jsheikabdullah

SSC Veteran

Points: 276

September 4, 2009 at 5:41 am

#1049310

Thanx mate!!!!

justanewone Ten Centuries Points: 1393 More actions · Answer 5

I think DENSE_RANK would be useful to you for your requirements, which will return records that have the same Salary, note I have also changed the order by to descending as you specified you need the nth highest salary.

WITH Salaries (ID, Salary, Ranking) AS

(

SELECT ID, Salary, DENSE_RANK() OVER(ORDER BY Salary DESC)

FROM [Table]

)

SELECT ID, Salary FROM Salaries WHERE Ranking = N

Dave