Sorting and deduplicating: ORDER BY, LIMIT, DISTINCT

Master sorting results with ORDER BY, limiting the number of rows with LIMIT and removing duplicates with DISTINCT.

6 min · 3 questions

By default, the order of the rows returned by a SELECT is not guaranteed: the database returns them however it finds convenient. To impose an order, add ORDER BY followed by a column. Sorting is ascending by default (you can make it explicit with ASC); add DESC for a descending sort.

SELECT title, price FROM books ORDER BY price;

SELECT title, price FROM books ORDER BY price DESC;

You can sort on several columns: ORDER BY genre, price DESC sorts first by genre, then, within the same genre, from most expensive to cheapest. The LIMIT clause keeps only the first N rows of the result: combined with ORDER BY, it gives you a "top N". LIMIT 3 OFFSET 2 would additionally skip the first 2 rows.

SELECT title, price FROM books
ORDER BY price DESC
LIMIT 3;

One last tool: DISTINCT, placed right after SELECT, removes duplicates from the result. Our table contains 7 books but only 3 different genres: without DISTINCT, the query below would display 7 rows with repetitions.

SELECT DISTINCT genre FROM books;

SELECT DISTINCT author FROM books;

Knowledge check

Make sure you remember the key points of this lesson.

1. How do you sort the books from most expensive to cheapest?
   • ORDER BY price
   • ORDER BY price DESC
   • SORT BY price DESC
2. What does SELECT DISTINCT genre FROM books; return on our table?
   • The 7 genres, one per book
   • Each genre only once, so 3 rows
   • The most frequent genre
3. What does LIMIT 5 return when used without ORDER BY?
   • The 5 most recent rows
   • 5 rows in an order that is not guaranteed
   • A syntax error