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
SubashPosted Nov 16, 2016, 8:30 PM
This is frequently asked interview questions thanks for share my suggestion if you compare the advantage or drawbacks of each query it will useful to understand the nature of those queries