Sorting & Limiting Results - MySQL Foundations

ORDER BY — Sorting Results

Without ORDER BY, MySQL returns rows in no guaranteed order — it might be insertion order, primary key order, or something else entirely. Never rely on default order. If you want a specific order, always use ORDER BY.

Ascending Order (ASC) — The Default


-- Sort books by price, cheapest first
SELECT title, price FROM books ORDER BY price;

-- Same thing — ASC is the default
SELECT title, price FROM books ORDER BY price ASC;

Output:


+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| Pride and Prejudice     |  7.99 |
| The Great Gatsby        |  8.99 |
| The Catcher in the Rye  |  8.99 |
| Brave New World         |  9.49 |
| 1984                    |  9.99 |
| Fahrenheit 451          | 10.49 |
| The Hobbit              | 10.99 |
| To Kill a Mockingbird   | 11.99 |
| Dune                    | 14.99 |
| Lord of the Rings       | 15.99 |
+-------------------------+-------+

Descending Order (DESC)


-- Sort books by price, most expensive first
SELECT title, price FROM books ORDER BY price DESC;

Output:


+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| Lord of the Rings       | 15.99 |
| Dune                    | 14.99 |
| To Kill a Mockingbird   | 11.99 |
| The Hobbit              | 10.99 |
| Fahrenheit 451          | 10.49 |
| 1984                    |  9.99 |
| Brave New World         |  9.49 |
| The Great Gatsby        |  8.99 |
| The Catcher in the Rye  |  8.99 |
| Pride and Prejudice     |  7.99 |
+-------------------------+-------+

Sorting Text

Text sorts alphabetically (A→Z for ASC, Z→A for DESC):


-- Alphabetical by title
SELECT title FROM books ORDER BY title ASC;

-- Alphabetical by author
SELECT DISTINCT author FROM books ORDER BY author;

-- Reverse alphabetical by last name
SELECT first_name, last_name FROM customers ORDER BY last_name DESC;

Sorting Dates

Dates sort chronologically — earliest first with ASC, latest first with DESC:


-- Oldest books first
SELECT title, published FROM books ORDER BY published ASC;

-- Newest books first
SELECT title, published FROM books ORDER BY published DESC;

Output (newest first):


+-------------------------+------------+
| title                   | published  |
+-------------------------+------------+
| Dune                    | 1965-08-01 |
| To Kill a Mockingbird   | 1960-07-11 |
| Lord of the Rings       | 1954-07-29 |
| Fahrenheit 451          | 1953-10-19 |
| The Catcher in the Rye  | 1951-07-16 |
| 1984                    | 1949-06-08 |
| The Hobbit              | 1937-09-21 |
| Brave New World         | 1932-01-01 |
| The Great Gatsby        | 1925-04-10 |
| Pride and Prejudice     | 1813-01-28 |
+-------------------------+------------+

Sorting with WHERE

ORDER BY works with WHERE — filter first, then sort the results:


-- In-stock books, cheapest first
SELECT title, price FROM books
WHERE in_stock = TRUE
ORDER BY price ASC;

-- Books over $10, newest first
SELECT title, price, published FROM books
WHERE price > 10
ORDER BY published DESC;

Sorting by Multiple Columns

When two rows have the same value in the first sort column, the second column breaks the tie:


-- Sort by price, then by title (for books at the same price)
SELECT title, price FROM books
ORDER BY price ASC, title ASC;

Output (notice the $8.99 books are now alphabetized):


+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| Pride and Prejudice     |  7.99 |
| The Catcher in the Rye  |  8.99 |  ← alphabetical
| The Great Gatsby        |  8.99 |  ← tie-breaker
| Brave New World         |  9.49 |
| 1984                    |  9.99 |
| Fahrenheit 451          | 10.49 |
| The Hobbit              | 10.99 |
| To Kill a Mockingbird   | 11.99 |
| Dune                    | 14.99 |
| Lord of the Rings       | 15.99 |
+-------------------------+-------+

Each column can have its own direction:


