How to find 5th maximum number through a query in sql

"How do I find Nth maximum value?" is one of the most asked questions in many
  
  interviews....  Here are some methods.. to solve this..

Use Inner Join
------------------
  select t1.num from number t1 inner join number t2 on t1.num<=t2.num  group by    t1.num having count(t1.num)=5

Use Top Operator

---------------------
Eg1:
Select top 1 num from(Select top 5 num from number order by num desc) T
order by num asc

Eg2:
Select TOP 1 quantity from findmax where quantity NOT IN (Select TOP 4 quantity FROM findmax order by quantity ASC)
order by quantity ASC

No comments:

Post a Comment