WITH T AS ( SELECT 111 NO, '칼세이건' NM, TO_DATE('2019-11-01','YYYY-MM-DD') D FROM DUAL UNION ALL SELECT 222 NO, '페러데이' NM, TO_DATE('2019-11-02','YYYY-MM-DD') D FROM DUAL UNION ALL SELECT 333 NO, '테슬라' NM, TO_DATE('2019-11-03','YYYY-MM-DD') D FROM DUAL UNION ALL SELECT 444 NO, '모건' NM, TO_DATE('2019-11-04','YYYY-MM-DD') D FROM DUAL ) SELECT * FROM ( SELECT T.*, ROW_NUMBER() OVER(ORDER BY D) S_NUM, ROW_NUMBER() OVER(ORDER BY D DESC) E_NUM FROM T ) WHERE (S_NUM = 1 OR E_NUM = 1)
같은날 입사한 사원까지 고려한다면?
row_number 대신 rank 를 사용하면 됩니다.
공부중 이라면? rank 등의 분석함수 사용법 외에도
서브쿼리나 셀프조인 등 다양한 방법을 시도해 보셔야 합니다.
SELECT empno, ename, hiredate FROM (SELECT empno, ename, hiredate , RANK() OVER(ORDER BY hiredate) rk1 , RANK() OVER(ORDER BY hiredate DESC) rk2 FROM emp ) WHERE 1 IN (rk1, rk2) ; SELECT empno, ename, hiredate FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp) OR hiredate = (SELECT MAX(hiredate) FROM emp) ; SELECT empno, ename, hiredate FROM emp WHERE hiredate IN (SELECT MIN(hiredate) FROM emp UNION ALL SELECT MAX(hiredate) FROM emp ) ; SELECT a.empno, a.ename, a.hiredate FROM emp a , (SELECT MIN(hiredate) min_dt , MAX(hiredate) max_dt FROM emp ) b WHERE a.hiredate IN (b.min_dt, b.max_dt) ; SELECT a.empno, a.ename, a.hiredate FROM emp a LEFT OUTER JOIN emp b ON a.hiredate > b.hiredate WHERE b.hiredate IS NULL UNION ALL SELECT a.empno, a.ename, a.hiredate FROM emp a LEFT OUTER JOIN emp b ON a.hiredate < b.hiredate WHERE b.hiredate IS NULL ;