Sunday, February 19, 2012

Sql Query for Highest Salary


Four Different Way to Find nth Highest Salary in Sql Server


SELECT * FROM Employee order by salary desc;


--**************************************
-- ************* 1st Query *************
--**************************************
SELECT TOP 1 SALARY
FROM
(
SELECT DISTINCT TOP 3 SALARY
FROM Employee
ORDER BY SALARY DESC
)a
Order by salary


--**************************************
-- ************* 2nd Query *************
--**************************************
SELECT * FROM (
SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary
FROM Employee ) AS Maxs
WHERE rownumber = 3


--**************************************
-- ************* 3rd Query *************
--**************************************
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary
FROM Employee
) AS foo
WHERE rownumber = 3


--**************************************
-- ************* 4th Query *************
--**************************************
Select  *
From    Employee E1
Where 3 = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary >= E1.Salary)


--***********************************************************
-- ************* For Second Highest Salary Query *************
--************************************************************
SELECT MAX(salary) AS SAL FROM EMPLOYEE WHERE salary<>(SELECT MAX(salary) FROM EMPLOYEE)


--***********************************************************
-- ************* For Understand Differnece between row_number(),rank() and dense_rank()function *************
--************************************************************
SELECT  salary
        ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
        ,rank () OVER (ORDER BY salary DESC) as RANK
        ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM Employee

0 comments: