Skip to main content
All SQL lessons

Level 7 of 13 · The sorting hat

SQL ORDER BY, LIMIT and OFFSET for Beginners

Sorting results ascending and descending, sorting by two columns, taking a top list with LIMIT and OFFSET, and where NULLs end up.

Covers: ASC / DESC · two-column sorts · LIMIT · OFFSET

Practise ORDER BY

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Sort rows: ORDER BY You need it for any list people read in order: cheapest first, newest first, A to Z. Without ORDER BY, the database returns rows in no guaranteed order. ORDER BY sorts them. - `ORDER BY price` sort by price, smallest first > Never rely on the order rows happen to come out in. If the order matters, write ORDER BY, even when the rows already look sorted.

SELECT name, price
FROM pizzas
ORDER BY price

# Biggest first: DESC - `DESC` descending: biggest first - `ASC` ascending: smallest first, the default > Text sorts alphabetically, and dates from oldest to newest. ORDER BY ordered_at DESC puts the newest first.

SELECT name, price
FROM pizzas
ORDER BY price DESC

# Sort by two columns - `ORDER BY city, name` by city first; within the same city, by name > Each column can have its own direction: ORDER BY city, price DESC.

SELECT city, name
FROM customers
ORDER BY city, name

# Top lists: LIMIT and OFFSET - `ORDER BY price DESC LIMIT 3` the 3 most expensive - `OFFSET 1` skip the first row before taking 3 > LIMIT without ORDER BY gives you any 3 rows, not the top 3. Always sort before you limit.

SELECT name, price
FROM pizzas
ORDER BY price DESC
LIMIT 3 OFFSET 1

# Where NULL goes In MySQL, NULL sorts as smaller than any value: first with ASC, last with DESC. Orders with no tip come first here. > Want the NULLs last in ascending order? ORDER BY tip IS NULL, tip. tip IS NULL is 0 for real tips and 1 for NULL, so the NULLs go to the end.

SELECT id, tip
FROM orders
ORDER BY tip

Appian corner

In Appian, a!sortInfo(field: …price, ascending: false) is ORDER BY price DESC. It goes in a!pagingInfo(startIndex, batchSize, sort), where startIndex does the job of OFFSET (it starts at 1).

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

  1. 1. Pizza names and prices, most expensive first.

    SELECT name, price
FROM pizzas
ORDER BY price ___
    Show the answer
    SELECT name, price
FROM pizzas
ORDER BY price DESC

    Descending, in three letters plus one.

  2. 2. Newest orders first: every order’s id and ordered_at, the latest at the top.

    Show the answer
    SELECT id, ordered_at
FROM orders
ORDER BY ordered_at DESC

    ORDER BY ordered_at DESC: the biggest date is the newest.

  3. 3. The 3 cheapest pizzas, cheapest first.

    Show the answer
    SELECT *
FROM pizzas
ORDER BY price
LIMIT 3

    ORDER BY price LIMIT 3

  4. 4. Which pizza comes first?

    SELECT name FROM pizzas
ORDER BY spicy DESC, price
    • Margherita
    • Diavola
    • Pepperoni
    • Pineapple Anchovy
    Show the answer

    Pepperoni. Spicy ones first (spicy = 1), and of those the cheapest: Pepperoni at 10 beats Diavola at 12.

  5. 5. Customers sorted by city A to Z, then by name inside each city. Show city and name.

    Show the answer
    SELECT city, name
FROM customers
ORDER BY city, name

    ORDER BY city, name

  6. 6. The 2nd and 3rd most expensive pizzas. Skip the most expensive one.

    Show the answer
    SELECT *
FROM pizzas
ORDER BY price DESC
LIMIT 2 OFFSET 1

    ORDER BY price DESC LIMIT 2 OFFSET 1

Practise ORDER BY