sql - 查询以查找列的 nᵗʰ 最大值

Question

我想找到一列的第 2^nd、第 3^rd ... n 个最大值。

Answer 1

考虑以下具有单个薪水列的 Employee 表。

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

例如。当需要第二个最大值时，

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

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