5 th height salary

Posted by Cnpphani under Sql Server on 3/22/2009 | Views : 2096 | Status : [Member] | Replies : 8
i need emp table 5 th heighst salary and details




Responses

Posted by: Sudhak on: 9/24/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks Pandian

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



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

Posted by: Satyapriyanayak on: 1/24/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
select distinct(salary) from employee A where 5=(select count(distinct(salary)) from employee B where A.salary<=B.salary)

If this post helps you mark it as answer
Thanks

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

Posted by: Kundan64 on: 1/25/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Lastword on: 1/25/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Neeks on: 3/22/2009 [Member] Bronze

Up
0
Down
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

Posted by: Arun151969 on: 3/25/2009 [Member] Starter

Up
0
Down
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

Posted by: BABU_AKKANDI on: 4/6/2009 [Member] Starter

Up
0
Down
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

Posted by: PandianS on: 7/13/2009 [Member] [MVP] Silver

Up
0
Down
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

Login to post response