WITH TMP AS

(

SELECT 101 TID, 3 SEQ, '20150101' REG_DT, '0002' REG_NO, 'Y' USE_YN FROM DUAL

UNION

SELECT 101 TID, 2 SEQ, '20130101' REG_DT, '0002' REG_NO, 'N' USE_YN FROM DUAL

UNION

SELECT 101 TID, 1 SEQ, '20110101' REG_DT, '0001' REG_NO, 'N' USE_YN FROM DUAL

UNION

SELECT 102 TID, 5 SEQ, '20170101' REG_DT, '0003' REG_NO, 'Y' USE_YN FROM DUAL

UNION

SELECT 102 TID, 3 SEQ, '20160101' REG_DT, '0002' REG_NO, 'N' USE_YN FROM DUAL

UNION

SELECT 102 TID, 1 SEQ, '20150101' REG_DT, '0001' REG_NO, 'N' USE_YN FROM DUAL

UNION

SELECT 103 TID, 2 SEQ, '20170101' REG_DT, '0002' REG_NO, 'Y' USE_YN FROM DUAL

UNION

SELECT 103 TID, 1 SEQ, '20160101' REG_DT, '0001' REG_NO, 'N' USE_YN FROM DUAL

UNION

SELECT 104 TID, 1 SEQ, '20170101' REG_DT, '0001' REG_NO, 'Y' USE_YN FROM DUAL

)

SELECT *

FROM TMP T1

WHERE 1=1

;

-- 결과조건

-- 결과값A : TID로 그룹핑하여 USE_YN = 'Y'인 값 (그룹중 1row만 존재함)

-- 결과값B : TID로 그룹핑하여 USE_YN = 'N'인 것들중 REG_DT DESC, REG_NO DESC 로 정렬시 제일 높은값

-- 결과데이터

-- TID | SEQ(결과값A) | SEQ(결과값B)

-- 101 3 2

-- 102 5 3

-- 103 2 1

-- 104 1 0 (by NVL)

이런결과값을 추출하고싶습니다. lead 함수도 사용해보았지만 잘안되네요..

도움주시면 감사하겠습니다.