Query to find nth max value of a column

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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

Answer 12

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;

Answer 13

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.