쿼리 가장 높은값 쿼리결과 값 부탁드립니다.

Question

A 테이블

CODE SEQ C1 C2
00001 1 838 222
00001 2 234 234
00001 3 224 153
00002 1 299 545
00002 2 234 666
00003 1 778 468
00003 2 243 456
00003 3 456 789

--------------------------------------------------------------

결과값

코드 SEQ C1 C2

00001 3 224 153

00002 2 234 666

00003 3 456 789
--------------------------------------------------------------

고수님들 쿼리 결과좀 부탁드립니다.

Answer 1

WITH AA AS

(

SELECT '00001' code, 1 seq, 838 c1, 222 c2 FROM dual

UNION ALL SELECT '00001', 2, 234, 234 FROM dual

UNION ALL SELECT '00001', 3, 224, 153 FROM dual

UNION ALL SELECT '00002', 1, 299, 545 FROM dual

UNION ALL SELECT '00002', 2, 234, 666 FROM dual

UNION ALL SELECT '00003', 1, 778, 468 FROM dual

UNION ALL SELECT '00003', 2, 243, 456 FROM dual

UNION ALL SELECT '00003', 3, 456, 789 FROM dual

)

SELECT code, seq, c1, c2

FROM (SELECT code, seq, c1, c2

, ROW_NUMBER() OVER(PARTITION BY code ORDER BY seq DESC) rn

FROM b

)

WHERE rn = 1

Answer 2

select t1.code, t1.seq, t1.c1, t1.c2 from 테이블 as t1, (select code, max(t1) as max_sort from 테이블 group by code) as t2 where t1.seq = t2.max_sort and t1.code = t2.code;

지금 휴대폰에서 답변.드리는거라 맞을지 모르겠네요

일단 위와.같이 직접 쿼리를.날려보시면

원하시는 결과가 나올거라 생각을 합니다