how to get nth highest salary in sql server
|
Jak
858
Points
132
Posts
|
Hi, I have EmpSalary table with field salary. I want select statement to get nth highest salary.
Views:
8985
Total Answered: 5 Total Marked As Answer: 1 Posted On: 17-Sep-2014 23:07 Share: 
|
Answers
|
Rahul M...
4822
Points
23
Posts
|
Hi, try the following query: SELECT TEMP.[Salary] FROM (SELECT ROW_NUMBER() OVER (ORDERBY [Salary] DESC) AS rownumber, [Salary] FROM [tablename] ) AS TEMP WHERE TEMP.rownumber =n
Posted On:
17-Sep-2014 23:24
|
|
NiceOne...
1382
Points
14
Posts
|
You can use following query as: 1st highest: SELECT * FROM EmpSalary emp1 WHERE 0 = (SELECT COUNT(DISTINCT(emp2.salary)) FROM EmpSalary emp2 WHERE emp2.salary > emp1.salary)
2nd highest: SELECT * FROM EmpSalary emp1 WHERE 1 = (SELECT COUNT(DISTINCT(emp2.salary)) FROM EmpSalary emp2 WHERE emp2.salary > emp1.salary)
3rd highest: SELECT * FROM EmpSalary emp1 WHERE 2 = (SELECT COUNT(DISTINCT(emp2.salary)) FROM EmpSalary emp2 WHERE emp2.salary > emp1.salary)
So if you want get nth highest salary then: SELECT * FROM EmpSalary emp1 WHERE (n+1) = (SELECT COUNT(DISTINCT(emp2.salary)) FROM EmpSalary emp2 WHERE emp2.salary > emp1.salary)
Posted On:
17-Dec-2015 01:24
|
|
Jak
858
Points
132
Posts
|
Hi Rahul Maurya, Both the query worked fine but when two or more employee have the same salary then above query working. Please help.
Posted On:
20-Dec-2015 22:30
|
|
Rahul M...
4822
Points
23
Posts
|
Hi Jak, You can use DENSE_RANK() inbuilt method the get the nth highest salary even if two or more employee have the same salary, this will work fine First Highest Salary: SELECT * FROM (SELECT *,DENSE_RANK() OVER(ORDER BY salary DESC) AS SalaryRANK FROM EmpSalary) TMP WHERE SalaryRANK=1
nth Highest Salary: SELECT * FROM (SELECT *,DENSE_RANK() OVER(ORDER BY salary DESC) AS SalaryRANK FROM EmpSalary) TMP WHERE SalaryRANK=n
Posted On:
20-Dec-2015 22:39
|
|
Jak
858
Points
132
Posts
|
Hi, Finaly I got the Query to get nth highest salary. Thaks to all...
Posted On:
20-Dec-2015 22:47
|
Blog
Active User (4)
