如何用oracle查询第二大的值，例如aaa 1,bbb 2,ccc 3要求列出bbb 2

Question

其中1 2 3是count以后的值，所以最好可以使用max count指令，谢谢，求大神

Answer 1

select max(count) from 表 where count not in (select max(count) from 表)

这是sql server的语法，你换成oracle的就行了

Answer 2

select * from (
select col1, count(1) , row_number() over(order by count(1) desc) row_num from dual
group by col1
) where row_number = 2