SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause
The TOP, LIMIT, FETCH FIRST, and ROWNUM clauses are used to specify the number of records to return in the result set.
SQL Server: TOP
The TOP clause is used in SQL Server to limit the number of rows returned.
SELECT TOP 3 * FROM employees;This query returns the first 3 records from the “employees” table.
MySQL: LIMIT
The LIMIT clause is used in MySQL to specify the number of records to return.
SELECT * FROM employees LIMIT 3;This query returns the first 3 records from the “employees” table.
PostgreSQL: LIMIT
The LIMIT clause is also used in PostgreSQL to limit the number of rows returned.
SELECT * FROM employees LIMIT 3;This query returns the first 3 records from the “employees” table.
Oracle: ROWNUM
The ROWNUM pseudo-column is used in Oracle to limit the number of rows returned.
SELECT * FROM employees WHERE ROWNUM <= 3;This query returns the first 3 records from the "employees" table.
Standard SQL: FETCH FIRST
The FETCH FIRST clause is used in standard SQL to limit the number of rows returned.
SELECT * FROM employees FETCH FIRST 3 ROWS ONLY;This query returns the first 3 records from the "employees" table.
Example 1: Using TOP with ORDER BY
SELECT TOP 5 * FROM employees ORDER BY salary DESC;This query returns the top 5 highest-paid employees.
Example 2: Using LIMIT with OFFSET
SELECT * FROM employees ORDER BY employee_id LIMIT 3 OFFSET 2;This query skips the first 2 records and returns the next 3 records from the "employees" table.
Example 3: Using ROWNUM with ORDER BY
SELECT * FROM
(SELECT * FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 5;This query returns the top 5 highest-paid employees in Oracle.
Example 4: Using FETCH FIRST with ORDER BY
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;This query returns the top 5 highest-paid employees using standard SQL.
The TOP, LIMIT, FETCH FIRST, and ROWNUM clauses are powerful tools for controlling the number of rows returned by a query, making them essential for efficient data retrieval in large databases.