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

phoenix_zg 2015-08-19 11:44:56

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
...全文
392 6 打赏 收藏 转发到动态 举报

6 条回复

• 打赏
• 举报

• 打赏
• 举报

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

kingwinerxp 2015-08-27
• 打赏
• 举报

mayanzs 2015-08-27
• 打赏
• 举报

• 打赏
• 举报

xu176032 2015-08-20
• 打赏
• 举报

``````
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;

``````

3,490

• 近7日
• 近30日
• 至今