TOP-N Query

SELECT employee_id, salary, ROWNUM AS rn FROM employees WHERE ROWNUM = 2; -- 조회되지 않음

SELECT employee_id, salary, ROWNUM AS rn FROM employees WHERE ROWNUM <= 2;

SELECT employee_id, salary, ROWNUM AS rn FROM employees WHERE ROWNUM <= 2;

SELECT employee_id, salary, ROWNUM AS rn FROM employees WHERE ROWNUM <=5 ORDER BY salary; -- 부정확한 결과값 도출

SELECT employee_id, salary, ROWNUM AS rn
 FROM (SELECT employee_id, salary FROM employees ORDER BY salary, employee_id)
WHERE ROWNUM <=5;

SELECT employee_id, salary
 FROM (SELECT employee_id, salary FROM employees ORDER BY DBMS_RANDOM.VALUE) -- ORDER BY 랜덤으로 처리 / Context Swithcing 성능저하 이슈 가능
WHERE ROWNUM <=3; 

SELECT a.* FROM departments a 
 WHERE EXISTS (SELECT 1 FROM employees b
                WHERE b.department_id = a.department_id);

SELECT a.department_id,
    (SELECT MAX(b.employee_id) FROM employees b WHERE b.department_id = a.department_id) AS empno
 FROM departments a; 
 
SELECT a.department_id,
     (SELECT MAX(b.employee_id) KEEP (DENSE_RANK FIRST ORDER BY salary DESC) -- 급여 많은 기준으로 employee_id를 가져옴 / KEEP 절 성능 저하 우려 존재
        FROM employees b
      WHERE b.department_id = a.department_id) AS empno
 FROM departments a;
 
SELECT a.department_id,
      (SELECT employee_id FROM (SELECT b.employee_id, b.department_id FROM employees b 
                                 WHERE b.department_id = a.department_id ORDER BY b.salary DESC, b.employee_id DESC) b
        WHERE ROWNUM <= 1) AS empno                          
 FROM departments a;

SELECT *
  FROM (SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary, employee_id) AS rn FROM employees)
 WHERE rn < 5
ORDER BY salary, employee_id; 

SELECT employee_id, salary, pr
  FROM (SELECT employee_id, salary, PERCENT_RANK() OVER (ORDER BY salary, employee_id) AS pr FROM employees)
 WHERE pr < 0.25
ORDER BY salary, employee_id;

SELECT employee_id, salary, rk
  FROM (SELECT employee_id, salary, RANK() OVER (ORDER BY salary) AS rk FROM employees)
 WHERE rk <= 6
ORDER BY SALARY;  -- 동일 등수 있을 시 공동으로 처리, 다음 값은 비워둔다.

SELECT employee_id, salary, dr
  FROM (SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY SALARY) AS dr FROM employees)
WHERE dr <= 6
ORDER BY SALARY;  -- 동일 등수 있을 시 공동으로 처리, 다음 등수로 이어서 처리

SELECT employee_id, salary FROM employees ORDER BY salary, employee_id FETCH FIRST 5 ROWS ONLY;
-- 12.1 버전부터 제공

SELECT employee_id, salary FROM employees ORDER BY salary, employee_id OFFSET 5 ROWS;
-- 5 ROW를 건너뛰고 그 다음부터 반환

SELECT employee_id, salary FROM employees ORDER BY salary, employee_id FETCH FIRST 25 PERCENT ROWS ONLY;

SELECT employee_id, salary FROM employees ORDER BY salary FETCH FIRST 6 ROWS WITH TIES; -- 동순위 처리가능

SELECT employee_id, salary, department_id, department_name
  FROM (SELECT a.employee_id, a.salary, a.department_id, b.department_name
          FROM employees a, departments b
         WHERE b.department_id(+) = a.department_id
        ORDER BY a.salary, a.employee_id)
 WHERE ROWNUM <= 2;
 -- employees 테이블과 departments 테이블은 조인차수가 M:1이고,
 -- 아우터 조인으로 조인했기 때문에 employees 테이블을 TOP-N 처리한 후 department 테이블 조인해도 동일한 결과 추출가능

SELECT a.employee_id, a.salary, a.department_id, b.department_name
 FROM (SELECT * FROM 
          (SELECT employee_id, salary, department_id FROM employees ORDER BY salary, employee_id)
        WHERE ROWNUM <= 2 ) a
      , departments b
 WHERE b.department_id(+) = a.department_id
ORDER BY a.salary, a.employee_id;

SELECT a.employee_id, a.salary, a.department_id, 
     (SELECT b.department_name FROM departments b WHERE b.department_id = a.department_id) AS department_name
  FROM (SELECT employee_id, salary, department_id from employee_id ORDER BY salary, employee_id) a
WHERE ROWNUM <= 2; -- 1개의 열만 조회할 경우 TOP-N 처리 후 스칼라 서브 쿼리 사용 가능