Here we are taking
example to find out 3rd Heighest salary.
--**************************************
--
************* 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