WITH TEMP AS

(

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S233' AS CODE FROM DUAL UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S437' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S5348' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S134' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S233' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S437' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S5348' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S134' AS CODE FROM DUAL

UNION ALL

SELECT '202101' AS YYMM, '00373' AS SEQNO , '01' AS SNO ,'002' AS DTL_SEQNO ,'S233' AS CODE FROM DUAL

)

SELECT *

FROM 

(

SELECT YYMM,

          SEQNO,

           SNO,

           DTL_SEQNO,

          COUNT(CODE) CNT ,

          CODE,

         ROW_NUMBER() OVER(ORDER BY COUNT(1) DESC) RN 

FROM TEMP

GROUP BY YYMM,SEQNO,SNO,DTL_SEQNO,CODE

)

WHERE 1=1

AND RN <= 2 

이 방법 말고 다른방법 부탁드려봅니다.

다른 방법을 찾는 특별한 이유가 있는지?
방법상의 문제는 없으니 다른 방법을 찾기보다는 다른 몇가지 문제점을 해결해야 합니다.
샘플을 보면 yymm, seqno, sno, dtl_seqno 가 1가지 밖에 없는데?
실제로도 1가지만 조회되는지?
여러가지 값이 함께 조회된다면?
ROW_NUMBER 함수 사용에 PARTITION BY 구문이 추가되어야 할 것입니다.
또한 예시에도 보이듯이 cnt 가 동률을 이룰 경우 어떻게 처리할지 규칙을 정해야 합니다.
2등이 2건인데 둘 다 출력할 것인지? 둘중 하나를 선택할 것인지?
둘중 하나를 선택한다면 선택 기준은 어떻게 되는지?
기타사항으로 COUNT(code) 는 COUNT(*) 사용하는 것이 좋습니다.

마뇽님 감사합니다..보니까 오타가 약간있었네요~437은 2건이네요 ㅠ  row_number()over(order by count(*) desc ,code desc)로했습니다.. 말씀하신대로  중복이되면 code 값에 큰값으로해서 2row만 나오면 됩니다..근데 partition by 를 애기하셨는데 어디에 써야할지 궁금합니다

, ROW_NUMBER() OVER(PARTITION BY yymm, seqno, sno, dtl_seqno ORDER BY COUNT(*) DESC, code DESC) rn