-- Sort by in_stock (in-stock first), then by price (cheapest first)
SELECT title, in_stock, price FROM books
ORDER BY in_stock DESC, price ASC;

💡 Why In-Stock DESC?

Remember that MySQL stores TRUE as 1 and FALSE as 0. DESC puts 1 (TRUE/in-stock) before 0 (FALSE/out-of-stock) — so in-stock books appear first.


-- Customers sorted by city, then last name within each city
SELECT first_name, last_name, city FROM customers
WHERE city IS NOT NULL
ORDER BY city ASC, last_name ASC;

Output:


+------------+-----------+-------------+
| first_name | last_name | city        |
+------------+-----------+-------------+
| Bob        | Smith     | Chicago     |
| Grace      | Wilson    | Chicago     |
| Dave       | Brown     | Houston     |
| Carol      | Williams  | Los Angeles |
| Alice      | Johnson   | New York    |
| Frank      | Miller    | New York    |
+------------+-----------+-------------+

Sorting by Expressions

You can sort by calculated values, not just column names:


-- Sort by price per page (value for money!)
SELECT
    title,
    price,
    pages,
    ROUND(price / pages, 4) AS price_per_page
FROM books
WHERE pages > 0
ORDER BY price_per_page ASC;


-- Sort by column position (not recommended, but possible)
SELECT title, author, price FROM books
ORDER BY 3 DESC;  -- sorts by the 3rd column (price)

⚠️ Avoid Sorting by Column Number

Sorting by position (ORDER BY 3) works but is fragile — if someone adds or reorders columns in the SELECT list, the sort breaks silently. Always use column names.

How NULL Values Sort

MySQL treats NULL as the lowest possible value:

ORDER BY col ASC → NULLs appear first
ORDER BY col DESC → NULLs appear last


-- NULLs first (ascending)
SELECT first_name, city FROM customers ORDER BY city ASC;

Output:


+------------+-------------+
| first_name | city        |
+------------+-------------+
| Eve        | NULL        |  ← NULLs first in ASC
| Hank       | NULL        |
| Bob        | Chicago     |
| Grace      | Chicago     |
| Dave       | Houston     |
| Carol      | Los Angeles |
| Alice      | New York    |
| Frank      | New York    |
+------------+-------------+

Controlling NULL Position

If you want NULLs at the end of an ascending sort, use a trick with IS NULL:


-- Ascending, but push NULLs to the bottom
SELECT first_name, city FROM customers
ORDER BY city IS NULL ASC, city ASC;

Output:


+------------+-------------+
| first_name | city        |
+------------+-------------+
| Bob        | Chicago     |
| Grace      | Chicago     |
| Dave       | Houston     |
| Carol      | Los Angeles |
| Alice      | New York    |
| Frank      | New York    |
| Eve        | NULL        |  ← NULLs at the end now
| Hank       | NULL        |
+------------+-------------+

💡 How This Works

city IS NULL evaluates to 1 (TRUE) for NULL cities and 0 (FALSE) for non-NULL cities. Sorting this ASC puts 0 (has a city) before 1 (NULL city). Then the second sort column city ASC sorts the non-NULL cities alphabetically.

LIMIT — Controlling Row Count

LIMIT restricts how many rows are returned. It's essential for performance (don't fetch a million rows when you need 10) and for building application features like "top N" lists.

Basic LIMIT


-- The 3 cheapest books
SELECT title, price FROM books
ORDER BY price ASC
LIMIT 3;

Output:


+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| Pride and Prejudice     |  7.99 |
| The Great Gatsby        |  8.99 |
| The Catcher in the Rye  |  8.99 |
+-------------------------+-------+


-- The 5 most expensive books
SELECT title, price FROM books
ORDER BY price DESC
LIMIT 5;

-- The 3 longest books
SELECT title, pages FROM books
ORDER BY pages DESC
LIMIT 3;

-- The newest book
SELECT title, published FROM books
ORDER BY published DESC
LIMIT 1;

✅ Common Patterns with LIMIT

LIMIT 1 is especially useful for finding the single "most" or "least" of something:


