Sometimes, you might not need all the rows that a query returns. For example, you might just want to find the top 10 newest employees in the organization, or the top 3 students by score, and so on.
To deal with such cases, you can use the TOP clause in your SELECT statement in SQL. However, the TOP clause is only available in SQL Server and MS Access databases.
MySQL uses a similar LIMIT clause, while Oracle uses the ROWNUM clause in the SELECT statement to limit the number of rows returned by a query.
The TOP clause in SQL is used to specify the number of rows to be returned. The basic syntax is:
number | percent column_list FROM table_name;In this syntax, column_list is a list of column or field names from a database table (like name, age, country, etc.) whose values you want to retrieve. Let's see how it works.
Imagine we have an employees table in our database with the following records:
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 | | 2 | Tony Montana | 2002-07-15 | 6500 | 1 | | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 3 | | 5 | Martin Blank | 2008-06-24 | 5600 | NULL | +--------+--------------+------------+--------+---------+
The following query retrieves the top three highest-paid employees from the employees table.
-- Syntax for SQL Server Database
SELECT TOP 3 * FROM employees
ORDER BY salary DESC;The resulting set will appear similar to this:
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 3 | | 2 | Tony Montana | 2002-07-15 | 6500 | 1 | +--------+--------------+------------+--------+---------+
You can also use the PERCENT keyword after a fixed value in a TOP clause if you want to retrieve a percentage of the rows instead of a fixed number. Fractional values are rounded up to the nearest whole number (e.g., 1.5 is rounded to 2).
The following query retrieves the top 30 percent of the highest-paid employees.
-- Syntax for SQL Server Database
SELECT TOP 30 PERCENT * FROM employees
ORDER BY salary DESC;The resulting set from the above query will appear like this:
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 3 | +--------+--------------+------------+--------+---------+
The LIMIT clause in MySQL serves the same purpose as the TOP clause in SQL. Its basic syntax is:
column_list FROM table_name LIMIT number;The following query retrieves the top three highest-paid employees from the employees table.
-- Syntax for MySQL Database
SELECT * FROM employees
ORDER BY salary DESC LIMIT 3;After executing the query, the output will look something like this:
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 3 | | 2 | Tony Montana | 2002-07-15 | 6500 | 1 | +--------+--------------+------------+--------+---------+
Note: When using a SELECT statement, always include an ORDER BY clause with the LIMIT clause. Without it, you might not get the expected result.
The LIMIT clause in MySQL also accepts an optional second parameter.
When using two parameters, the first parameter determines the offset of the first row to return, which starts from 0, not 1. The second parameter specifies the maximum number of rows to retrieve.
For example, to find the third-highest paid employee, you can use the following query:
-- Syntax for MySQL Database
SELECT * FROM employees
ORDER BY salary DESC LIMIT 2, 1;After executing the above query, your result set will contain only one record.
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 2 | Tony Montana | 2002-07-15 | 6500 | 1 | +--------+--------------+------------+--------+---------+