Back to cookbooks list Articles Cookbook

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.

nameexam_result
Janet Morgen9
Taya Bain11
Anne Johnson11
Josh Kaur10
Ellen Thornton8

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:

nameexam_result
Taya Bain11
Anne Johnson11
Josh Kaur10

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.

Recommended courses:

Recommended articles:

See also: