Query to find nᵗʰ max value of a column

Question

I want to find 2^nd, 3^rd, ... n^th maximum value of a column.

Answer 1

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 2

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.