Query to find nᵗʰ max value of a column

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

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)