SQL SELECT TOP, LIMIT and ROWNUM Keywords

 In SQL, the SELECT statement is used to query data from a database, and keywords like TOP, LIMIT, and ROWNUM are used to restrict the number of rows returned by the query. However, they are used differently depending on the database system you are working with.

1. SELECT TOP (SQL Server, MS Access)

The TOP keyword is used in SQL Server (and MS Access) to limit the number of rows returned by a query.

Syntax:

SELECT TOP (number) column1, column2, ...
FROM table_name
ORDER BY column_name;

• number: The number of rows to return.
• column_name: The column used to order the rows.

Example:

SELECT TOP 5 * 
FROM Employees
ORDER BY HireDate;

This query will return the first 5 rows ordered by the HireDate.

2. LIMIT (MySQL, PostgreSQL, SQLite)

The LIMIT keyword is used in databases like MySQL, PostgreSQL, and SQLite to limit the number of rows returned by a query.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number;

• number: The number of rows to return.

Example:

SELECT * 
FROM Employees
ORDER BY HireDate
LIMIT 5;

This query will return the first 5 rows ordered by HireDate.

3. ROWNUM (Oracle)

The ROWNUM pseudo-column is used in Oracle to return a unique number for each row in the result set. You can use ROWNUM to limit the number of rows returned.

Syntax:

SELECT column1, column2, ...
FROM (SELECT column1, column2, ...
      FROM table_name
      ORDER BY column_name)
WHERE ROWNUM <= number;

• ROWNUM: Represents the row number for each row in the result set.
• number: The number of rows to return.

Example:

SELECT * 
FROM Employees 
WHERE ROWNUM <= 5
ORDER BY HireDate;

This query returns the first 5 rows ordered by HireDate. However, note that the ORDER BY clause should be used inside a subquery when working with ROWNUM to get the correct order.

Comparison:

• SQL Server: Use TOP.
• MySQL, PostgreSQL, SQLite: Use LIMIT.
• Oracle: Use ROWNUM with a subquery to limit the result set after applying sorting.