Query to find nth max value of a column

I want to find 2nd,3rd..nth maximum value of a column

From stackoverflow

• What database? I don't think there is a very good "generic" solution to this problem.

• You could sort the column into descending format and then just obtain the value from the nth row.

• You didn't specify which database, on MySQL you can do

  SELECT column FROM table ORDER BY column DESC LIMIT 7,10;
  

  Would skip the first 7, and then get you the next ten highest.

  Matthew Watson : If you are using mysql, this wont work in oracle (or mssql I believe)

  Pieter : good point, clarified it

• In SQL Server, just do:

  select distinct top n+1 column from table order by column desc
  

  And then throw away the first value, if you don't need it.

• Again you may need to fix for your database, but if you want the top 2nd value in a dataset that potentially has the value duplicated, you'll want to do a group as well:

  SELECT column FROM table WHERE column IS NOT NULL GROUP BY column ORDER BY column DESC LIMIT 5 OFFSET 2;

  Would skip the first two, and then get you the next seven highest.

• Pure SQL (note: I would recommend using SQL features specific to your DBMS since it will be likely more efficient). This will get you the n+1th largest value (to get smallest, flip the <). If you have duplicates, make it COUNT( DISTINCT VALUE )..

  select id from table order by id desc limit 4 ;
  +------+
  | id   |
  +------+
  | 2211 | 
  | 2210 | 
  | 2209 | 
  | 2208 | 
  +------+
  
  
  SELECT yourvalue
    FROM yourtable t1
   WHERE EXISTS( SELECT COUNT(*)
                   FROM yourtable t2
                  WHERE t1.id       <> t2.id
                    AND t1.yourvalue < t2.yourvalue
                 HAVING COUNT(*) = 3 )
  
  
  +------+
  | id   |
  +------+
  | 2208 | 
  +------+
  

• Here's a method for Oracle. This example gets the 9th highest value. Simply replace the 9 with a bind variable containing the position you are looking for.

     select created from (
       select created from (
         select created from user_objects
           order by created desc
         )
         where rownum <= 9
         order by created asc
       )
       where rownum = 1
  

  If you wanted the nth unique value, you would add DISTINCT on the innermost query block.

• for SQL 2005:

  SELECT col1 from 
       (select col1, dense_rank(col1) over (order by col1 desc) ranking 
       from t1) subq where ranking between 2 and @n
  

• Another one for Oracle using analytic functions:

  select col1
  from 
  ( select col1, dense_rank() over (order by col1 desc) rnk
    from tbl
  )
  where rnk = :b1
  

• Just dug out this question when looking for the answer myself, and this seems to work for SQL Server 2005 (derived from Blorgbeard's solution):

  SELECT MIN(q.col1) FROM (
      SELECT
          DISTINCT TOP n col1
          FROM myTable
          ORDER BY col1 DESC
  ) q;
  

  Effectively, that is a SELECT MIN(q.someCol) FROM someTable q, with the top n of the table retrieved by the SELECT DISTINCT... query.

• Consider the following Employee table with a single column for salary.

  +------+
  | Sal  |
  +------+
  | 3500 | 
  | 2500 | 
  | 2500 | 
  | 5500 |
  | 7500 |
  +------+
  

  The following query will return the Nth Maximum element.

  select SAL from EMPLOYEE E1 where 
   (N - 1) = (select count(distinct(SAL)) 
              from EMPLOYEE E2 
              where E2.SAL > E1.SAL )
  

  For eg. when the second maximum value is required,

    select SAL from EMPLOYEE E1 where 
       (2 - 1) = (select count(distinct(SAL)) 
                  from EMPLOYEE E2 
                  where E2.SAL > E1.SAL )
  

  +------+
  | Sal  |
  +------+
  | 5500 |
  +------+
  

• select sal,ename from emp e where 2=(select count(distinct sal) from emp where e.sal<=emp.sal) or 3=(select count(distinct sal) from emp where e.sal<=emp.sal) or 4=(select count(distinct sal) from emp where e.sal<=emp.sal) order by sal desc;

• Select max(sal) from table t1 where N (select max(sal) from table t2 where t2.sal > t1.sal)

  To find the Nth max sal.