A common SQL Query
This is a very simple SQL query for finding the 2nd highest salary from an employee table.
A very common question in .NET interviews. The query is pretty self explanatory.
select top(1)[Name],Salary from Employee
where salary not in (select top (1) salary from employee order by salary desc) ORDER BY SALARY DESC
– Change the top parameter to (nth highest salary – 1)
2 comments so far
Leave a reply
– Small correction (Added Order by clause at the end)
SELECT TOP(1)[NAME],SALARY FROM EMPLOYEE
WHERE SALARY NOT IN (SELECT TOP (1) SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
ORDER BY SALARY DESC
– Other methods: 1
WITH EMPLOYEE_ROW AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS ‘RowNumber’
FROM EMPLOYEE
)
SELECT * fROM EMPLOYEE_R WHERE RowNumber= 2
– Other methods: 2
WITH EMPLOYEE_RAN AS
(
SELECT *, RANK() OVER(ORDER BY SALARY DESC) AS ‘RANK’
FROM EMPLOYEE
)
SELECT * fROM EMPLOYEE_RAN WHERE RANK= 2
Thanks for the correction Sijo. I missed when i copied and pasted the query. U rock!!