Learn SQL for Developers/

...

/

Limiting Results

Limiting Results

Learn to apply limits on results and use pagination.

We'll cover the following...

Imagine we’re managing an online store with thousands of products, and we need to display only the top 10 best-selling products on the homepage. Fetching all the products and then manually filtering them can be inefficient and time-consuming. By focusing on just a subset of data, we can improve performance, make data easier to browse, and create a smoother user experience. This is where limiting results in SQL queries becomes important. By using SQL clauses like LIMIT and OFFSET, we can retrieve just the data we need without overloading the database or the user interface.

Let's dive into the concept of limiting the results in SQL queries. We will learn to:

  • Use the LIMIT clause to restrict the number of rows returned by a query.

  • Use the OFFSET clause to skip a specific number of rows.

  • Combine LIMIT and OFFSET for paginating results in a user-friendly manner.

The LIMIT clause

When we retrieve data from a table, we may not always need to see all the rows at once. Sometimes, we want to focus on the first few results or just a small sample. The LIMIT clause helps us do exactly that. By setting a limit on the number of rows returned, we make the result set more manageable. This can improve query performance and make our queries more user-friendly, especially when dealing with large datasets.

To use LIMIT, we simply add the clause at the end of our SELECT statement followed by the number of rows we want to return. The syntax of using LIMIT is as follows:

Press + to interact
SELECT Column1, Column2, ...
FROM TableName
LIMIT n;

In the query above, n is the number of required rows. For example, if we want to see just the first 5 products from Products, we can write:

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT * FROM Products
LIMIT 5;

The query above retrieved only the first five rows from the Products table. 

Using LIMIT with ORDER BY

Often, we use ORDER BY to sort results before limiting them. This ensures that we are controlling not just how many rows to display, but also which rows to display first. For example, if we want the 5 most affordable products, we could first sort by Price and then limit the result:

Press + to interact
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT ProductName, Price
FROM Products
ORDER BY Price ASC
LIMIT 5;

In the query above,

  • Line 15: ORDER BY Price ASC sorts the products by price in ascending order.

  • Line 16: LIMIT 5 restricts the results to the first 5 rows.

This ensures that we get only the cheapest products, providing a focused look at a small but meaningful subset of the overall ...