N-Largest salary without using top keyword or max key word in Sql server
This the commom questions often ask in the interview how to find out the 2nd,3rd…….. or nth laragest salary or data from the table.
Its not a comlecated question and you are ok with top key word but he want to know something different and he asks you that show me the same out put without using Max or top key word…………….
This time you are blank
Ok lets starts the practice to find out the Nth largest salary with out using top or Max key word.
select * from
(
select row_number() over(order by a.AmountReceived desc) as row,a.AmountReceived from
(
select distinct AmountReceived from LoungeAccessHistory
)a
)b where b.row=3
The same way you can find out the complete details of employee from the table who have Nth largest salary like this
Or
Find out the compete detail of employee who have 3rd largest salary into the compay
select * from LoungeAccessHistory
where
AmountReceived=(select b.AmountReceived from
(
select row_number() over(order by a.AmountReceived desc) as row,a.AmountReceived from
(
select distinct AmountReceived from LoungeAccessHistory
)a
)b where b.row=3
)
Nth Largest salary using Correlated Query
Declare @n int
Set @n=3
Select AmountReceived
from LoungeAccessHistory E1 where (Select Count(Distinct(E2.AmountReceived))
From
LoungeAccessHistory E2 Where E2.AmountReceived >= E1.AmountReceived)= @n
order by E1.AmountReceived desc
Nth Largest salary using DENSE_RANK () function of Sql Server
Declare @n int
Set @n=3
select * from
(
select DENSE_RANK () over(order by AmountReceived desc) as row,* from LoungeAccessHistory
)a where a.row=@n
