100 Days of SQL

Day 76 – SQL SELECT TOP, LIMIT and ROWNUM Keywords

In SQL, the keywords TOP, LIMIT, and ROWNUM are used to limit the number of rows returned in a query result set.

• TOP is used in Microsoft SQL Server and Sybase databases to limit the number of rows returned by a query. The basic syntax is: SELECT TOP n column1, column2, ... FROM table_name WHERE condition; Here, n is the number of rows to be returned, column1, column2, etc. are the names of the columns to be returned, table_name is the name of the table to query, and condition is an optional condition to filter the results.
• LIMIT is used in MySQL, PostgreSQL, SQLite, and some other databases to limit the number of rows returned by a query. The basic syntax is: SELECT column1, column2, ... FROM table_name WHERE condition LIMIT n; Here, n is the number of rows to be returned, column1, column2, etc. are the names of the columns to be returned, table_name is the name of the table to query, and condition is an optional condition to filter the results.
• ROWNUM is used in Oracle databases to limit the number of rows returned by a query. The basic syntax is: SELECT column1, column2, ... FROM ( SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column_name ) WHERE ROWNUM <= n; Here, n is the number of rows to be returned, column1, column2, etc. are the names of the columns to be returned, table_name is the name of the table to query, condition is an optional condition to filter the results, and column_name is the name of the column used to sort the results.

For example, suppose we have a table called “employees” with columns “id”, “name”, and “salary”. To return the top 10 highest paid employees in Microsoft SQL Server, we can use the following query:

SELECT TOP 10 name, salary
FROM employees
ORDER BY salary DESC;

To achieve the same result in MySQL, we can use the following query:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

To achieve the same result in Oracle, we can use the following query:

SELECT name, salary
FROM (
  SELECT name, salary
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 10;