5) SQL Commands for Querying Databases Lesson

LIMIT

3 min to complete · By Ryan Desmond

SQL: Limit

The SQL LIMIT query is just a nice little utility statement that will help you LIMIT the number of results you get back.

How does SQL Limit Work?

To use the LIMIT statement in SQL, you place it at the end of your query to limit the results to a certain number.

SQL Limit Examples

Below are three examples of the SQL LIMIT query using the customer and payment tables we created earlier in the Sakila database. If you don't already have the Sakila DB installed, follow the steps to install the Sakila DB, then head back here.

1. Limit 10 from 1 Table

Select 10, and only 10 customers:

SELECT * FROM customer
LIMIT 10;
Sakila.Customer limit results to 10

2. Limit 5 from 2 Tables

Now, take the query you ran in the previous GROUP BY example, where you got the total payments for all customers. But this time, LIMIT it to 5 results to just get your 5 best customers, so you can send them a thank you gift over the holidays :)

SELECT c.first_name, c.last_name, SUM(p.amount) as total_payments
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY c.customer_id
ORDER BY total_payments DESC
LIMIT 5;
Sakila.Customer table joined with payment and limit to 5

Avoid this Error with SQL Limit!

The SQL LIMIT query must always be the last and final clause in the query. Otherwise, you will get an error. For instance, the following query will fail because the LIMIT statement is not last in the query.

SELECT c.first_name, c.last_name, SUM(p.amount) as total_payments
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY c.customer_id
LIMIT 5  
ORDER BY total_payments DESC;

To avoid errors, always put SQL LIMIT at the end of your query.

Summary: SQL Limit Query

  • SQL LIMIT can be placed at the end of an SQL query to limit the results to a certain number.
  • The LIMIT keyword is followed by a number
  • LIMIT must come as the last element in the query

Next, check out a video going over ORDER BY and LIMIT in action!