– TOP -K
SELECT ROWNUM,EMPLOYEE_ID, SALARY
FROM EMPLOYEES
WHERE HIRE_DATE LIKE ‘06%’
ORDER BY SALARY
원하는대로 출력이 되지 않을 것이다.
이를 원하는대로 출력하기 위해서는 다음과 같이!
SELECT ROWNUM, EMPLOYEE_ID, SALARY
FROM (
SELECT EMPLOYEE_ID, SALARY
FROM EMPLOYEES
WHERE HIRE_DATE LIKE ‘06%’
ORDER BY SALARY
);
SELECT ROWNUM, EMPLOYEE_ID, SALARY
FROM (
SELECT EMPLOYEE_ID, SALARY
FROM EMPLOYEES
WHERE HIRE_DATE LIKE ‘06%’
ORDER BY SALARY
)
WHERE ROWNUM <= 3 ;
–앞에서 3개 뽑는것
SELECT ROWNUM, EMPLOYEE_ID, SALARY
FROM (
SELECT EMPLOYEE_ID, SALARY
FROM EMPLOYEES
WHERE HIRE_DATE LIKE ‘06%’
ORDER BY SALARY
)
WHERE 6 >= ROWNUM AND ROWNUM <= 10;
–이것은 원하는 결과가 나오지 않는다.
1이 찍히지 않으므로 그렇다. 이로인해 ROWNUM이 증가하지 않음
SELECT * FROM
(
SELECT ROWNUM AS RN, EMPLOYEE_ID, SALARY
FROM (
SELECT EMPLOYEE_ID, SALARY
FROM EMPLOYEES
WHERE HIRE_DATE LIKE ‘06%’
ORDER BY SALARY
)
)
WHERE RN >=6 AND RN <=10;
– 위의 문제를 이렇게 해결함.