Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 125 · Querying Data

Sorting with ORDER BY

Sort query results with SQL ORDER BY. Learn ASC and DESC, multiple sort columns, NULLS FIRST/LAST, sorting by expressions and aliases, custom orders and collations.

A table is a set of rows, and a set has no built-in order. When you run SELECT without asking for an order, the database returns rows in whatever order is cheapest for it: often the order they were inserted, sometimes the order of an index, and possibly a different order tomorrow after the data or the query plan changes. If the order of the result matters to you or to your users, you must ask for it with ORDER BY.

You already met ORDER BY in Your First SQL Queries. This lesson covers everything else: sorting by several columns, mixing directions, placing NULLs, sorting by calculations and aliases, building a custom order, and how collations decide whether "apple" comes before "Banana".

ASC and DESC

ORDER BY goes after WHERE (and is the last clause of a query, apart from LIMIT). It sorts in ascending order by default: smallest numbers, earliest dates and A-to-Z text first. Add DESC for descending order. You can write ASC explicitly, but it changes nothing:

SQL Most expensive products · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The Ultralight Tent at 189 comes first, followed by the speakers and headphones. Remove DESC to see the five cheapest products instead.

How values compare depends on their type:

  • Numbers sort by value: 9 before 10 before 100.
  • Text sorts character by character, using the column's collation (more below).
  • Dates stored as ISO text, such as '2025-03-07', sort correctly as text, because the year comes first and every part has a fixed width. That's one of the reasons ISO 8601 dates are the norm in SQL.

Sorting by several columns

List several columns separated by commas. The database sorts by the first one; rows that are equal in the first column are then sorted by the second, and so on. Sorting customers by country and then by name gives a tidy directory:

SQL Sort by country, then name · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The four Australian customers come first, sorted by last name (Esposito, Garcia, Kaya, Usman), then the three in Canada. In Colombia there are two Garcias, and the third sort column puts Felix before Yusuf. The second and third columns only matter within a group of equal values in the columns before them.

Each column gets its own direction. DESC applies only to the column it follows, so the next query lists categories in ascending order and, inside each category, products from the most expensive down:

SQL Mixed directions · database: shop

Press Run (or Ctrl + Enter) to see the result here.

ORDER BY a, b DESC is not ORDER BY a DESC, b DESC. A common mistake is to write DESC once at the end, expecting it to reverse every column. It reverses only b; a is still ascending.

Ties and a stable order

When two rows have the same value in every sort column, their order relative to each other is not defined. The database may return them in any order, and the order can change between runs. Several employees in the company database share a salary:

SQL Breaking ties · database: company

Press Run (or Ctrl + Enter) to see the result here.

Three people earn 93,000 and three earn 84,000. In the first result their order within each salary is whatever the database chose. The second query adds tie-breaker columns, so the result is fully determined. When you show results page by page (LIMIT, OFFSET and Pagination), a unique last sort column, often the primary key id, is essential: without it, a row can appear on two pages or on none.

Where NULLs go

NULL means "no value", so it can't be compared with anything. Each database picks a fixed position for it: SQLite, MySQL and SQL Server put NULLs first in ascending order, PostgreSQL and Oracle put them last. With DESC the position flips. Working with NULL has the full table.

The standard NULLS FIRST and NULLS LAST options make the position explicit. SQLite (3.30 and later), PostgreSQL and Oracle support them:

SQL NULLS LAST · database: school

Press Run (or Ctrl + Enter) to see the result here.

The first query starts with enrollments that have no grade yet; the second skips past them to the lowest real grades. MySQL and SQL Server don't support NULLS LAST, but a portable trick works everywhere: sort by a test first.

SQL
-- Works in every database: rows with a grade (0) before rows without (1)
ORDER BY CASE WHEN grade IS NULL THEN 1 ELSE 0 END, grade

-- Shorter in SQLite, PostgreSQL and MySQL, where a comparison is a value
ORDER BY grade IS NULL, grade

Sorting by expressions and aliases

You can sort by any expression, not only by a column. Here the shop's products are ranked by the value of their stock, price * stock:

SQL Sort by a calculation · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query sorts by its alias stock_value. That works because ORDER BY is evaluated after the SELECT list, so it can see the names you gave your columns. (It's one of the few places where you can use a column alias; see Column and Table Aliases.) The second query sorts by the length of the name, with the name itself as a tie-breaker, and you don't even have to show the length in the result.

That last point applies to columns too: you may sort by a column that isn't in the SELECT list. SELECT name FROM products ORDER BY price DESC is perfectly valid. (The exception is SELECT DISTINCT, where most databases require the sort columns to appear in the result; you'll see why in DISTINCT.)

Sorting by position

You can also refer to a column by its position in the SELECT list, starting at 1:

SQL
SELECT name, price
FROM products
ORDER BY 2 DESC;   -- the second column: price

