How to Limit Results in MySQL, PostgreSQL and SQLite
Database:
Operators:
Table of Contents
Problem
You want to limit the number of rows resulting from a query in MySQL, PostgreSQL, or SQLite.
Example
In the exam table, there are names of the students with the results of the exam.
| name | exam_result |
|---|---|
| Janet Morgen | 9 |
| Taya Bain | 11 |
| Anne Johnson | 11 |
| Josh Kaur | 10 |
| Ellen Thornton | 8 |
You want to get the three rows with the best exam results.
Solution
SELECT * FROM exam ORDER BY exam_result DESC LIMIT 3;
The result of the query looks like this:
| name | exam_result |
|---|---|
| Taya Bain | 11 |
| Anne Johnson | 11 |
| Josh Kaur | 10 |
Discussion
First, sort the rows by the exam_result column in descending order using the ORDER BY clause and the DESC keyword. Then, after the ORDER BY clause, use the LIMIT keyword with a number of rows you'd like to return (here, 3).
ORDER BY exam_result DESC LIMIT 3
If you'd like to retrieve three rows randomly instead of the best three, skip the ORDER BY part.
SELECT * FROM exam LIMIT 3;
Of course, you can retrieve any number of rows you want. Just replace 3 with your desired number.