How to Limit Results in MySQL, PostgreSQL and SQLite

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.

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:

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.