N-Largest salary without using top keyword or max key word in Sql server

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 2^nd,3^rd…….. 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 3^rd 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