-- Most expensive book
SELECT title, price FROM books ORDER BY price DESC LIMIT 1;

-- Oldest book
SELECT title, published FROM books ORDER BY published ASC LIMIT 1;

-- Most recently created customer
SELECT * FROM customers ORDER BY created_at DESC LIMIT 1;

LIMIT with WHERE


-- Top 3 cheapest in-stock books
SELECT title, price FROM books
WHERE in_stock = TRUE
ORDER BY price ASC
LIMIT 3;

-- The 2 most expensive books under $15
SELECT title, price FROM books
WHERE price < 15
ORDER BY price DESC
LIMIT 2;

⚠️ LIMIT Without ORDER BY

LIMIT without ORDER BY returns an arbitrary subset of rows — MySQL doesn't guarantee which ones. This is rarely useful. Always pair LIMIT with ORDER BY unless you genuinely don't care which rows you get.

OFFSET — Skipping Rows

OFFSET tells MySQL to skip a number of rows before starting to return results. Combined with LIMIT, it enables pagination.


-- Skip the first 3 rows, then return the next 3
SELECT title, price FROM books
ORDER BY price ASC
LIMIT 3 OFFSET 3;

Output (rows 4–6 by price):


+-----------------+-------+
| title           | price |
+-----------------+-------+
| Brave New World |  9.49 |
| 1984            |  9.99 |
| Fahrenheit 451  | 10.49 |
+-----------------+-------+

Alternative Syntax

MySQL also supports a comma syntax: LIMIT offset, count:


-- Same as LIMIT 3 OFFSET 3:
SELECT title, price FROM books
ORDER BY price ASC
LIMIT 3, 3;
-- The first number is the offset, the second is the count

💡 Which Syntax to Use?

LIMIT 3 OFFSET 3 is clearer and more readable than LIMIT 3, 3. The comma syntax is older and easier to misread (which number is offset and which is count?). Prefer the LIMIT ... OFFSET ... form.

graph LR subgraph All_Rows["All 10 books sorted by price"] R1["Row 1"] --> R2["Row 2"] --> R3["Row 3"] --> R4["Row 4"] --> R5["Row 5"] --> R6["Row 6"] --> R7["Row 7"] --> R8["Row 8"] --> R9["Row 9"] --> R10["Row 10"] end subgraph OFFSET_3["OFFSET 3 (skip these)"] R1 R2 R3 end subgraph LIMIT_3["LIMIT 3 (return these)"] R4 R5 R6 end

Pagination Patterns

Pagination is how applications display large datasets across multiple pages — like search results, product listings, or email inboxes. The formula is:


-- Page N with P items per page:
SELECT columns FROM table
ORDER BY sort_column
LIMIT P OFFSET (N - 1) * P;

Example: 3 Books Per Page


-- Page 1 (first 3 books)
SELECT title, price FROM books ORDER BY title LIMIT 3 OFFSET 0;

-- Page 2 (next 3 books)
SELECT title, price FROM books ORDER BY title LIMIT 3 OFFSET 3;

-- Page 3 (next 3 books)
SELECT title, price FROM books ORDER BY title LIMIT 3 OFFSET 6;

-- Page 4 (remaining book)
SELECT title, price FROM books ORDER BY title LIMIT 3 OFFSET 9;

Page 1 Output:


+-----------------------+-------+
| title                 | price |
+-----------------------+-------+
| 1984                  |  9.99 |
| Brave New World       |  9.49 |
| Dune                  | 14.99 |
+-----------------------+-------+

Page 2 Output:


+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| Fahrenheit 451          | 10.49 |
| Lord of the Rings       | 15.99 |
| Pride and Prejudice     |  7.99 |
+-------------------------+-------+

Getting the Total Count

For pagination UI (showing "Page 2 of 4" or "Showing 4–6 of 10"), you also need the total row count:


-- Total number of books
SELECT COUNT(*) AS total_books FROM books;

-- Total in-stock books (for filtered pagination)
SELECT COUNT(*) AS total_in_stock FROM books WHERE in_stock = TRUE;

