-- 답에만 맞췄어요., WITH T ( 구분,출발,도착) AS ( SELECT 'A' , '09' ,'10' FROM DUAL UNION ALL SELECT 'A' , '10' ,'12' FROM DUAL UNION ALL SELECT 'A' , '16' ,'18' FROM DUAL UNION ALL SELECT 'B' , '09' ,'10' FROM DUAL UNION ALL SELECT 'B' , '10' ,'13' FROM DUAL UNION ALL SELECT 'B' , '17' ,'18' FROM DUAL UNION ALL SELECT 'C' , '13' ,'16' FROM DUAL UNION ALL SELECT 'C' , '16' ,'17' FROM DUAL UNION ALL SELECT 'D' , '14' ,'18' FROM DUAL UNION ALL SELECT 'D' , '19' ,'20' FROM DUAL UNION ALL SELECT 'F' , '09' ,'11' FROM DUAL ) SELECT * FROM (SELECT 구분 , 출발 , 도착 , ROW_NUMBER() OVER(PARTITION BY 구분 ORDER BY 출발 DESC ) RN FROM T WHERE 출발 < 14 AND 도착 < 16 ) WHERE RN = 1
WITH t AS ( SELECT 'A' id, '09' stm, '10' etm FROM dual UNION ALL SELECT 'A', '10', '12' FROM dual UNION ALL SELECT 'A', '16', '18' FROM dual UNION ALL SELECT 'B', '09', '10' FROM dual UNION ALL SELECT 'B', '10', '13' FROM dual UNION ALL SELECT 'B', '17', '18' FROM dual UNION ALL SELECT 'C', '13', '16' FROM dual UNION ALL SELECT 'C', '16', '17' FROM dual UNION ALL SELECT 'D', '14', '18' FROM dual UNION ALL SELECT 'D', '19', '20' FROM dual UNION ALL SELECT 'F', '09', '11' FROM dual ) SELECT b.id , MAX(c.stm) s , MAX(c.etm) e FROM dual a LEFT OUTER JOIN t b PARTITION BY (b.id) ON b.stm < '16' AND b.etm > '14' LEFT JOIN t c ON b.id = c.id AND c.etm <= '14' WHERE b.stm IS NULL GROUP BY b.id ;