SQL SELECT TOP, LIMIT and ROWNUM Keywords

 In SQL, the keywords SELECT TOP, LIMIT, and ROWNUM are used to limit the number of rows returned in a query. However, their usage depends on the SQL database management system (DBMS) you're working with. Here's an overview of each one:

1. SELECT TOP (SQL Server, MS Access)

The SELECT TOP keyword is used to specify the number of rows to return from the query result. It is commonly used in Microsoft SQL Server and MS Access.

Syntax:

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

• n specifies the number of rows to return.
• ORDER BY is optional but often used to determine the order of rows.

Example:

SELECT TOP 5 Name, Age
FROM Employees
ORDER BY Age DESC;

This query retrieves the top 5 employees with the highest age.

2. LIMIT (MySQL, PostgreSQL, SQLite, MariaDB)

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

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1
LIMIT n;

• n specifies the number of rows to return.

Example:

SELECT Name, Age
FROM Employees
ORDER BY Age DESC
LIMIT 5;

This query retrieves the top 5 employees with the highest age in MySQL or PostgreSQL.

3. ROWNUM (Oracle)

In Oracle, the ROWNUM keyword is used to limit the number of rows returned. ROWNUM is a pseudo-column that assigns a unique number to each row returned by a query.

Syntax:

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

• ROWNUM is assigned incrementally starting at 1 for the first row.
• n specifies the maximum number of rows to return.

Example:

SELECT Name, Age
FROM Employees
WHERE ROWNUM <= 5
ORDER BY Age DESC;

This query retrieves the top 5 employees based on age, but note that ROWNUM is applied before sorting. For correctly ordered results, you need to wrap the query in a subquery:

SELECT Name, Age
FROM (
    SELECT Name, Age
    FROM Employees
    ORDER BY Age DESC
)
WHERE ROWNUM <= 5;

Summary:

• SQL Server/MS Access: Use SELECT TOP (n).
• MySQL/PostgreSQL/SQLite/MariaDB: Use LIMIT n.
• Oracle: Use ROWNUM with a subquery for ordered results.

Each of these keywords helps to control the amount of data retrieved in a query, making it useful for performance optimization or when working with large datasets.