Code Project

Link Unit

Thursday, December 27, 2007

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) )


 

Post a Comment