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 constraint 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 end up taking a long time to return all rows:

File:11276-1320-1.png

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

SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5;

File:11277-1320-2.png

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 just 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 7 rows:

File:11278-1320-3.png

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 7 rows:

File:11279-1320-4.png


2. OFFSET clause

If we want to skip a number of rows before returning the rows, we can use the OFFSET clause that’s 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 it using:

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

File:11280-1320-5.png


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

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

File:11281-1320-6.png

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;

File:11282-1320-7.png

The result set had returned 7 rows but 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 5 rows and return the number of rows remaining up to 10 as the LIMIT was set to 10:

File:11283-1320-8.png


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:

File:11284-1320-9.png



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.