💡 Ask Tutor

Chapter 9: Limiting Results with LIMIT/TOP

Imagine a website showing search results or a list of blog posts. You wouldn’t want to load thousands of items on a single page! Not only would it be overwhelming for the user, but it would also be incredibly slow to load. This is where the ability to limit the number of rows returned by your query becomes essential.

The LIMIT clause (or TOP for some SQL dialects) allows you to restrict the number of rows returned by a SELECT statement. This chapter will teach you how to retrieve a specific number of records, and how to implement pagination, a core feature of most dynamic web applications.

The Purpose of LIMIT / TOP

  • LIMIT: Used primarily in MySQL, PostgreSQL, SQLite, and MariaDB. It allows you to specify a maximum number of rows to return, and optionally, an offset (how many rows to skip from the beginning).
  • TOP: Used in SQL Server and MS Access. It allows you to specify the number or percentage of rows to return from the top of the result set.

Since this tutorial focuses on MySQL/MariaDB, we will primarily use the LIMIT clause. However, a brief note on TOP will be included for awareness.

When to Use LIMIT

  • Pagination: Displaying a fixed number of results per page (e.g., “10 posts per page”).
  • “Show More” Functionality: Loading additional data as a user scrolls or clicks a button.
  • Top N Results: Finding the top 5 most expensive products, or the 3 most recent users.
  • Performance Optimization: Preventing large result sets from being retrieved unnecessarily.

SQL Syntax & Examples (MySQL/MariaDB LIMIT)

The LIMIT clause is placed at the very end of your SELECT statement, after ORDER BY if present.

Syntax 1: LIMIT count (Get the first ‘count’ rows)

SQL
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by
LIMIT count;

  • count: The maximum number of rows you want to retrieve.

Example 1: Get the first 3 users

SQL
USE scriptbuzz_db;

SELECT user_id, username FROM users
LIMIT 3;

Expected Output (example – order might vary without ORDER BY):

user_idusername
1john_doe
2admin_user
3jane_doe

What it does: Retrieves the first 3 rows encountered in the users table. Without an ORDER BY clause, the “first” rows are not guaranteed to be consistent or meaningful (e.g., by ID).

Example 2: Get the 5 most expensive products

To get the “most expensive,” we need to sort the data first.

SQL
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

Expected Output (example):

nameprice
Laptop Pro1200.00
Gaming Monitor350.00
Mechanical Keyboard99.99
Gaming Mouse75.00
Webcam 1080p65.00

What it does: Sorts all products by price in descending order, then takes only the first 5 results from that sorted list.

Syntax 2: LIMIT offset, count (For Pagination)

This syntax is crucial for pagination, allowing you to skip a certain number of rows before starting to retrieve.

SQL
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by
LIMIT offset, count;

  • offset: The number of rows to skip from the beginning of the result set. offset is 0-indexed, meaning the first row is at offset 0.
  • count: The maximum number of rows to return after the offset.

Example 3: Pagination – Page 1 (10 products per page)

Let’s assume we want to display 10 products per page, sorted by name.

SQL
SELECT product_id, name, price FROM products
ORDER BY name ASC
LIMIT 0, 10;

What it does: Skips 0 rows and retrieves the next 10 products. This is your “Page 1”.

Example 4: Pagination – Page 2 (10 products per page)

To get the next 10 products, you need to skip the first 10.

SQL
SELECT product_id, name, price FROM products
ORDER BY name ASC
LIMIT 10, 10;

What it does: Skips the first 10 rows and retrieves the next 10 products (rows 11-20). This is your “Page 2”.

General Pagination Formula: If you want N items per page and you are on page_number (starting from 1): LIMIT (page_number - 1) * N, N;

For instance, for page_number = 3 and N = 10: LIMIT (3 - 1) * 10, 10; which simplifies to LIMIT 20, 10;

Syntax 3 (Alternative in MySQL/MariaDB): LIMIT count OFFSET offset

This is another way to write the same thing as LIMIT offset, count and is often considered more readable.

SQL
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by
LIMIT count OFFSET offset;

Example 5: Get 10 products starting from the 11th product (same as Page 2 above)

SQL
SELECT product_id, name, price FROM products
ORDER BY name ASC
LIMIT 10 OFFSET 10;

What it does: Identical functionality to LIMIT 10, 10.

SQL Server TOP Clause (For Awareness)

If you were working with SQL Server, the syntax for limiting results is different:

SQL
SELECT TOP count column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by;

Or, to get a percentage of rows:

SQL
SELECT TOP percentage PERCENT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by;

Example (SQL Server):

SQL
SELECT TOP 5 name, price FROM products
ORDER BY price DESC;

This is equivalent to LIMIT 5 in MySQL/MariaDB. Remember, for this tutorial, we will continue using LIMIT.

Tips

  • Always use ORDER BY with LIMIT for consistent results. Without an ORDER BY clause, the order of rows returned by the database is not guaranteed. If you ask for LIMIT 10 without an ORDER BY, you might get a different set of 10 rows each time you run the query, even if the underlying data hasn’t changed.
  • Performance: LIMIT combined with ORDER BY can still be slow on very large tables if the ORDER BY column is not indexed. We will cover indexes in a later chapter to address this.
  • Calculating Total Rows for Pagination: To properly implement pagination in a web application, you usually need two pieces of information:
    1. The actual data for the current page (obtained with SELECT ... LIMIT offset, count).
    2. The total number of rows without the LIMIT (obtained with SELECT COUNT(*) FROM table_name WHERE condition;). This helps determine the total number of pages.

Notes:

  • The LIMIT clause (MySQL/MariaDB) restricts the number of rows returned by a SELECT statement.
  • LIMIT count: Retrieves the first count rows.
  • LIMIT offset, count: Retrieves count rows starting after offset rows.
  • LIMIT count OFFSET offset: An alternative, often more readable, syntax for the same.
  • LIMIT is fundamental for pagination and displaying “top N” results.
  • Always use ORDER BY with LIMIT to ensure a consistent and predictable order of results.
  • Be aware that other database systems (like SQL Server) use TOP for similar functionality.

Small Exercise

Using your scriptbuzz_db:

  1. Select the 2 most recently created users (hint: use created_at and DESC).
  2. Select the 3 cheapest products that are currently active (is_active = 1).
  3. Imagine you want to display blog posts (from your posts table, if created) at 5 posts per page. Write the query to get the posts for “Page 3”, sorted by created_at (newest first).

You’ve now learned how to precisely control which rows you retrieve and how many. This brings us closer to building dynamic web interfaces that manage data effectively. Next, we’ll learn how to perform calculations on your data!

📘 Continue to the Next Chapter

Aggregate Functions in SQL

Explore SQL aggregate functions like COUNT, SUM, AVG, MIN, and MAX to perform calculations on data sets in your database.

➡️

🚀 Explore Popular Learning Tracks