Skip to main content

Using LIMIT and OFFSET

Premium

The LIMIT clause is an important tool in SQL that allows you to control the number of rows returned by a query. This clause is especially useful when you have large datasets and only need to see a portion of the data. In this lesson, we'll explore how to use the LIMIT clause and its various use cases.

The basic syntax for using LIMIT is the following:

SQL
SELECT column_name(s) FROM table_name
LIMIT number_of_rows;

The number_of_rows argument specifies the maximum number of rows to return. For example, if you want to see the first 10 courses in your courses table, you would write the following query:

SQL
SELECT * FROM courses
LIMIT 10;

Finding the Top N results with LIMIT and ORDER BY

One of the most common use cases for LIMIT is when you want to find the "Top N" results within a given set of data. To do this, we need to cleverly combine the LIMIT clause with WHERE and ORDER BY. For example, let’s say we want to find the top 10 SQL courses in our catalog based on the number of students. For this example, let’s imagine we also have the columns student_count, a quantity of students, and category, a text value representing the category of the course.

SQL
SELECT * FROM courses
WHERE category = 'SQL'
ORDER BY student_count DESC
LIMIT 10;

Note that the limit is always applied last in the order of operations:

  1. The WHERE clause filters the data first,
  2. The ORDER BY clause sorts the filtered data in descending order.
  3. Finally, the LIMIT clause returns the first 10 rows of the sorted data.

To perform these steps in a different order, you’ll need to learn about more advanced query patterns like subqueries and CTEs, both of which we’ll explain later in this course.

Try it yourself

Given a table of customers, find the most recently created customer using the created_at column.

Find the N-th result using LIMIT and OFFSET

The LIMIT clause is often used with the OFFSET keyword to “paginate” query results. This can be useful when you want to display a large number of results one page at a time, for example, when displaying search results in an online store or web application. The OFFSET clause specifies the number of rows to skip from the start of the result set, while the LIMIT clause sets the maximum number of rows to return.

For example, to return 10 results per page and display the second page of results, you can use the following query:

SQL
SELECT * FROM courses
ORDER BY course_id
LIMIT 10 OFFSET 10;

To get to the “next page”, you keep the limit the same and increase the offset. When used in conjunction with the ORDER BY clause, you can paginate the results in a specific order.

Combining LIMIT and OFFSET can also be useful if you want to answer a specific data question like "Which is the 3rd least popular course?"

SQL
SELECT * FROM courses
ORDER BY student_count ASC
LIMIT 1 OFFSET 2;

Note: Keep in mind that the order of the rows returned by the LIMIT clause is not guaranteed unless you specify an ORDER BY clause. If you don't specify the order, the rows returned by the LIMIT clause could theoretically be returned in any order. The exact order depends on where the rows are located on disk, the database’s query planner, or when the records were last updated—in other words, unpredictable!

In conclusion, the LIMIT clause is a powerful tool for controlling the number of rows returned by a query, and it can be used in conjunction with other clauses like WHERE, ORDER BY, and OFFSET to create complex queries that retrieve just the data we need. In the next lesson, we'll look at another important SQL clause, the GROUP BY clause, which allows you to group data and perform aggregate functions on it.

Next Lesson