Row limiting using FETCH FIRST

  For Top'N' queries  , Oracle Database 12c introduces a new clause, FETCH FIRST, to simplify the code and comply with ANSI SQL standard guidelines. The clause is used to limit the number of rows returned by a query. The new clause can be used in conjunction with ORDER BY to retrieve Top-N results.

The row limiting clause can be used with the FOR UPDATE clause in a SQL query. In the case of a materialized view, the defining query should not contain the FETCH clause.

Another new clause, OFFSET, can be used to skip the records from the top or middle, before limiting the number of rows. For consistent results, the offset value must be a positive number, less than the total number of rows returned by the query. For all other offset values, the value is counted as zero.

Keywords with the FETCH FIRST clause are:

·FIRST | NEXT—Specify FIRST to begin row limiting from the top. Use NEXT with OFFSET to skip certain rows.

·ROWS | PERCENT—Specify the size of the result set as a fixed number of rows or percentage of total number of rows returned by the query.

·ONLY | WITH TIES—Use ONLY to fix the size of the result set, irrespective of duplicate sort keys. If you want records with matching sort keys, specify WITH TIES.

The following query demonstrates the use of the FETCH FIRST and OFFSET clauses in Top-N queries:

/*Create the test table*/

CREATE TABLE t_fetch_first

(empno VARCHAR2(30),

deptno NUMBER,

sal NUMBER,

hiredate DATE)

/

The following PL/SQL block inserts sample data for testing:

/*Insert the test data in T_FETCH_FIRST table*/

BEGIN

INSERT INTO t_fetch_first VALUES (101, 10, 1500, '01-FEB-2011');

INSERT INTO t_fetch_first VALUES (102, 20, 1100, '15-JUN-2001');

INSERT INTO t_fetch_first VALUES (103, 20, 1300, '20-JUN-2000');

INSERT INTO t_fetch_first VALUES (104, 30, 1550, '30-DEC-2001');

INSERT INTO t_fetch_first VALUES (105, 10, 1200, '11-JUL-2012');

INSERT INTO t_fetch_first VALUES (106, 30, 1400, '16-AUG-2004');

INSERT INTO t_fetch_first VALUES (107, 20, 1350, '05-JAN-2007');

INSERT INTO t_fetch_first VALUES (108, 20, 1000, '18-JAN-2009');

COMMIT;

END;

/

The SELECT query pulls in the top-5 rows when sorted by their salary:

/*Query to list top-5 employees by salary*/

SELECT *

FROM t_fetch_first

ORDER BY sal DESC

FETCH FIRST 5 ROWS ONLY

/

EMPNO       DEPTNO   SAL HIREDATE

-------- ------ ------- ---------

104               30   1550 30-DEC-01

101               10   1500 01-FEB-11

106               30   1400 16-AUG-04

107               20   1350 05-JAN-07

103               20    1300 20-JUN-00

The SELECT query lists the top 25% of employees (2) when sorted by their hiredate:

/*Query to list top-25% employees by hiredate*/

SELECT *

FROM t_fetch_first

ORDER BY hiredate FETCH FIRST 25 PERCENT ROW ONLY

/

EMPNO     DEPTNO SAL HIREDATE

-------- ------ ----- ---------

103       20     1300 20-JUN-00

102       20     1100 15-JUN-01

The SELECT query skips the first five employees and displays the next two—the 6th and 7th employee data:

/*Query to list 2 employees after skipping first 5 employees*/

SELECT *

FROM t_fetch_first

ORDER BY SAL DESC

OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY

/

Regards,

Chandrasekar S.