⚠️ OFFSET Performance on Large Tables

OFFSET doesn't skip rows efficiently — MySQL still reads and discards them. OFFSET 100000 means MySQL reads 100,000 rows just to throw them away. For large datasets (millions of rows), consider these alternatives:

Keyset pagination — WHERE id > last_seen_id LIMIT 10 (much faster)
Covering indexes — ensure the sort column is indexed
Caching — cache page results in the application layer

For our course and most small-to-medium applications, OFFSET works perfectly fine.

Keyset Pagination Preview

For comparison, here's how keyset pagination works — instead of skipping by count, you filter by the last value seen:


-- Page 1: get the first 3 books by ID
SELECT id, title, price FROM books
ORDER BY id ASC
LIMIT 3;
-- Returns IDs 1, 2, 3

-- Page 2: get 3 books AFTER id 3
SELECT id, title, price FROM books
WHERE id > 3
ORDER BY id ASC
LIMIT 3;
-- Returns IDs 4, 5, 6

-- Page 3: get 3 books AFTER id 6
SELECT id, title, price FROM books
WHERE id > 6
ORDER BY id ASC
LIMIT 3;

This avoids the OFFSET penalty entirely and is how most high-traffic APIs implement pagination.

SQL Clause Order

SQL clauses must appear in a specific order. Getting this wrong causes syntax errors. Here's the order for everything we've learned so far:

graph TD A["SELECT columns"] --> B["FROM table"] B --> C["WHERE conditions"] C --> D["ORDER BY columns"] D --> E["LIMIT count"] E --> F["OFFSET skip"]


-- The full pattern:
SELECT title, price
FROM books
WHERE in_stock = TRUE AND price < 15
ORDER BY price ASC
LIMIT 5
OFFSET 0;

✅ Memory Aid

Think of it as a pipeline: Select what you want → From where → Which rows → Order them → Limit how many. Or just remember: S-F-W-O-L.

⚠️ Common Mistake

Putting ORDER BY before WHERE is a syntax error:


-- ⛔ WRONG — ORDER BY can't come before WHERE
SELECT title FROM books ORDER BY price WHERE price > 10;

-- ✅ CORRECT
SELECT title FROM books WHERE price > 10 ORDER BY price;

Exercises

🏋️ Exercise 1: Basic Sorting

Write queries to:

List all books alphabetically by title
List all books from most expensive to cheapest
List all customers alphabetically by last name, then first name
List all books from newest to oldest publication date

✅ Solution


-- 1
SELECT title FROM books ORDER BY title ASC;

-- 2
SELECT title, price FROM books ORDER BY price DESC;

-- 3
SELECT last_name, first_name FROM customers
ORDER BY last_name ASC, first_name ASC;

-- 4
SELECT title, published FROM books ORDER BY published DESC;

🏋️ Exercise 2: LIMIT & Top N

Write queries to find:

The 3 most expensive books
The single oldest book in the database
The 5 shortest books (fewest pages)
The 3 cheapest in-stock books

✅ Solution


-- 1
SELECT title, price FROM books
ORDER BY price DESC LIMIT 3;

-- 2
SELECT title, published FROM books
ORDER BY published ASC LIMIT 1;

-- 3
SELECT title, pages FROM books
ORDER BY pages ASC LIMIT 5;

-- 4
SELECT title, price FROM books
WHERE in_stock = TRUE
ORDER BY price ASC LIMIT 3;

🏋️ Exercise 3: Pagination

Imagine a bookstore website showing 4 books per page, sorted by title alphabetically. Write queries for:

Page 1 (first 4 books)
Page 2 (next 4 books)
Page 3 (remaining books)
The total number of books (for "Page X of Y")

✅ Solution


-- Page 1
SELECT title, price FROM books
ORDER BY title LIMIT 4 OFFSET 0;

-- Page 2
SELECT title, price FROM books
ORDER BY title LIMIT 4 OFFSET 4;

