Sorting and Limiting with ORDER BY and LIMIT in SQL

What You Will Learn

• The ORDER BY clause and how to sort ascending or descending
• Sort by multiple columns
• How NULLs are handled in sorting
• The LIMIT clause to return only the top N rows

Why This Topic Matters

Without sorting, query results come back in an unpredictable order — usually the order rows were stored. That is useless for analysis. "Top 10 customers by revenue", "5 most recent orders", "lowest-priced products" — all of these need ORDER BY + LIMIT. Ranking queries are some of the most common in business analytics.

Sample Table

We reuse the orders table from lesson 18:

Basic ORDER BY — Ascending

SELECT *
FROM orders
ORDER BY amount;

Default is ascending: NULL, 500, 750, 900, 1200, 1500. (NULL handling varies by database — see below.)

Descending — Highest First

SELECT *
FROM orders
ORDER BY amount DESC;

Use DESC for descending, ASC for ascending (default). Result: 1500, 1200, 900, 750, 500, NULL.

Sort by Multiple Columns

SELECT *
FROM orders
ORDER BY customer ASC, amount DESC;

First sort by customer alphabetically. Within each customer, sort by amount largest first. Result:

Notice Ravi's two orders: the ₹1200 one comes before the NULL one because we sorted DESC by amount within each customer.

Sort by Computed Column

SELECT customer, amount, amount * 0.10 AS tax
FROM orders
ORDER BY tax DESC;

Sort by the calculated tax column. You can also order by an expression directly: ORDER BY amount * 0.10 DESC.

Sorting NULLs

NULL handling in ORDER BY varies by database. The defaults:

To make the behavior explicit, use:

-- PostgreSQL / SQLite (also MySQL 8+):
SELECT * FROM orders ORDER BY amount DESC NULLS LAST;

-- For databases without NULLS FIRST/LAST:
SELECT * FROM orders ORDER BY (amount IS NULL), amount DESC;

The second trick uses a boolean expression: amount IS NULL returns TRUE (1) for NULL rows and FALSE (0) for non-NULL rows. Sorting by that first puts all non-NULL rows ahead of NULLs.

LIMIT — Return Only the Top N

SELECT *
FROM orders
ORDER BY amount DESC
LIMIT 3;

Returns the top 3 rows by amount: 1500, 1200, 900. Without ORDER BY, LIMIT returns arbitrary rows — always combine them.

LIMIT with OFFSET — Skip Rows

SELECT *
FROM orders
ORDER BY amount DESC
LIMIT 3 OFFSET 2;

Skip the first 2 rows, then return the next 3. Useful for pagination — "show me orders 11 through 20".

Dialect differences

Real Example — Top 3 Customers by Revenue

SELECT customer, SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer
ORDER BY total DESC
LIMIT 3;

Result:

This single query combines SELECT, WHERE, GROUP BY, ORDER BY, and LIMIT — five clauses working together. We will break GROUP BY down in lesson 21.

Common Mistakes

1. Using LIMIT without ORDER BY. The rows returned are arbitrary. Always sort first.
2. Forgetting that ASC is the default. If you forget DESC, your "top 10" returns the bottom 10.
3. Sorting on a column with mixed types. If amount has both numeric and text values, the sort breaks. Clean the data first.
4. Forgetting dialect differences for LIMIT. SQL Server and Oracle use different syntax. Use the one your database supports.
5. Not handling NULLs explicitly. If NULLs appear at the top of your "top 10" list, that is why. Use NULLS LAST or the boolean trick.

Practical Exercise (10 minutes)

Using the sample orders table:

1. Write a query to return all orders sorted by date, most recent first.
2. Write a query to return the 3 smallest orders by amount.
3. Write a query to return orders sorted by city (A→Z), then by amount (largest first) within each city.
4. Write a query to return the second-highest order by amount. (Hint: LIMIT 1 OFFSET 1.)

Mini Challenge

Write a query that returns the most recent order per customer. (Hint: this requires either a window function — lesson 23 — or a self-join. For now, see if you can do it with ORDER BY and LIMIT inside a subquery.)

Key Takeaways