LIMIT and Pagination - Controlling Result Size

Introduction

What if a query returns 1 million rows, but you only need the first 10? Or you're building a web app that shows 25 results per page?

LIMIT controls how many rows are returned. Combined with OFFSET, it enables pagination—showing results one page at a time.

LIMIT Syntax

SELECT columns
FROM table
WHERE condition
ORDER BY column
LIMIT number;

Basic LIMIT Example

-- Get first 10 products
SELECT name, price
FROM products
LIMIT 10;

Result: Only 10 rows returned, even if there are 1000 products.

LIMIT with ORDER BY

IMPORTANT: Always use ORDER BY with LIMIT to ensure consistent results!

Top 5 Most Expensive Products

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

Result:

       name        | price
-------------------+--------
 Laptop Pro 15     | 999.99
 Monitor 4K 32"    | 799.99
 Gaming Chair      | 549.99
 Desk Standing     | 499.99
 Webcam HD         | 299.99

Latest 10 Orders

SELECT id, customer_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 10;

Cheapest 3 Products per Category

-- PostgreSQL DISTINCT ON
SELECT DISTINCT ON (category)
    category,
    name,
    price
FROM products
ORDER BY category, price ASC
LIMIT 3;  -- Only 3 categories

-- Better: Top 3 per category
WITH ranked AS (
    SELECT
        category,
        name,
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price ASC) AS rn
    FROM products
)
SELECT category, name, price
FROM ranked
WHERE rn <= 3;

LIMIT Without ORDER BY (Dangerous!)

-- ❌ Unpredictable: Order is not guaranteed
SELECT name FROM products LIMIT 10;
-- Might return different rows each time!

-- ✅ Always use ORDER BY
SELECT name FROM products
ORDER BY name
LIMIT 10;

Without ORDER BY, the database can return rows in any order (often based on physical storage order, which can change).

OFFSET - Skipping Rows

OFFSET skips a specified number of rows before returning results.

Syntax

SELECT columns
FROM table
ORDER BY column
LIMIT number
OFFSET number;

Example: Skip First 10 Rows

-- Products ranked 11-20 by price
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 10;

Execution:

1. Sort by price descending
2. Skip first 10 rows
3. Return next 10 rows

Pagination Pattern

Page 1 (Rows 1-10)

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 0;  -- Skip 0 rows

Page 2 (Rows 11-20)

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 10;  -- Skip first 10 rows

Page 3 (Rows 21-30)

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 20;  -- Skip first 20 rows

Formula for Pagination

LIMIT page_size
OFFSET (page_number - 1) * page_size

Example:

• Page size = 25
• Page 1: OFFSET 0 (0 * 25)
• Page 2: OFFSET 25 (1 * 25)
• Page 3: OFFSET 50 (2 * 25)

Pagination with Total Count

-- Get page of results + total count
WITH data AS (
    SELECT name, price
    FROM products
    ORDER BY price DESC
),
total AS (
    SELECT COUNT(*) AS total_count FROM data
)
SELECT
    name,
    price,
    (SELECT total_count FROM total) AS total_products
FROM data
LIMIT 25
OFFSET 0;

Better approach (single query):

SELECT
    name,
    price,
    COUNT(*) OVER () AS total_count
FROM products
ORDER BY price DESC
LIMIT 25
OFFSET 0;

Result:

       name        | price  | total_count
-------------------+--------+-------------
 Laptop Pro 15     | 999.99 |     243
 Monitor 4K 32"    | 799.99 |     243
 ...

Every row includes the total count (useful for showing "Page 1 of 10").

FETCH (Standard SQL)

LIMIT is PostgreSQL/MySQL syntax. Standard SQL uses FETCH.

FETCH Syntax

SELECT columns
FROM table
ORDER BY column
OFFSET number ROWS
FETCH FIRST number ROWS ONLY;

Example

-- Standard SQL (works in PostgreSQL, Oracle, SQL Server)
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;

-- PostgreSQL/MySQL equivalent
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 10;

Recommendation: Use LIMIT/OFFSET for PostgreSQL and MySQL. Use FETCH for portability.

Performance Considerations

