Online College Courses for Credit

+
LIMIT and OFFSET to Cap Results

LIMIT and OFFSET to Cap Results

Rating:
(0)
Author: Sophia Tutorial
Description:

Given a query with 100s of ordered records in the result set, modify to utilize LIMIT and OFFSET to view the first 10 results, as well as 10 results that appear in the “middle” of the data.

(more)
See More
Tutorial

what's covered
This tutorial explores using the LIMIT and OFFSET clauses to cap results in two parts:
  1. LIMIT Clause
  2. OFFSET Clause

1. LIMIT Clause

The LIMIT clause helps to constrain the number of rows that are returned by the query. It is an optional clause added to the end of the SELECT statement.


SELECT <columns>
FROM <tablename>
LIMIT <rowcount>;

For example, let’s take a look at invoices based on their total values in descending order:


SELECT *
FROM invoice
ORDER BY total DESC;

This ends up returning all of the rows (412 in this case), although in a larger database, this can take a long time to return all rows:

Query Result Example

If we were only interested in looking at the top five, we could add the LIMIT clause to only return the top five rows:


SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5;

Query Result Example

You will see the same top five rows as the prior query, but it returns much faster. Exploring data in small sets can be more efficient. Think about how you search results on a search engine. The results do not return every single result on the same page; it would simply be too slow to do so. Instead, they return only the top few results.

Let’s look at another example of invoices that have the billing_country as Belgium in descending order:


SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC;

This should return seven rows:

Query Result Example

If we had set the LIMIT to 10:


SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10;

The result set would stay the same with seven rows:

Query Result Example


2. OFFSET Clause

If we want to skip a number of rows before returning the rows, we can use the OFFSET clause that is placed after the LIMIT clause.


SELECT <columns>
FROM <tablename>
LIMIT <rowcount>
OFFSET <rowstoskip>;

By doing this, the statement will skip the first number in the OFFSET and then return the number of rows based on the LIMIT. A search engine having multiple pages of content would work in a very similar way.

Using our invoice example, perhaps we are looking through each set of invoices five lines at a time. To look at the next five rows, we would run the query using:


SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5
OFFSET 5;

Query Result Example


If we wanted to look at the next five after that:


SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5
OFFSET 10;

Query Result Example

Using the OFFSET and LIMIT, we can jump to any subset of rows being returned within the SELECT statement.

Looking at our prior example of invoices in Belgium:


SELECT 
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10;

Query Result Example

The result set had returned seven rows but look at what happens if we added the OFFSET to 5.


SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10
OFFSET 5;

This would similarly skip the first five rows and return the number of rows remaining up to 10 as the LIMIT was set to 10:

Query Result Example


If we had added an OFFSET of 10, this would be more than the rows available to return:


SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10
OFFSET 10;

As such, this would simply return 0 rows:

Successful Query Result Message



try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The LIMIT and OFFSET clauses are used to cap records returned from SELECT statements.

Source: Authored by Vincent Tran