SQL ORDER BY and LIMIT

ORDER BY sorts your results, and LIMIT caps how many rows come back. Together they answer every "top 5" or "latest 10" question.

Syntax

SELECT columns
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC]
LIMIT n OFFSET m;

Examples

1. Sort ascending (the default)

Cheapest products first:

SELECT product_name, price
FROM products
ORDER BY price
LIMIT 5;

Result:

(5 rows)

2. Sort descending

Most expensive products first:

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

Result:

(5 rows)

That's a "top 5 products by price" query, the classic top-N pattern.

3. Sort by multiple columns

Sort by city, then by name within each city:

SELECT customer_name, city
FROM customers
WHERE city IN ('Bengaluru', 'Mumbai', 'Pune')
ORDER BY city, customer_name;

Result:

(4 rows)

The second column only breaks ties in the first.

4. Sort by a calculated column or alias

Unlike WHERE, ORDER BY runs after SELECT, so aliases work:

SELECT product_name,
       price - cost_price AS margin
FROM products
ORDER BY margin DESC
LIMIT 3;

Result:

(3 rows)

5. Control where NULLs go

Undelivered orders have a NULL delivery_date. Put them first to see what's pending:

SELECT order_id, status, delivery_date
FROM orders
ORDER BY delivery_date DESC NULLS FIRST
LIMIT 6;

Result:

(6 rows)

6. Pagination with OFFSET

Show products 6 to 10 by price (page 2 with 5 per page):

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;

Result:

(5 rows)

Page p with n rows per page uses LIMIT n OFFSET (p - 1) * n.

Common mistakes

• Using LIMIT without ORDER BY. "Top 5" means nothing without a sort; the database can return any 5 rows.
• Ties at the cut-off. If the 5th and 6th rows have the same value, LIMIT 5 picks one arbitrarily. Add a tie-breaker column (ORDER BY price DESC, product_id) or use RANK.
• Large OFFSETs. OFFSET 100000 still reads and throws away 100,000 rows. For deep pagination, filter on the last seen value instead.

Practice question

Show the 3 most recent orders paid by UPI.

SELECT order_id, order_date, status
FROM orders
WHERE payment_method = 'UPI'
ORDER BY order_date DESC
LIMIT 3;

Key takeaways

• ORDER BY sorts ascending by default; add DESC for descending.
• Multiple sort columns break ties left to right.
• LIMIT n returns the first n rows after sorting; OFFSET skips rows.
• Always pair LIMIT with ORDER BY for a predictable result.