Finding out Nth Maximum or Nth Minimum from Table's Column

 

Finding out Nth Maximum or Nth Minimum from Table's Column

For Finding Nth Maximun

Declare @ int

Set @n=5

1. select qty from tmpJSales T1 where @n = (select count(distinct qty) from tmpJSales where qty >= T1.qty )
   

   Purpose: To find out the nth highest number in a column. E.g.: Second highest salary from the salaries table
   

   The following SQL statement is taken from
   

   http://vyaskn.tripod.com/code/nth.txt
   

2. SELECT MAX(qty) from tmpJSales WHERE qty NOT IN ( SELECT TOP @n-1 qty FROM tmpJSales group by qty ORDER BY qty DESC )
   

   Although this statement is efficient than the First one. But first one is more manageable like what about finding minimum.
   

   In case of (1) simply change >= into <= that's it.
   

   In case of (2) change Max into Min and DESC with space
   

   And more expandable too. like what about this query find out the details of maximum nth sale/salary etc.
   

   In case of (1) Replace qty of outer query with *
   

   In case of (2) Put (2) as subquery as follow
   

   select * from tablename where qty = ( (Query2) )
   

   select * from tablename where qty in ( (Query2) )