|
Find Nth highest record from table
In interview you can faced question like to give solution(query) for find Nth highest Record/number/salary from given employee table.
TO find out 3rd highest salary from table
--Find 3rd highest salary
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM tblSalary
ORDER BY salary DESC) S
ORDER BY salary
General form to find to Nth highest salary from table
--Find Nth highest salary
SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP N salary FROM tblSalary ORDER BY salary DESC) S
ORDER BY salary
There are many solution to solve to this but above solution is easiest.
Take other possible solution,
SELECT MIN(salary) FROM tblSalary WHERE salary IN
(SELECT DISTINCT TOP 3 salary FROM tblSalary ORDER BY salary DESC)
--or--
SELECT MIN(salary) FROM
(SELECT DISTINCT TOP 3 salary FROM tblSalary ORDER BY salary DESC) S
|
User Comments:
Comment By
Sandhya
on
05/19/2010
Should it not be
SELECT MAX(salary) FROM tblSalary WHERE salary IN
(SELECT DISTINCT TOP 3 salary FROM tblSalary ORDER BY salary DESC)
Post Your Comment :
|