Lesson · 10 min

Sort and limit

The order of rows

A query does not promise any order unless you ask for one. The ORDER BY clause sorts the rows. It goes after WHERE (if there is one).

SELECT title, price FROM books ORDER BY price;

By default, the order is ascending: from small to large, or from A to Z. Add DESC for descending order.

SELECT title, price FROM books ORDER BY price DESC;

Separate the columns with commas. The database sorts by the first column. When two rows have the same value, it uses the next column.

SELECT genre, title FROM books ORDER BY genre, title;

LIMIT keeps only the first rows of the result. It is the last clause of the query. Together with ORDER BY, it gives you "the top N".

SELECT title FROM books ORDER BY year DESC LIMIT 2;

SELECT DISTINCT removes rows that are the same as an earlier row. Use it to find the different values in a column.

SELECT DISTINCT author FROM books;

The clauses must be in this order: SELECT, FROM, WHERE, ORDER BY, LIMIT.

Write one query for each task, one after the other. Tasks 1 to 4 check the order of the rows. Task 5 does not.

5 to do

Task 1, not checked yet. Select the title and price of every book. Put the cheapest book first. Use ORDER BY.
Task 2, not checked yet. Select the title and year of every book. Put the newest book first.
Task 3, not checked yet. Select the title and price of the three most expensive books, the most expensive one first. Use ORDER BY and LIMIT.
Task 4, not checked yet. Select the genre and title of every book. Sort by genre first, and then by title.
Task 5, not checked yet. Select each genre one time only. Use DISTINCT.

Task 1 of 5

Select the title and price of every book. Put the cheapest book first. Use ORDER BY.