Oracle FETCH 함수

1. TOP-N (12c 이전)

SELECT *
FROM ( SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold)
WHERE rownum <= 5;

2. FETCH (12c)

SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 ROWS ONLY;

응용 case 1. amount_sold 높은 순, 순위 중복 허용
SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold DESC
FETCH FIRST 5 ROWS WITH TIES;

응용 case 2. 특정 구간
SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
OFFSET 5
FETCH NEXT 5 ROWS ONLY;

cf. FETCH 함수 SYNTAX
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT }[ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ]

편.안.
출처: https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1