It's handy for quick, throwaway queries, but avoid it in saved code: if someone later adds a column at the start of the SELECT list, the query silently sorts by something else. Name the column or alias instead.

A custom order with CASE

Sometimes the natural order isn't alphabetical or numeric. Order statuses have a business order (pending, shipped, delivered, cancelled), and alphabetical order would put cancelled first. A CASE expression maps each value to a number you choose:

SQL Custom sort order · database: shop

Press Run (or Ctrl + Enter) to see the result here.

Pending orders appear first, then delivered ones, then the cancelled orders at the end, each group in date order. CASE gets its own lesson in CASE Expressions. MySQL also offers ORDER BY FIELD(status, 'pending', 'shipped', ...) for the same job, but CASE works everywhere.

Collations: how text is compared

A collation is the set of rules a database uses to compare text: whether case matters, whether accents matter, and which alphabet order applies. SQLite has three built-in collations:

Collation Rule
BINARY (default) Compares the raw bytes. Uppercase letters (A–Z) sort before lowercase letters, and accented letters after both.
NOCASE Like BINARY, but treats ASCII A–Z and a–z as equal.
RTRIM Like BINARY, but ignores trailing spaces.

Apply a collation to one sort column with COLLATE:

SQL Binary versus case-insensitive sorting

Press Run (or Ctrl + Enter) to see the result here.

The default BINARY order is Apple, Banana split, apricot, banana, cherry, Éclair: all capitalized words first, because uppercase letters have smaller byte values. With NOCASE you get the dictionary-like Apple, apricot, banana, Banana split, cherry, Éclair. The É still sorts last, since NOCASE only folds English letters.

Other databases have much richer, language-aware collations, which is how a German, Swedish or Turkish application gets its alphabet right:

SQL
-- PostgreSQL: an ICU collation for German
SELECT name FROM fruits ORDER BY name COLLATE "de-x-icu";

-- MySQL 8: the default utf8mb4_0900_ai_ci is accent- and case-insensitive
SELECT name FROM fruits ORDER BY name COLLATE utf8mb4_0900_as_cs;

-- SQL Server
SELECT name FROM fruits ORDER BY name COLLATE Latin1_General_CS_AS;

The default collation also differs: MySQL and SQL Server usually sort case-insensitively out of the box, while SQLite and PostgreSQL (with the "C" collation) compare case-sensitively.

Numbers stored as text

A classic sorting surprise is numbers stored in a text column. Text compares character by character, so '100' comes before '9' (because '1' < '9'). If you can't fix the column type, convert while sorting:

SQL
ORDER BY CAST(code AS INTEGER)

You'll learn more about types and conversions in Types and CAST.

Random order

To shuffle rows, sort by a random value. In SQLite and PostgreSQL that's ORDER BY random(), in MySQL ORDER BY RAND() and in SQL Server ORDER BY NEWID(). Combined with LIMIT 1, it picks a random row. It's fine for small tables, but the database must generate a number for every row and sort them all, so avoid it on very large tables.

Check your understanding

Quick check · 5 questions

of 5 answered You scored / 5 · perfect!

  1. 1. What does ORDER BY country, signed_up DESC do?

    Each sort column has its own direction. DESC applies only to signed_up; country uses the default ascending order.
  2. 2. In SQLite, which value comes first in ORDER BY grade when the column contains 70, NULL and 55?

    SQLite treats NULL as smaller than any value, so it sorts first in ascending order. Use NULLS LAST to move it.
  3. 3. Which query is valid in SQLite?

    ORDER BY runs after the SELECT list, so it can use the alias value. The others have syntax errors.
  4. 4. A text column holds '9', '10' and '100'. What does ORDER BY code return?

    Text compares character by character: '1' sorts before '9', and '10' is a prefix of '100', so the order is 10, 100, 9.
  5. 5. Why should a paginated query end its ORDER BY with a unique column like id?

    The order of tied rows is undefined. A unique tie-breaker makes the full order deterministic, so every row lands on exactly one page.

Summary

  • Without ORDER BY, the order of rows is undefined; always sort when the order matters.
  • ASC (default) sorts smallest first; DESC sorts largest first. Each sort column has its own direction.
  • Later sort columns only break ties in earlier ones; add a unique tie-breaker such as id for a deterministic order.
  • NULLS FIRST / NULLS LAST control where missing values go (SQLite, PostgreSQL, Oracle); ORDER BY x IS NULL, x is a portable alternative.
  • You can sort by expressions, by column aliases, by columns you don't select and (in quick queries only) by position.
  • Use CASE for a custom business order, and COLLATE (NOCASE, BINARY, RTRIM in SQLite) to control text comparison.

Next: LIMIT, OFFSET and Pagination, where you'll return just the first rows of a sorted result and page through the rest.