LIMIT clause
LIMIT
is an output modifier. Logically it is applied at the end of the query, and the LIMIT
clause restricts the number of rows fetched.
Note that while LIMIT
can be used without an ORDER BY
clause, the results might not be deterministic without the ORDER BY
clause. This can still be useful, for example, when you want to inspect a quick snapshot of the data.
Basic LIMIT
clause examples:
-- provide the result set by the average salary in descending order and return only the first 5 rows of the result set
SELECT department, job_title, AVG(salary)
FROM employees
ORDER BY AVG(salary) DESC
LIMIT 5;
The ORDER BY
clause sorts the rows in the result set based on the value of the specified expression, in this case the average salary.
It's possible to remove the GROUP BY
clause from the query and retrieve the first 5 rows of the result set without sorting the data, as shown in the example below. However, the result set will contain 5 random rows from the employees
table, as the data is not sorted.
-- provide the result set by the average salary without sorting and return only the first 5 rows of the result set
SELECT department, job_title, AVG(salary)
FROM employees
LIMIT 5;