OFFSET is Slow for Large Offsets

-- ❌ Very slow: Database must scan + skip 1 million rows
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 1000000;

Why slow?

• Database retrieves all 1,000,010 rows
• Discards first 1,000,000
• Returns last 10

Time complexity: O(n) where n = OFFSET + LIMIT

Keyset Pagination (Faster Alternative)

Instead of OFFSET, use WHERE with the last seen value:

-- Page 1
SELECT id, name, price
FROM products
ORDER BY price DESC, id DESC
LIMIT 10;
-- Last row: id=42, price=599.99

-- Page 2 (using last seen values)
SELECT id, name, price
FROM products
WHERE (price, id) < (599.99, 42)  -- Continue from last row
ORDER BY price DESC, id DESC
LIMIT 10;

Advantages:

• ✅ Constant time O(1) (uses index)
• ✅ Scales to millions of rows
• ✅ No performance degradation on deep pages

Disadvantages:

• ❌ Can't jump to arbitrary page numbers
• ❌ More complex to implement
• ❌ Requires stable sort order

Index for Pagination

-- Create index on ORDER BY columns
CREATE INDEX idx_products_price_id ON products(price DESC, id DESC);

-- Fast pagination with index
SELECT name, price
FROM products
ORDER BY price DESC, id DESC
LIMIT 10
OFFSET 100;

Pagination in Web Applications

Example: Products Listing (Page-Based)

-- Parameters from user: page=2, page_size=25
SELECT
    id,
    name,
    price,
    COUNT(*) OVER () AS total_count
FROM products
WHERE active = TRUE
ORDER BY name
LIMIT 25
OFFSET 25;  -- (page - 1) * page_size

In application code (Python example):

page = int(request.args.get('page', 1))
page_size = 25
offset = (page - 1) * page_size

query = """
    SELECT id, name, price, COUNT(*) OVER () AS total_count
    FROM products
    WHERE active = TRUE
    ORDER BY name
    LIMIT %s OFFSET %s
"""
results = db.execute(query, (page_size, offset))

total_count = results[0]['total_count'] if results else 0
total_pages = (total_count + page_size - 1) // page_size

Example: Infinite Scroll (Keyset-Based)

-- Parameters from user: last_id=42, last_price=599.99
SELECT id, name, price
FROM products
WHERE active = TRUE
  AND (price, id) < (:last_price, :last_id)
ORDER BY price DESC, id DESC
LIMIT 25;

In application code (JavaScript example):

async function loadMore(lastPrice, lastId) {
    const response = await fetch(
        `/api/products?last_price=${lastPrice}&last_id=${lastId}`
    );
    const products = await response.json();
    // Append to page
}

Combining LIMIT with Aggregates

Top 5 Customers by Spending

SELECT
    c.name,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 5;

Top 10 Products by Units Sold

SELECT
    p.name,
    SUM(oi.quantity) AS units_sold
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY units_sold DESC
LIMIT 10;

LIMIT in Subqueries

Latest Order per Customer (Top 100 Customers)

SELECT
    c.name,
    (
        SELECT order_date
        FROM orders
        WHERE customer_id = c.id
        ORDER BY order_date DESC
        LIMIT 1
    ) AS last_order_date
FROM customers c
ORDER BY c.name
LIMIT 100;

Top Product per Category

SELECT DISTINCT ON (category)
    category,
    name,
    price
FROM products
ORDER BY category, price DESC;
-- Returns one product per category (highest price)

LIMIT with CTEs

WITH top_customers AS (
    SELECT
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
    ORDER BY total_spent DESC
    LIMIT 10
)
SELECT
    c.name,
    tc.total_spent
FROM top_customers tc
INNER JOIN customers c ON tc.customer_id = c.id
ORDER BY tc.total_spent DESC;

Common Patterns

Pattern 1: Random Sample

-- Get 10 random products
SELECT name, price
FROM products
ORDER BY RANDOM()  -- PostgreSQL
LIMIT 10;

-- MySQL equivalent
SELECT name, price
FROM products
ORDER BY RAND()
LIMIT 10;

