15,877,145 members
See more:
HI Filks.....!!!

I Have a Table --> Empdetails

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

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

Posted

Solution 2

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```

VJ Reddy 15-Apr-12 1:55am
Bala Selvanayagam 15-Apr-12 2:13am
Thanks VJ

Solution 1

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

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!