SQL SELECT TOP, LIMIT and ROWNUM Keywords

 In SQL, TOP, LIMIT, and ROWNUM are used to restrict the number of rows returned by a query. However, they are used differently across different SQL database systems. Here's a breakdown of how they work:

1. TOP (SQL Server, MS Access)

In SQL Server and MS Access, the TOP keyword is used to limit the number of rows returned in a result set.

Syntax:

SELECT TOP n column1, column2, ...
FROM table_name
WHERE condition;

• n: The number of rows to return.
• This can be a constant or a percentage.

Example:

SELECT TOP 5 * FROM Employees;

This query will return the first 5 rows from the Employees table.

2. LIMIT (MySQL, PostgreSQL, SQLite)

In MySQL, PostgreSQL, and SQLite, LIMIT is used to restrict the number of rows returned.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
LIMIT n;

• n: The number of rows to return.
• In PostgreSQL, you can also use LIMIT with an OFFSET to control the starting row.

Example:

SELECT * FROM Employees
LIMIT 5;

This query will return the first 5 rows from the Employees table.

3. ROWNUM (Oracle)

In Oracle, ROWNUM is a pseudo-column that is assigned to rows in the order in which they are retrieved from the database. It can be used to limit the number of rows returned by a query.

Syntax:

SELECT column1, column2, ...
FROM (SELECT column1, column2, ...
      FROM table_name
      WHERE condition)
WHERE ROWNUM <= n;

• ROWNUM is assigned before ORDER BY is applied, so it's important to be cautious when using it with an ORDER BY clause.

Example:

SELECT * FROM Employees
WHERE ROWNUM <= 5;

This query will return the first 5 rows from the Employees table based on the order in which they are retrieved by the query. To apply ROWNUM after sorting, use a subquery:

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

This will return the first 5 rows after sorting the Employees table by the hire_date in descending order.

Summary:

• SQL Server uses TOP to limit rows.
• MySQL, PostgreSQL, and SQLite use LIMIT.
• Oracle uses ROWNUM, often requiring a subquery when combined with ORDER BY.

Different SQL databases have different methods of achieving the same goal, and it's important to use the correct one depending on the database you're working with.