5 th height salary

Thanks Pandian

The Dense_rank() function evaluates correctly than Row_Number() function

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

If in your table Salary value is duplicate(repeated) then use the Query:

Select distinct(sal) from employee A where 5=(Select count(distinct(sal)) from employee B where A.sal<=B.sal)

If no any Duplicate value in your salary field then you can remove the 'Distinct' key word, then your query will be:

Select sal from employee A where 5=(Select count(sal) from employee B where A.sal<=B.sal)

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)
Just set your requirement value in place of n..
Like if you want to find out 5th highest salary
then just place 5 in place of n...
You will get the answer

Last word

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary, FName, LName
FROM test
) AS foo
WHERE rownumber = 5

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

SELECT top 1 salary From (SELECT DISTINCT top n salary from employee order by salary DESC) a order by salary

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

Hi..

This Query Will helps to Get only 5th Highest Salary,

SELECT TOP 1 salary

FROM (SELECT DISTINCT TOP 5 salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary 

Hope it Helps!

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator

DENSE_RANK() is the perfect match here insteadof ROW_NUMBER()

Sample Data

39000
29000
19500
19000
11000
11000
11000
10000

SELECT * FROM (
SELECT
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary
FROM test ) AS Maxs
WHERE rownumber = 5

Result:
5 11000
5 11000
5 11000

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary
FROM test
) AS foo
WHERE rownumber = 5

5 11000

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Cnpphani, if this helps please login to Mark As Answer. | Alert Moderator