LIMIT and DISTINCT

Two small clauses that solve big practical problems. LIMIT keeps your result sets manageable — critical when exploring a table with millions of rows. DISTINCT removes duplicates — essential for understanding what unique values exist in your data and for counting unique entities accurately.

Learning Objectives

• Use LIMIT / TOP to cap result set size
• Use DISTINCT to retrieve unique values
• Understand the performance implications of each
• Use OFFSET for pagination — and understand why it breaks at scale
• Distinguish DISTINCT from GROUP BY and know when to use each

---

LIMIT — Control How Many Rows Return

Basic usage

-- Return only the first 10 rows
SELECT order_id, customer_id, order_date, quantity * unit_price AS order_total
FROM orders
LIMIT 10;

MySQL / PostgreSQL / SQLite: use LIMIT n SQL Server / MS Access: use TOP n in the SELECT clause

-- SQL Server / Azure SQL syntax
SELECT TOP 10 order_id, customer_id, order_date
FROM orders;

Why LIMIT matters for analysts

When you first explore a new table, running SELECT * FROM big_table without a LIMIT can return millions of rows and crash your query tool — or worse, send a very large result set to a business stakeholder who asked for a "quick look."

-- Safe way to preview a new table
SELECT *
FROM customer_transactions
LIMIT 100;

Analyst habit: always LIMIT exploratory queries

Add LIMIT 100 or LIMIT 1000 any time you're exploring a table for the first time. Remove it only when you need the full result for an export or aggregation. Make this a reflex.

Top N with ORDER BY — the most common use case

LIMIT without ORDER BY gives you an arbitrary N rows. The meaningful use is always ORDER BY first, then LIMIT:

-- Top 5 highest-value completed orders
SELECT
    order_id,
    customer_id,
    quantity * unit_price   AS order_total
FROM orders
WHERE status = 'completed'
ORDER BY order_total DESC
LIMIT 5;

Sample output:

order_id	customer_id	order_total
O002	C003	389.00
O008	C002	389.00
O001	C001	298.00
O007	C003	249.95
O014	C007	199.00

-- 5 most recent orders
SELECT order_id, order_date, status
FROM orders
ORDER BY order_date DESC
LIMIT 5;

For mid-level analysts

LIMIT runs after ORDER BY in the logical execution order. So ORDER BY order_total DESC LIMIT 5 correctly sorts all rows first, then caps the output to 5. The database is optimised for this — it doesn't need to return all rows to memory; it can stop scanning once it has found the top N.

---

OFFSET — Pagination

OFFSET skips a number of rows before returning results. Combined with LIMIT, it enables pagination.

-- Page 1 — rows 1–10
SELECT order_id, order_date, quantity * unit_price AS order_total
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 0;

-- Page 2 — rows 11–20
LIMIT 10 OFFSET 10;

-- Page 3 — rows 21–30
LIMIT 10 OFFSET 20;

Formula: OFFSET = (page_number - 1) * page_size

-- General: page_number = 4, page_size = 25
LIMIT 25 OFFSET 75;

OFFSET pagination degrades on large tables

LIMIT 10 OFFSET 10000 forces the database to scan and discard 10,000 rows before returning 10. At page 5,000 with 10 rows per page, you're discarding 49,990 rows every query. This is O(n) — it gets slower linearly as the page number increases.

For senior analysts

Use keyset pagination (cursor pagination) instead. Store the last seen value and filter from there:

-- Instead of: LIMIT 10 OFFSET 5000
-- Use: remember the last order_date and order_id from the previous page
WHERE order_date < '2024-01-15'
   OR (order_date = '2024-01-15' AND order_id < 'O048')
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

This is O(log n) with the right index — scales to billions of rows without degrading.

---

DISTINCT — Remove Duplicate Rows

DISTINCT eliminates duplicate rows from the result set.

Simple case — unique values in one column

-- What order statuses exist in the database?
SELECT DISTINCT status
FROM orders
ORDER BY status;

Sample output:

status
cancelled
completed
pending
refunded

-- What countries are our customers from?
SELECT DISTINCT country
FROM customers
ORDER BY country;

