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

如题，想获取某列最大值所在行，以及该列次最大值所在行。因为海量数据的原因，不想用排序。不知道可不可以用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

6 个解决方案

#1

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;

#2

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

#3

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

#4

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

#5

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


这种写法效率比排序还差

#6

 我用 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