Sort and Trim Results: ORDER BY, LIMIT, DISTINCT

By default a database gives you rows in no promised order. Sometimes they come out in the order they were added, but you must never depend on it. If order matters to you, ask for it. In this lesson you learn to sort results, cut them to the top few, remove duplicates, and use a few helpful built-in functions.

ORDER BY: sort the result

SELECT name, price
FROM products
ORDER BY price;

That sorts from the smallest price to the largest. This is called ascending order and it is the default. Add DESC for descending, largest first.

SELECT name, price
FROM products
ORDER BY price DESC;

name         | price
Laptop stand | 1800
Backpack     | 1400
Mouse        | 700
Desk lamp    | 650
Water bottle | 300
Notebook     | 120
Pen pack     | 80
Sticky notes | 60

ORDER BY is always near the end of the query, after WHERE. Text sorts alphabetically and dates sort from earliest to latest when they are written as YYYY-MM-DD.

Sort by more than one column

When two rows tie on the first column, a second column decides. List them with commas. Each one can have its own direction.

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

category   | name         | price
Bags       | Backpack     | 1400
Home       | Desk lamp    | 650
Home       | Water bottle | 300
Office     | Laptop stand | 1800
Office     | Mouse        | 700
Stationery | Notebook     | 120
Stationery | Pen pack     | 80
Stationery | Sticky notes | 60

Categories are in alphabetical order, and inside each category the dearest product comes first. You can also sort by a calculated column or by its alias, for example ORDER BY price_with_tax.

LIMIT: only the first few rows

LIMIT cuts the result to the first N rows. Combined with ORDER BY it gives a “top N” list, one of the most common requests in reporting.

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

name         | price
Laptop stand | 1800
Backpack     | 1400
Mouse        | 700

Always use ORDER BY with LIMIT. A LIMIT without a sort returns some rows, but which ones is not promised. To skip rows, for example for page 2 of a list, add OFFSET: LIMIT 3 OFFSET 3 skips the first three and returns the next three.

DISTINCT: remove repeated values

Two customers live in Nairobi. If you ask for the city column, Nairobi appears twice. DISTINCT returns each different value once.

SELECT DISTINCT city FROM customers ORDER BY city;

city
NULL
Bengaluru
Chennai
Lagos
London
Nairobi
Toronto

NULL counts as a value here, and in SQLite it sorts first. DISTINCT works on the whole row you select. SELECT DISTINCT category, price returns each different pair of values, not each different category.

Handy functions

A function takes a value and returns a changed one. You call it with its name and brackets. Here are the ones you will use most. They work the same in nearly all engines, with small name differences.

SELECT UPPER(name) AS shout, LENGTH(name) AS letters
FROM customers
WHERE id <= 3;

shout        | letters
ASHA RAO     | 8
BEN CARTER   | 10
CHIDI OKAFOR | 12

• UPPER(x) and LOWER(x) change the case of text.
• LENGTH(x) counts characters (LEN in SQL Server).
• ROUND(x, 2) rounds a number to 2 decimal places.
• SUBSTR(x, start, length) takes part of a text (SUBSTRING in other engines). SUBSTR(order_date, 1, 7) gives the year and month, like 2024-06.
• x || y joins text in SQLite and PostgreSQL. MySQL uses CONCAT(x, y).

COALESCE: give NULL a fallback

Reports look odd with blank cells. COALESCE(a, b) returns the first value that is not NULL, so you can show a friendly default.

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers
WHERE id >= 7;

name         | city
Grace Mwangi | Nairobi
Hari Singh   | Unknown

This changes only the result you see. The table still holds NULL.

CASE: if this, then that

CASE lets a query choose a value by a rule, like an if statement. This is how you turn numbers into labels.

SELECT name, price,
  CASE
    WHEN price < 100 THEN 'cheap'
    WHEN price < 1000 THEN 'medium'
    ELSE 'premium'
  END AS band
FROM products
ORDER BY price;

name         | price | band
Sticky notes | 60    | cheap
Pen pack     | 80    | cheap
Notebook     | 120   | medium
Water bottle | 300   | medium
Desk lamp    | 650   | medium
Mouse        | 700   | medium
Backpack     | 1400  | premium
Laptop stand | 1800  | premium

The database checks the WHEN lines from top to bottom and uses the first one that is true. The ELSE is what you get when none match. Without an ELSE, unmatched rows become NULL. CASE is useful in reports, and it often comes up in interviews.

Your task

1. List all customers sorted by joined, newest first.
2. Show the three cheapest products.
3. List the different order statuses used in the orders table, each only once.
4. Show each customer name with their city, but show 'Not given' when the city is missing.
5. Label each order as 'open' when its status is shipped or pending, and 'closed' otherwise.

How to check it worked

• The newest customer first is Hari Singh (2024-06-01).
• The three cheapest are Sticky notes, Pen pack and Notebook.
• There are four different statuses: cancelled, delivered, pending and shipped.