ORDER BY, LIMIT, and DISTINCT

What Is It?

What Are ORDER BY, LIMIT, and DISTINCT?

In the previous chapter you learned to retrieve rows with SELECT and filter them with WHERE. But the rows came back in an unpredictable order, and you had no way to say "give me only the top 5" or "remove duplicates." This chapter fixes all three problems.

ORDER BY — Sorting

ORDER BY sorts your result rows. You can sort ascending (ASC, the default) or descending (DESC), and by multiple columns (e.g., department first, then salary within each department).

LIMIT and OFFSET — Restricting Rows

LIMIT n returns only the first n rows. Combined with ORDER BY, this gives you queries like "top 5 highest-paid employees." OFFSET skips rows — essential for pagination (page 1 shows rows 1-10, page 2 shows rows 11-20, etc.).

DISTINCT — Removing Duplicates

SELECT DISTINCT removes duplicate rows from the result. Useful when you want unique values — e.g., "which departments exist?" or "which cities have orders?". Combined with COUNT(), it tells you how many unique values exist.

Together, these three features turn raw data into the kind of sorted, paginated, deduplicated results you actually use in dashboards, reports, and paginated lists.

Why Does It Matter?

Why Do These Matter?

1. Every Dashboard Uses ORDER BY

Look at any dashboard — top products by revenue, most recent orders, highest-rated restaurants. Every single one uses ORDER BY to present data in a meaningful order. Without sorting, data is just a random pile.

2. LIMIT Makes Queries Fast

If you want the top 5 employees, you do NOT want the database to send you 10 million rows and then throw away 9,999,995 of them. LIMIT 5 lets the database stop early. Combined with an index, this can turn a 10-second query into a 1-millisecond query.

3. Pagination Is Everywhere

Instagram feed, Amazon product listings, Gmail inbox — all paginated. Every pagination system on the internet uses SQL's LIMIT and OFFSET (or similar patterns). If you cannot write a pagination query, you cannot build a real web app.

4. DISTINCT Answers "What Categories Exist?"

Product managers constantly ask: "How many unique customers bought from us this month?" "Which countries have active users?" These are all COUNT(DISTINCT col) or SELECT DISTINCT col queries.

5. Interview Staples

"Find the 2nd highest salary" is possibly the most-asked SQL interview question in the world. It uses ORDER BY, LIMIT, and sometimes OFFSET. You will be asked this in literally every SQL interview.

Detailed Explanation

Detailed Explanation

Reference Table: products

For this chapter we use a products table. Create it once:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    category VARCHAR(30),
    price DECIMAL(10,2),
    stock INT,
    city VARCHAR(30)
);

INSERT INTO products VALUES
(1, 'iPhone 15', 'Electronics', 79999.00, 25, 'Mumbai'),
(2, 'Samsung TV', 'Electronics', 45999.00, 15, 'Delhi'),
(3, 'Running Shoes', 'Sports', 4999.00, 120, 'Bengaluru'),
(4, 'Cricket Bat', 'Sports', 2499.00, 80, 'Mumbai'),
(5, 'Milk 1L', 'Grocery', 68.00, 500, 'Delhi'),
(6, 'Bread', 'Grocery', 45.00, 300, 'Bengaluru'),
(7, 'Headphones', 'Electronics', 3499.00, 60, 'Mumbai'),
(8, 'Yoga Mat', 'Sports', 1299.00, 45, 'Pune'),
(9, 'Rice 5kg', 'Grocery', 420.00, 200, 'Delhi'),
(10, 'Laptop', 'Electronics', 65000.00, 10, 'Bengaluru'),
(11, 'Football', 'Sports', 899.00, 150, 'Mumbai'),
(12, 'Soap', 'Grocery', 60.00, 800, 'Pune');

1. ORDER BY Basics

Syntax: ORDER BY column [ASC|DESC]. ASC (ascending, low to high) is the default — you can omit it.

-- Sort products by price ascending
SELECT name, price FROM products ORDER BY price;

-- Sort descending (highest price first)
SELECT name, price FROM products ORDER BY price DESC;

2. Multi-Column Sort

Provide multiple columns separated by commas. SQL sorts by the first column, then breaks ties using the second, then the third, etc.

-- Sort by category ascending, then by price descending within category
SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

Result: Electronics group comes first (alphabetically), with highest-priced electronics at top. Then Grocery group. Then Sports group.

3. ORDER BY with Aliases and Expressions

ORDER BY is one of the few clauses where column aliases from SELECT ARE allowed (unlike WHERE). You can also sort by computed expressions:

-- Sort by total inventory value (price * stock)
SELECT name, price, stock, price * stock AS inventory_value
FROM products
ORDER BY inventory_value DESC;

-- Or use the expression directly
SELECT name, price, stock FROM products
ORDER BY price * stock DESC;

You can even sort by column position (1-indexed): ORDER BY 2 DESC sorts by the second column in SELECT. This is discouraged — it breaks when you reorder columns.

4. NULL Handling in ORDER BY

In MySQL, NULL is treated as the smallest value. So ORDER BY col ASC puts NULLs first, and ORDER BY col DESC puts NULLs last. PostgreSQL does the opposite by default. For portability, use ORDER BY col IS NULL, col to force NULLs to the end.

5. LIMIT — Getting the First N Rows

LIMIT n returns the first n rows of the result. Usually paired with ORDER BY:

-- Top 3 most expensive products
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;

Without ORDER BY, the "first 3" is whatever rows the database returns first — unpredictable. Always pair LIMIT with ORDER BY for deterministic results.

6. LIMIT with OFFSET — Pagination

MySQL supports two equivalent syntaxes:

