ROWNUM, TOP-N, 페이징

1. ROWNUM

SELECT ROWNUM AS RN
     , 직원ID
     , 이름
     , 연봉
     , 부서ID
  FROM 직원;

SELECT ROWNUM AS RN
     , 직원ID
     , 이름
  FROM 직원
 WHERE ROWNUM <= 3;

SELECT *
  FROM (
        SELECT ROWNUM AS RN
             , 직원ID
             , 이름
          FROM 직원
        )
 WHERE RN = 2;

 

2. TOP - N

SELECT *
  FROM 직원
 WHERE 연봉 IS NOT NULL
 ORDER BY 연봉 DESC;

SELECT *
  FROM (
        SELECT *
          FROM 직원
         WHERE 연봉 IS NOT NULL
         ORDER BY 연봉 DESC
        )
 WHERE ROWNUM <= 5;

SELECT *
  FROM (
        SELECT *
          FROM 직원
         WHERE 연봉 IS NOT NULL
         ORDER BY 연봉
        )
 WHERE ROWNUM <= 3;

SELECT *
  FROM (
        SELECT *
          FROM 직원
         WHERE 입사일시 IS NOT NULL
         ORDER BY 입사일시 DESC
        )
 WHERE ROWNUM <= 3;

 

3. 페이징

SELECT *
  FROM (
        SELECT *
          FROM 게시판
         ORDER BY 작성일시 DESC
        )
 WHERE ROWNUM <= 20;

SELECT *
  FROM (
        SELECT *
          FROM 게시판
         ORDER BY 작성일시 DESC
        )
 WHERE ROWNUM <= 40;

SELECT *
 FROM (
        SELECT ROWNUM AS RN
             , A.*
        FROM (
            SELECT *
              FROM 게시판
             ORDER BY 작성일시 DESC
        ) A
        WHERE ROWNUM <= 40
        )
 WHERE ROWNUM >= 21;