Click here to Skip to main content
15,880,608 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI Filks.....!!!

I Have a Table --> Empdetails

Empname Salary
------- ------
prasad 1000
arun 2000
kumar 3000
divya 4000
sreeya 5000
avinash 6000
rakesh 7000
lokesh 8000

How to find 6th MAximim Salry without using TOP ???

Please help me.....!!!
Posted

Try this:

SELECT TA.Empname, TA.Salary FROM (SELECT ROW_NUMBER() OVER (ORDER BY Salary ASC) AS rownumber, Empname, Salary FROM Empdetails) AS TA WHERE rownumber = 6
 
Share this answer
 
Comments
mistryshailesh 14-Apr-12 14:05pm    
The solution u have provided is wrong.
If the data is somthing like the foll:

EmpId EmpSal
1 1000.00
2 2000.00
3 3000.00
4 3000.00
5 4500.00
6 5000.00
7 6000.00
8 7000.00

it will return u wrong result..
Jorge J. Martins 14-Apr-12 14:18pm    
OK, I suppose You want only the salary.
You would have to group the salary.

SELECT TA.Salary FROM (SELECT ROW_NUMBER() OVER (ORDER BY Salary ASC) AS rownumber, Salary FROM Empdetails GROUP BY Salary) AS TA WHERE rownumber = 6
Bala Selvanayagam 14-Apr-12 16:53pm    
I think the order by clause for the row_number() should be DESC ?
VJ Reddy 15-Apr-12 1:56am    
Good answer, except for ASC, which is a minor fix. 5!
SQL
SELECT Empname, Salary
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) AS ROW_NUM
, Empname
, Salary
FROM Empdetails)
AS Empdetails
WHERE ROW_NUM = 6
 
Share this answer
 
Comments
VJ Reddy 15-Apr-12 1:55am    
Good answer. 5!
Bala Selvanayagam 15-Apr-12 2:13am    
Thanks VJ

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