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