Sorting and limiting results with ORDER BY / LIMIT

Sorting with ORDER BY

To sort data by a particular column, use ORDER BY. If you don't specify ORDER BY, the order the rows come back in is not guaranteed. Always specify it whenever you need a consistent order.

SELECT *
FROM products
ORDER BY price DESC;

• ASC (ascending): smallest-to-largest, oldest-to-newest (this is the default when omitted)
• DESC (descending): largest-to-smallest, newest-to-oldest

Sorting by multiple columns

Separating columns with commas sorts by "the first priority, then the second priority when the first is tied."

SELECT *
FROM products
ORDER BY category ASC, price DESC;

Limiting the number of rows with LIMIT

To limit how many rows come back, use LIMIT. This is handy when you just want to peek at the first N rows without fetching everything.

SELECT *
FROM products
LIMIT 5;

Skipping rows with OFFSET (pagination)

OFFSET lets you specify how many rows to skip from the start. It's commonly used for page-by-page navigation in web services.

-- page 1 (rows 1-5)
SELECT * FROM products LIMIT 5 OFFSET 0;
-- page 2 (rows 6-10)
SELECT * FROM products LIMIT 5 OFFSET 5;

Combining WHERE + ORDER BY + LIMIT

SELECT name, price FROM products
WHERE category = 'PC'
ORDER BY price DESC
LIMIT 3;