Row limiting clause TOP ROWNUM LIMIT FETCH

Learn how to fetch top N rows using SQL TOP, ROWNUM, LIMIT, FETCH clauses with example queries.

Row Limiting clause is useful to limit the number of records to return on large tables with thousands of records.
TOP, ROWNUM, LIMIT, FETCH are Row Limiting clauses used to specify the number of records to return.

TOP — Used in Microsoft SQL server Database
LIMIT — Used in MySQL Database
ROWNUM, FETCH used in Oracle Database

Below is the syntax for all above Row Limiting clauses

SQL TOP Syntax

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

SQL LIMIT Syntax:

SELECT column_name1,column_name2
FROM table_name
WHERE condition
LIMIT number;

SQL ROWNUM Syntax

SELECT column_name1,column_name2
FROM table_name
WHERE ROWNUM = num;

SQL FETCH Syntax

SELECT column_name1,column_name2 
FROM table_name
WHERE condition
[ OFFSET offset ROWS]
 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

FETCH is similar to SQL LIMIT clause That retrieves the Top N rows ( FETCH clause specifies the number of rows or percentage of rows to return)

OFFSET clause specifies the number of rows to skip before the row limiting starts. clause specifies the number of rows to skip before the row limiting starts.

OFFSET clause is optional. If you skip it, then offset is 0 and row limiting starts with the first row.

ONLY | WITH TIES

The ONLY returns exactly the number of rows or percentage of rows after FETCH NEXT (or FIRST).

The WITH TIES returns additional rows with the same sort key as the last row fetched.

NOTES

If you use WITH TIES, you must specify an ORDER BY clause in the query. If you don’t, the query will not return the additional rows.

Query to get TOP N ROWS

SELECT TOP 10 * 
FROM EMP;     -- SQL SERVER

---
SELECT * 
FROM EMP LIMIT 10;  --MYSQL
--
SELECT * 
FROM EMP 
WHERE ROWNUM <= 10; --ORACLE
--

SELECT * 
FROM EMP 
FETCH FIRST 10 ROWS ONLY; --ORACLE

Limit by percentage of rows example in oracle

SELECT TOP 50 PERCENT *
FROM EMPLOYEE   --SQL SERVER

SELECT * 
 FROM EMPLOYEE 
 FETCH FIRST 10 PERCENT ROWS ONLY; --ORACLE

Total rows in emp table is 14
Required percentage of rows is 10

Then 10 percents of rows is 14/10
i.e, 1.4 rounded to 2

WITH TIES example

SELECT * 
FROM EMPLOYEE  order by sal desc
FETCH FIRST 10  ROWS with ties; 

OFFSET EXAMPLE

SELECT * 
FROM EMPLOYEE 
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;