Sample output:

country
China
India
Ireland
United Kingdom
United States

DISTINCT across multiple columns

DISTINCT applies to the entire row — all selected columns together must form a unique combination.

-- Unique customer-city combinations
SELECT DISTINCT customer_id, city
FROM customers
ORDER BY city;

This returns one row per unique (customer_id, city) pair — not just unique customer_id values.

DISTINCT on multiple columns — easy to misread

SELECT DISTINCT customer_id, order_date FROM orders removes rows where both customer_id AND order_date are identical. A customer who placed two orders on the same day still appears twice — two rows with the same customer_id but different order_id (not selected) values don't collide. If you want unique customers only: SELECT DISTINCT customer_id.

---

Counting Unique Values — COUNT DISTINCT

This is one of the most-used patterns in analytics:

-- How many unique customers have placed at least one completed order?
SELECT COUNT(DISTINCT customer_id) AS unique_buyers
FROM orders
WHERE status = 'completed';

Sample output:

unique_buyers
28

-- How many unique products were sold in January 2024?
SELECT COUNT(DISTINCT product_id) AS unique_products_sold
FROM orders
WHERE status = 'completed'
  AND order_date BETWEEN '2024-01-01' AND '2024-01-31';

Business scenario

Your stakeholder asks: "How many unique customers visited our checkout page this month?"

The raw page_views table has one row per page view — a customer who visits the checkout 8 times appears 8 times. COUNT(*) gives you page views; COUNT(DISTINCT user_id) gives you unique customers. These are very different numbers, and using the wrong one in a report is a credibility-destroying mistake.

---

DISTINCT vs GROUP BY

Both can find unique values, but they serve different purposes:

-- DISTINCT — just the unique values, nothing else
SELECT DISTINCT customer_id
FROM orders;

-- GROUP BY — unique values PLUS the ability to aggregate
SELECT
    customer_id,
    COUNT(*)                    AS total_orders,
    SUM(quantity * unit_price)  AS total_spend
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY total_spend DESC;

Use DISTINCT when you want unique values with no further calculation. Use GROUP BY when you need to count, sum, or average per group.

For mid-level analysts

On a single column, SELECT DISTINCT customer_id FROM orders and SELECT customer_id FROM orders GROUP BY customer_id return the same rows. GROUP BY is more flexible — you can add aggregates. DISTINCT is simpler to read for the pure "what unique values exist" question. Don't combine them unnecessarily: SELECT DISTINCT customer_id FROM orders GROUP BY customer_id is redundant.

---

Performance Notes

DISTINCT on large tables can be slow

DISTINCT forces the database to compare every row in the result set and eliminate duplicates — this requires a sort or hash operation. On millions of rows, this is expensive. Always filter with WHERE before applying DISTINCT to reduce the row count.

For senior analysts

COUNT(DISTINCT column) is computationally expensive on very large datasets because it cannot be trivially parallelised. On analytical databases (BigQuery, Redshift, Snowflake), consider approximate alternatives:

• BigQuery: APPROX_COUNT_DISTINCT(column) — ~1% error, much faster
• PostgreSQL: hyperloglog extension — probabilistic count, configurable precision

For a 2% acceptable error margin, approximate counting is often the right engineering trade-off on billions of rows.

---

Practice Exercises

Warm-up 1. Retrieve the first 20 rows from the products table. 2. Get a list of all unique category values in the products table, sorted alphabetically. 3. Get a list of all unique country values in the customers table, sorted alphabetically.

Main 4. Find the 3 most expensive products. Show product_id, product_name, and unit_price. 5. Count how many unique customers have placed at least one completed order. 6. How many unique product categories have been sold (status = 'completed')? You'll need a JOIN to products. 7. Write a paginated query: return orders sorted by order_date DESC, showing rows 21 through 30 (page 3 of a 10-per-page result).

Stretch 8. A page_views table has columns user_id, page_url, viewed_at. Write a query that counts unique users who viewed the /checkout page in January 2024. 9. You're building a paginated API on a 50M-row orders table. Write page 1 using LIMIT/OFFSET, then rewrite it using keyset pagination (assume the last order_date from page 1 was '2024-03-15' and the last order_id was 'O4821'). Explain why the second version is faster.