-- Syntax 1: LIMIT offset, count
SELECT * FROM products ORDER BY id LIMIT 10, 5;
-- Skip 10, take 5 (rows 11-15)

-- Syntax 2: LIMIT count OFFSET offset
SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 10;
-- Same thing: skip 10, take 5

The second form is clearer — use it. MySQL-only code often uses the first form; PostgreSQL only supports the second.

7. Pagination Pattern

To show N items per page, on page P:

-- Page 1: LIMIT 10 OFFSET 0
-- Page 2: LIMIT 10 OFFSET 10
-- Page 3: LIMIT 10 OFFSET 20
-- Page P: LIMIT 10 OFFSET (P-1)*10

Formula: OFFSET = (page_number - 1) * page_size.

Performance Warning: Deep OFFSET Is Slow

At page 10000 with page size 10, MySQL has to scan 100,000 rows and throw away the first 99,990. This gets slower and slower. For deep pagination, use keyset pagination: WHERE id > last_seen_id ORDER BY id LIMIT 10. This is O(log n) with an index instead of O(n).

8. DISTINCT — Unique Values

DISTINCT removes duplicate rows from the result:

-- What categories do we sell?
SELECT DISTINCT category FROM products;
-- Returns: Electronics, Grocery, Sports (3 rows, not 12)

-- What cities?
SELECT DISTINCT city FROM products;
-- Returns: Mumbai, Delhi, Bengaluru, Pune

DISTINCT applies to the whole row, not a single column. SELECT DISTINCT category, city FROM products returns unique combinations of category and city.

SELECT DISTINCT category, city FROM products ORDER BY category, city;
-- Returns all unique (category, city) pairs
-- Electronics-Bengaluru, Electronics-Delhi, Electronics-Mumbai,
-- Grocery-Bengaluru, Grocery-Delhi, Grocery-Pune,
-- Sports-Bengaluru, Sports-Mumbai, Sports-Pune

9. COUNT(DISTINCT col) — How Many Unique Values

To count distinct values, wrap the column in COUNT:

-- How many unique categories?
SELECT COUNT(DISTINCT category) FROM products;
-- Returns: 3

-- How many unique cities?
SELECT COUNT(DISTINCT city) FROM products;
-- Returns: 4

Note: COUNT(*) counts all rows including duplicates. COUNT(column) counts non-NULL values. COUNT(DISTINCT column) counts unique non-NULL values. NULL is always excluded from DISTINCT counts.

10. Order of Clauses

When combining everything, the order matters:

SELECT [DISTINCT] columns
FROM table
WHERE conditions
ORDER BY columns
LIMIT n OFFSET m;

This is the syntactic order. The logical execution order is different (FROM → WHERE → SELECT → ORDER BY → LIMIT), which matters when you use aliases — more on this in the GROUP BY chapter.

Code Examples

-- Ascending (default): cheapest first
SELECT name, price FROM products
ORDER BY price
LIMIT 5;

-- Descending: most expensive first
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

-- Sort by category ascending, then price descending within category
SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

-- Sort by inventory value (price * stock) descending
SELECT name, price, stock, price * stock AS inventory_value
FROM products
ORDER BY inventory_value DESC
LIMIT 5;

-- Top 3 most expensive products
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;

-- Cheapest 2 products
SELECT name, price FROM products
ORDER BY price ASC
LIMIT 2;

-- 5 items per page, ordered by id

-- Page 1: rows 1-5
SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 0;

-- Page 2: rows 6-10
SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 5;

-- Page 3: rows 11-12 (only 2 left)
SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 10;

-- Unique categories
SELECT DISTINCT category FROM products;

-- Unique cities, sorted
SELECT DISTINCT city FROM products ORDER BY city;

-- Unique (category, city) combinations
SELECT DISTINCT category, city FROM products
ORDER BY category, city;

-- How many unique categories?
SELECT COUNT(DISTINCT category) AS num_categories FROM products;

-- How many unique cities?
SELECT COUNT(DISTINCT city) AS num_cities FROM products;

-- Compare: COUNT(*), COUNT(category), COUNT(DISTINCT category)
SELECT
    COUNT(*) AS all_rows,
    COUNT(category) AS non_null_cats,
    COUNT(DISTINCT category) AS unique_cats
FROM products;

-- Top 3 most expensive Electronics products, with city info
SELECT name, price, city
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC
LIMIT 3;

-- Using employees table from chapter 6
-- Get the 2nd highest salary using LIMIT with OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- General: Nth highest = LIMIT 1 OFFSET N-1

Common Mistakes

-- Intent: get the top 5 most expensive products
SELECT * FROM products LIMIT 5;

SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

-- Trying to get 10 rows starting at row 20
SELECT * FROM products LIMIT 10, 20;

-- To get 10 rows starting at offset 20:
SELECT * FROM products LIMIT 20, 10;
-- Or clearer:
SELECT * FROM products LIMIT 10 OFFSET 20;

-- Intent: unique names, but also show price
SELECT DISTINCT name, price FROM products;

-- If you just want unique names:
SELECT DISTINCT name FROM products;

-- If you want one price per name (e.g., highest), use GROUP BY:
SELECT name, MAX(price) FROM products GROUP BY name;

-- Page 10,000 of a large table:
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 99990;

-- Keyset pagination (remember the last seen id):
SELECT * FROM products
WHERE id > 99990  -- last seen id from previous page
ORDER BY id
LIMIT 10;

SELECT * FROM products WHERE ORDER BY price DESC;
SELECT * FROM products WHERE DISTINCT category = 'Sports';

-- Correct syntax:
SELECT * FROM products
WHERE category = 'Sports'
ORDER BY price DESC;

SELECT DISTINCT category FROM products
WHERE category = 'Sports';

Summary