还应该考虑到最大值是重复的情况,所以使用dense_rank,而不用row_number.
因为dense_rank:连续排序,重复值算同一个名次,
而row_number则是流水号,重复值的流水号是不同的。
如有值:1,2,3,4,5,5
SQL> select a,row_number() over(order by a desc) "名次" from t_max;
A 名次
---------- ----------
5 1
5 2
4 3
3 4
2 5
1 6
6 rows selected
SQL> select a,dense_rank() over(order by a desc) "名次" from t_max;
A 名次
---------- ----------
5 1
5 1
4 2
3 3
2 4
1 5
6 rows selected
所以,正确的SQL应该是
SQL> select max(a) from
2 (
3 select a,dense_rank() over(order by a desc) m from t_max
4 )
5 where m>1;
MAX(A)
----------
4
--------------测试代码------------------------
SQL> create table t_max
2 (
3 a number
4 );
Table created
SQL> insert into t_max values(1);
1 row inserted
SQL> insert into t_max values(2);
1 row inserted
SQL> insert into t_max values(3);
1 row inserted
SQL> insert into t_max values(4);
1 row inserted
SQL> insert into t_max values(5);
1 row inserted
SQL> insert into t_max values(5);
1 row inserted
SQL> commit;
Commit complete
SQL> select max(a) from
2 (
3 select a,row_number() over(order by a desc) m from t_max
4 )
5 where m>1;
MAX(A)
----------
5
SQL> select max(a) from
2 (
3 select a,dense_rank() over(order by a desc) m from t_max
4 )
5 where m>1;
MAX(A)
----------
4