SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

In SQL, the TOP, LIMIT, FETCH FIRST, and ROWNUM clauses are used to limit the number of rows returned by a query. The specific clause used depends on the SQL database management system (DBMS) you're working with. Here are examples and outputs for each of these clauses across different DBMS:

1. TOP Clause (SQL Server)

Example:

SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;

Output: This query returns the top 5 employees with the highest salaries from the Employees table.

2. LIMIT Clause (MySQL, PostgreSQL, SQLite)

Example:

SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

Output: This query returns the top 5 employees with the highest salaries from the Employees table.

3. FETCH FIRST Clause (DB2, Oracle 12c and later)

Example:

SELECT *
FROM Employees
ORDER BY Salary DESC
FETCH FIRST 5 ROWS ONLY;

Output: This query returns the top 5 employees with the highest salaries from the Employees table.

4. ROWNUM Clause (Oracle)

Example:

SELECT *
FROM (
    SELECT *
    FROM Employees
    ORDER BY Salary DESC
)
WHERE ROWNUM <= 5;

Output: This query returns the top 5 employees with the highest salaries from the Employees table.

Summary

• SQL Server: Use TOP clause.
• MySQL, PostgreSQL, SQLite: Use LIMIT clause.
• DB2, Oracle 12c+: Use FETCH FIRST clause.
• Oracle (any version): Use ROWNUM clause.

Each clause is designed to perform the same task of limiting the number of rows returned, but they are syntactically different to accommodate the respective SQL DBMS.