ORDER BY, LIMIT and DISTINCT

Rows have no order until you ask

A database does not promise any order for the rows it returns. If you need "cheapest first" or "newest first", you must say so with ORDER BY. ASC means ascending (small to big, A to Z) and is the default. DESC means descending (big to small, Z to A).

SELECT name, price FROM products ORDER BY price DESC;

Sorting by more than one column

You can sort by several columns. The database sorts by the first one, and only when two rows are equal there does it look at the second one.

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

LIMIT and OFFSET: pages of results

LIMIT n keeps only the first n rows. OFFSET m skips the first m rows. Together they give pages: with 10 items per page, page 1 is LIMIT 10 OFFSET 0, page 2 is LIMIT 10 OFFSET 10, and page p is LIMIT 10 OFFSET (p - 1) * 10.

SELECT name, price FROM products
ORDER BY price ASC
LIMIT 3 OFFSET 3;

DISTINCT and AS

DISTINCT removes repeated rows from the result. SELECT DISTINCT category FROM products lists each category once, even if fifty products share it. AS gives a column a new name in the result, called an alias, which makes the output easier to read.

SELECT DISTINCT category FROM products ORDER BY category;

SELECT name AS product, price AS price_in_rupees FROM products;