-- Page 3
SELECT title, price FROM books
ORDER BY title LIMIT 4 OFFSET 8;

-- Total count
SELECT COUNT(*) AS total_books FROM books;
-- Returns 10 → 10 / 4 = 3 pages (with 2 books on the last page)

🏋️ Exercise 4: Combined Challenge

Write a single query that:

Shows the title, author, price, and pages
Only includes in-stock books
Only includes books with more than 200 pages
Sorts by pages (most pages first)
Returns only the top 5 results

✅ Solution


SELECT title, author, price, pages
FROM books
WHERE in_stock = TRUE
  AND pages > 200
ORDER BY pages DESC
LIMIT 5;

🎯 Quick Quiz

Question 1: What is the default sort direction for ORDER BY?

Question 2: Where do NULL values appear in an ascending sort?

Question 3: What does LIMIT 5 OFFSET 10 do?

Question 4: What is the correct order of SQL clauses?

Summary

🎉 Key Takeaways

ORDER BY sorts results — ASC (default) for ascending, DESC for descending
You can sort by multiple columns — the second column is the tie-breaker
Each sort column can have its own direction (ASC or DESC)
NULL values sort as the lowest value — first in ASC, last in DESC
LIMIT N returns only N rows — essential for "top N" queries
OFFSET M skips M rows before returning results
Pagination formula: LIMIT page_size OFFSET (page - 1) * page_size
Always pair LIMIT with ORDER BY for predictable results
SQL clause order: SELECT → FROM → WHERE → ORDER BY → LIMIT → OFFSET
For large tables, keyset pagination (WHERE id > last_id) outperforms OFFSET

Quick Reference

Pattern	Syntax
Sort ascending	`ORDER BY col ASC`
Sort descending	`ORDER BY col DESC`
Multi-column sort	`ORDER BY col1 ASC, col2 DESC`
NULLs last (ASC)	`ORDER BY col IS NULL, col ASC`
Top N	`ORDER BY col LIMIT N`
Pagination	`LIMIT size OFFSET (page-1)*size`
Keyset pagination	`WHERE id > last_id LIMIT size`

📚 Additional Resources

🚀 What's Next?

You can now filter, sort, and paginate — but what about asking questions about your data? "How many books do we have?" "What's the average price?" "Which author has the most books?" In the next lesson, you'll learn aggregate functions (COUNT, SUM, AVG, MIN, MAX) and GROUP BY — the tools that turn raw data into insights.

🎉 Congratulations!

Your queries now produce polished, organized results. Sorted, limited, and paginated — just like a real application!

📑 In This Lesson

ORDER BY — Sorting Results

Ascending Order (ASC) — The Default

Output:

Descending Order (DESC)

Output:

Sorting Text

Sorting Dates

Output (newest first):

Sorting with WHERE

Sorting by Multiple Columns

Output (notice the $8.99 books are now alphabetized):

💡 Why In-Stock DESC?

Output:

Sorting by Expressions

⚠️ Avoid Sorting by Column Number

How NULL Values Sort

Output:

Controlling NULL Position

Output:

💡 How This Works

LIMIT — Controlling Row Count

Basic LIMIT

Output:

✅ Common Patterns with LIMIT

LIMIT with WHERE

⚠️ LIMIT Without ORDER BY

OFFSET — Skipping Rows

Output (rows 4–6 by price):

Alternative Syntax

💡 Which Syntax to Use?

Pagination Patterns

Example: 3 Books Per Page

Page 1 Output:

Page 2 Output:

Getting the Total Count

⚠️ OFFSET Performance on Large Tables

Keyset Pagination Preview

SQL Clause Order

✅ Memory Aid

⚠️ Common Mistake

Exercises

🏋️ Exercise 1: Basic Sorting

🏋️ Exercise 2: LIMIT & Top N

🏋️ Exercise 3: Pagination

🏋️ Exercise 4: Combined Challenge

🎯 Quick Quiz

Summary

🎉 Key Takeaways

Quick Reference

📚 Additional Resources

🚀 What's Next?

🎉 Congratulations!