Warning: RANDOM()/RAND() is slow on large tables! Consider alternatives:

-- Faster: Random using ID range
SELECT name, price
FROM products
WHERE id >= (
    SELECT FLOOR(RANDOM() * (SELECT MAX(id) FROM products))
)
LIMIT 10;

Pattern 2: Preview Results

-- Quick preview during development
SELECT *
FROM huge_table
LIMIT 100;

Pattern 3: Top N per Group

-- Top 3 orders per customer
WITH ranked AS (
    SELECT
        customer_id,
        order_date,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
    FROM orders
)
SELECT customer_id, order_date, total
FROM ranked
WHERE rn <= 3;

Pattern 4: Latest Records

-- Last 7 days of orders
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY order_date DESC
LIMIT 100;

Handling Edge Cases

No Results

-- What if table is empty or OFFSET too large?
SELECT name FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 1000000;
-- Returns 0 rows (not an error)

Last Page (Partial Results)

-- Page size = 25, but only 17 products remain
SELECT name FROM products
ORDER BY name
LIMIT 25
OFFSET 225;
-- Returns 17 rows

Database Differences

PostgreSQL

LIMIT 10 OFFSET 20

MySQL

LIMIT 10 OFFSET 20
-- OR
LIMIT 20, 10  -- OFFSET, LIMIT (confusing order!)

SQL Server

OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

Oracle

-- Oracle 12c+
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY

-- Older Oracle (using ROWNUM)
SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT * FROM products ORDER BY price DESC
    ) a
    WHERE ROWNUM <= 30
)
WHERE rnum > 20;

Common Mistakes

Mistake 1: LIMIT Without ORDER BY

-- ❌ Unpredictable results
SELECT name FROM products LIMIT 10;

-- ✅ Always specify order
SELECT name FROM products ORDER BY name LIMIT 10;

Mistake 2: Using LIMIT for Deduplication

-- ❌ Wrong way to remove duplicates
SELECT name FROM products LIMIT 1;

-- ✅ Use DISTINCT
SELECT DISTINCT name FROM products;

Mistake 3: Deep OFFSET Without Index

-- ❌ Slow on large tables
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10
OFFSET 100000;

-- ✅ Use keyset pagination
SELECT * FROM products
WHERE created_at < :last_created_at
ORDER BY created_at DESC
LIMIT 10;

Practice Exercises

Exercise 1

Get the 5 most expensive products.

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

Exercise 2

Get products ranked 11-20 by price (second page, 10 per page).

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 10;

Exercise 3

Get the latest order for each customer (only top 50 customers by total spent).

WITH top_customers AS (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    ORDER BY SUM(total) DESC
    LIMIT 50
),
latest_orders AS (
    SELECT DISTINCT ON (customer_id)
        customer_id,
        order_date,
        total
    FROM orders
    WHERE customer_id IN (SELECT customer_id FROM top_customers)
    ORDER BY customer_id, order_date DESC
)
SELECT
    c.name,
    lo.order_date,
    lo.total
FROM latest_orders lo
INNER JOIN customers c ON lo.customer_id = c.id
ORDER BY lo.total DESC;

Exercise 4

Implement pagination showing products, 25 per page, with total count.

-- Page 2
SELECT
    id,
    name,
    price,
    COUNT(*) OVER () AS total_count
FROM products
ORDER BY name
LIMIT 25
OFFSET 25;  -- (page - 1) * page_size

Key Takeaways

• ✅ LIMIT controls maximum number of rows returned
• ✅ OFFSET skips rows before returning results
• ✅ Always use ORDER BY with LIMIT for predictable results
• ✅ OFFSET is slow for deep pagination (use keyset pagination instead)
• ✅ COUNT(*) OVER() provides total count in paginated queries
• ✅ FETCH is standard SQL alternative to LIMIT
• ✅ Index ORDER BY columns for fast pagination
• ✅ Keyset pagination scales better than OFFSET for large datasets

Next Steps

You've completed the advanced query techniques in Module 6! In the final lesson, we'll explore String Functions and Pattern Matching—manipulating and searching text data with LIKE, ILIKE, and string functions.

Almost done with Module 6!