Oracle 12c Tutorial step by step.

How to use Row Limiting Clause in Oracle 12c SQL.

Row limiting clause is introduced in oracle 12c R1.

• Using the row_limiting_clause to limit the number of rows that are returned by a query.

• using this clause to implement Top-N reporting.

• You can specify the percentage of rows or number of rows to return with the FETCH FIRST keyword.

• You can use the OFFSET keyword to specify that the returned rows begin with a row after the first row of the full result set.
• The WITH TIES keyword includes additional rows with the same ordering keys as the last row of the row-limited result set. (You must specify ORDER BY in the query.

You specify the row_limiting_clause in the SQL SELECT statement by placing it after the ORDER BY clause.

Use of FETCH FIRST keyword

The FETCH FIRST keyword show the first five record according order by clause, in following query i used order by clause in ascending order so that’s why it’s showing first top five rows and if you use DESC then it’ll showing also top 5 rows in descending order.

SQL> select empno,ename from emp
     order by empno
     fetch first 5 rows only;

EMPNO      ENAME
---------- ----------
7369       SMITH
7499       ALLEN
7521       WARD
7566       JONES
7654       MARTIN

Use of OFFSET KEYWORD

Offset keyword is show the next top five rows after first top five rows.

SQL> select empno,ename from emp
     order by empno
     offset 5 rows fetch next 5 rows only;

EMPNO      ENAME
---------- ----------
 7698      BLAKE
 7782      CLARK
 7788      SCOTT
 7839      KING
 7844      TURNER

Use of WITH TIES keyword.

Using the WITH TIES clause return first 5 rows from the ordered set and if any other rows is additional ties with the output, it shows in the last.

SQL> select deptno from emp
     order by deptno
     fetch first 5 rows with ties;

 DEPTNO
----------
 10
 10
 10
 20
 20
 20
 20
 20

8 rows selected.