Sorting, Limiting and DISTINCT

A table has no guaranteed order. If the order of rows matters, you must ask for it.

ORDER BY

SELECT name, price
FROM products
ORDER BY price;

The default is ascending. Add DESC for largest first:

SELECT name, price
FROM products
ORDER BY price DESC;

Rows are sorted by the first column, and ties are broken by the next.

SELECT name, category, price
FROM products
ORDER BY category, price DESC;

You can sort by an alias or by a calculation:

SELECT name, price * 1.18 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC;

Combine ORDER BY with LIMIT to get the highest or lowest rows.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

LIMIT without ORDER BY returns an arbitrary set of rows. Always sort first when you want “the top”.

Other databases spell this differently: SQL Server uses SELECT TOP 3, and standard SQL uses FETCH FIRST 3 ROWS ONLY.

OFFSET skips rows before returning any. Together with LIMIT it pages through results.

SELECT order_id, order_date
FROM orders
ORDER BY order_date
LIMIT 3 OFFSET 3;

This returns rows four to six.

DISTINCT removes duplicate rows from the result.

SELECT DISTINCT category
FROM products;

With several columns, it returns each distinct combination:

SELECT DISTINCT customer_id, status
FROM orders
ORDER BY customer_id;

In SQLite, NULL values come first in ascending order. Other databases differ, so check when it matters.

SELECT name, city
FROM customers
ORDER BY city;

SELECT   columns
FROM     table
WHERE    row filter
ORDER BY sort
LIMIT    row count

The clauses must appear in this order. Later lessons add GROUP BY and HAVING between WHERE and ORDER BY.

List the three most recent orders, showing the order id, date and status.