Sorting, the first N, no repeats

Problem

Top 3 for the shop window

The owner wants the three most expensive products on the home page. You know how to get the name and the price. But in what order do the rows come, and how do you take just three?

The idea

ORDER BY

ORDER BY sorts the result. By default from small to large:

SQLdatabase: shop

SELECT name, price FROM products ORDER BY price;

SELECT name, price FROM products ORDER BY price;

▶ RunRestore the queryFresh database

DESC reverses it, from large to small:

SQLdatabase: shop

SELECT name, price FROM products ORDER BY price DESC;

SELECT name, price FROM products ORDER BY price DESC;

▶ RunRestore the queryFresh database

You can sort by several columns: first by the first, and on a tie by the second. Customers by city, and within a city by name:

SQLdatabase: shop

SELECT name, city FROM customers ORDER BY city, name;

SELECT name, city FROM customers ORDER BY city, name;

▶ RunRestore the queryFresh database

Text sorts alphabetically, dates like 2026-10-19 by time.

More precisely, if you are curious

Without ORDER BY, the order of the rows is not promised. It often comes out by id, but that is an accident of how the database read the table. After a change to the table or with a new index, the order can change. When order matters to you, write ORDER BY.

The idea

LIMIT: only the first

LIMIT n returns the first n rows of the result. Together with ORDER BY it gives a top N:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

LIMIT runs last, after sorting. That is why “the three most expensive” is right. Without ORDER BY it would be three random products.

OFFSET skips rows. That is how pages are made: the second page of orders, 10 per page:

SQLdatabase: shop

SELECT id, ordered_at, status FROM orders
ORDER BY id
LIMIT 10 OFFSET 10;

SELECT id, ordered_at, status FROM orders ORDER BY id LIMIT 10 OFFSET 10;

▶ RunRestore the queryFresh database

Try it

The new customers

Show the name and sign-up date of the five newest customers, newest first.

SQLdatabase: shop

SELECT name, joined_at FROM customers;

SELECT name, joined_at FROM customers;

Check▶ Run

The idea

DISTINCT: no repeats

Which cities are the customers from? With SELECT city, each city repeats as many times as it has customers. DISTINCT removes the repeats:

SQLdatabase: shop

SELECT DISTINCT city FROM customers;

SELECT DISTINCT city FROM customers;

▶ RunRestore the queryFresh database

Six cities and NULL: Boris has no city, and that absence is a separate result too.

Try it

The order statuses

Show all the different order statuses, in alphabetical order.

SQLdatabase: shop

Check▶ Run

Exercise

Cheap and in stock

Show the name, price and stock of the five cheapest products that are in stock, cheapest first.

SQLdatabase: shop

Check▶ Run

More precisely, if you are curious

Where do NULLs go when sorting? In SQLite and MySQL, ORDER BY city puts them first; in PostgreSQL and Oracle they come last. If it matters, say so: ORDER BY city NULLS LAST. SQLite understands it since version 3.30.

SQLdatabase: shop

SELECT name, city FROM customers ORDER BY city NULLS LAST;

SELECT name, city FROM customers ORDER BY city NULLS LAST;

▶ RunRestore the queryFresh database

Test

Check yourself

1. In what order do the rows of a SELECT without ORDER BY come?

In whatever order the database picks: the order is not promisedIn the order they were addedBy id

2. What does LIMIT 10 OFFSET 20 do?

Skips 20 rows and returns the next 10Rows 10 to 20Returns 20 rows starting from the tenth

3. SELECT DISTINCT city FROM customers also returns NULL. Why?

A bug in SQLiteDISTINCT removes repeats, and NULL is one of the distinct resultsBecause NULL equals empty text

4. How do you write “most expensive first”?

ORDER BY priceORDER BY price DESCORDER BY DESC price

Check

Start the lesson over