---

Interview Questions

[Beginner] What does LIMIT 10 do in a SQL query?

Show answer

LIMIT 10 restricts the result set to a maximum of 10 rows. The database stops returning rows after the first 10 that satisfy the other conditions.

Without ORDER BY, the 10 rows returned are arbitrary. With ORDER BY, LIMIT returns the top N rows by the specified sort:

-- 10 arbitrary rows from orders
SELECT order_id FROM orders LIMIT 10;

-- The 10 most recent orders
SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 10;

In SQL Server, the equivalent is SELECT TOP 10 .... In Oracle (pre-12c), it's WHERE ROWNUM <= 10.

---

[Beginner] What is the difference between SELECT status FROM orders and SELECT DISTINCT status FROM orders?

Show answer

• SELECT status FROM orders returns one row for every row in the orders table. If there are 10,000 orders and all have status values, you get 10,000 rows, with many duplicates.
• SELECT DISTINCT status FROM orders returns one row per unique value of status. If orders can be 'completed', 'pending', 'cancelled', or 'refunded', you get exactly 4 rows.

SELECT status FROM orders;           -- 10,000 rows (one per order)
SELECT DISTINCT status FROM orders;  -- 4 rows (one per unique status)

Use DISTINCT when you want to know what values exist, not how many times each appears.

---

[Mid-level] What does SELECT DISTINCT customer_id, city FROM customers return? Is it the same as SELECT DISTINCT customer_id FROM customers?

Show answer

SELECT DISTINCT customer_id, city FROM customers returns one row per unique combination of (customer_id, city). If a customer has moved and has two rows with different cities, both appear.

SELECT DISTINCT customer_id FROM customers returns one row per unique customer_id — ignoring the city entirely.

These are the same only when customer_id is already unique (a primary key). In that case, adding city to the column list doesn't create more rows because the combination (unique_id, any_value) is still unique per row.

The general rule: DISTINCT looks at all selected columns together. More columns = potentially more rows.

---

[Mid-level] How would you return the top 10 customers by total revenue? Write the query.

Show answer

SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name)  AS full_name,
    COUNT(o.order_id)                        AS total_orders,
    SUM(o.quantity * o.unit_price)           AS total_revenue
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_revenue DESC
LIMIT 10;

Key points: - WHERE status = 'completed' filters before aggregating — only count completed orders - GROUP BY customer to get one row per customer - ORDER BY total_revenue DESC puts the highest spenders first - LIMIT 10 caps at 10 rows after sorting

---

[Senior] You're building a paginated API endpoint that uses LIMIT/OFFSET on a table with 100 million rows. Page 5000 is taking 10 seconds. What's the problem and how do you fix it?

Show answer

The problem: LIMIT 10 OFFSET 49990 requires the database to scan and discard 49,990 rows before returning 10. This is a sequential scan of almost 50,000 rows per request — performance degrades linearly with page number. At page 5,000 it's 500x slower than page 1.

The fix: keyset pagination (cursor pagination)

Instead of tracking a page number, track the last value you saw:

-- Page 1 (initial request)
SELECT order_id, order_date, customer_id, order_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date DESC, order_id DESC
LIMIT 10;
-- Last row: order_date = '2024-03-15', order_id = 'O4821'

-- Page 2: pass those values as cursor
SELECT order_id, order_date, customer_id, order_total
FROM orders
WHERE status = 'completed'
  AND (order_date < '2024-03-15'
       OR (order_date = '2024-03-15' AND order_id < 'O4821'))
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

With an index on (status, order_date DESC, order_id DESC), this is O(log n) — equally fast on page 1 or page 500,000.

Trade-offs of keyset pagination: - Cannot jump to an arbitrary page number (you must traverse sequentially) - More complex API design — clients must store the cursor, not a page number - Handles inserts/deletes correctly — OFFSET pagination can skip or repeat rows when data changes between requests

---

Previous: 03-order-by | Next: 05-basic-query-practice