求某列第二最大值所在行的SQL

[phoenix_zg]

如题，想获取某列最大值所在行，以及该列次最大值所在行。因为海量数据的原因，不想用排序。不知道可不可以用exists实现？
C1 SEQ
a 1
a 3
a 5
b 1
b 2
b 3


想要的结果
C1 SEQ
a 3
b 2

或者能出来这样的结果也行
C1 SEQ
a 3
a 5
b 2
b 3

[xu176032]

create table t2(c1 varchar2(30),seq varchar2(30));

insert into t2 values('a','1');
insert into t2 values('a','3');
insert into t2 values('a','5');
insert into t2 values('b','1');
insert into t2 values('b','2');
insert into t2 values('b','3');



select c1, seq
  from (select t2.*, row_number() over(partition by c1 order by seq) as fn
          from t2)
 where fn = 2;

select c1, seq
  from (select t2.*, row_number() over(partition by c1 order by seq) as fn
          from t2)
 where fn >= 2;

[卖水果的net]

如果数据的重复的，可以使用 dense_rank() 代替 row_number()

[mayanzs]

需： order by seq desc 要不然是第二最小了。

[kingwinerxp]

如果是11gR2的话，可以考虑用Nth_value

[惑先生]

WITH T AS
(SELECT 'a' AS C1, 1 AS SEQ
FROM DUAL
UNION ALL
SELECT 'a' AS C1, 3 AS SEQ
FROM DUAL
UNION ALL
SELECT 'a' AS C1, 5 AS SEQ
FROM DUAL
UNION ALL
SELECT 'b' AS C1, 1 AS SEQ
FROM DUAL
UNION ALL
SELECT 'b' AS C1, 2 AS SEQ
FROM DUAL
UNION ALL
SELECT 'b' AS C1, 3 AS SEQ FROM DUAL)
SELECT C1, MAX(SEQ) AS SEQ
FROM T
GROUP BY C1
UNION
SELECT T.C1, MAX(T.SEQ) AS SEQ
FROM T,(
SELECT C1, MAX(SEQ) AS SEQ
FROM T
GROUP BY C1) TT
WHERE T.C1 = TT.C1 AND T.SEQ < TT.SEQ
GROUP BY T.C1


这种写法效率比排序还差

[越烟]

我用 emp 表来说一下把 。。。 select * from ( select p.*, dense_rank() over( partition by deptno order by sal desc) d from scott.emp p) p where p.d=2; 如果是11g 的话用 select * from ( select p.*, nth_value(sal,2) from last over( partition by deptno order by sal rows between unbounded preceding and unbounded following ) p from scott.emp p ) where sal = p