Since the first lesson of the module we've been repeating the same warning: the result set has no guaranteed order. It's time to take control. ORDER BY is the only way to make a query return rows in a particular order, and although it looks like the simplest clause in SQL, it hides half a dozen details that separate a query that works by accident from one that always works: what happens with ties, where nulls go, why a capital letter can end up somewhere odd, and why sorting by column number is a time bomb.

1. Without ORDER BY there's no guaranteed order
2. ASC and DESC
3. Sorting by several columns and breaking ties
4. Sorting by alias, by expression and by ordinal position
5. Nulls: NULLS FIRST and NULLS LAST
6. Sorting text: collation and accents
7. Sorting dates
8. ORDER BY in the logical execution order
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

Let's make it explicit once and for all. This query:

returns, today, on your machine, products 1 to 20. And that gives you a false sense of security, because the SQL standard promises no order when there's no ORDER BY, and neither does PostgreSQL. What you're seeing is the order in which the engine found the rows as it walked the data file.

Real circumstances in which that order changes without you touching the query:

You can check the first case yourself, if you dare modify the data (remember that the script from 01-06 is idempotent and you can reload it):

Product 1 will now appear at the end of the list. Nothing has failed, nothing warns you: the order simply was never guaranteed.

Absolute rule: if the order of the rows matters for your report, your application or your pagination, write ORDER BY. There are no shortcuts, there are no exceptions, and "but it always comes out right" is not an argument.

ORDER BY is written at the end of the query and states which column to sort by and in which direction:

The whole catalogue from most to least expensive. There's a business answer for you already: the matcha is the most expensive product (€22.00) and the crushed tomato the cheapest (€1.95).

Since ASC is the default, these two are identical:

Writing ASC explicitly isn't mandatory, but in queries with several columns and mixed directions it helps readability a lot.

When two rows have the same value in the sort column, their relative order is not defined. It's the same problem as in section 1, on a smaller scale.

The five rows of category 1 do come out together, but in what order among themselves? Whichever the engine sees fit. To pin it down you add more columns to the ORDER BY, separated by commas: the second breaks ties in the first, the third breaks ties in the second, and so on. Each column can carry its own ASC or DESC.

A perfectly presentable catalogue: grouped by category and, within each one, from the most to the least expensive item.

Three columns, with customer data:

Read it from the outside in: first everybody from France, then Portugal, then Spain; within each country the cities in alphabetical order, and within each city the surnames. The two customers from Barcelona end up sorted by surname (Belmonte before Bosch) and the four from Valencia do too (Carrasco, Ferrer, Llorens, Martínez).

Professional tip: always finish the ORDER BY with a unique column. Adding , id as the last criterion guarantees the order is deterministic: the same query returns exactly the same order today and a year from now. It's essential for the pagination you'll see in 02-06, and for automated tests not to fail at random.

Here the debt from 02-02 is settled: since ORDER BY runs after SELECT, the alias already exists and can be used.

Notice the real tie in rows 13 and 14: the lip balm (product 9) and the orange juice (product 17) both leave exactly €2.80 of margin. The trailing , id is what makes 9 come out before 17 reproducibly; without it, the order between the two would be the engine's business.

And notice that we've sorted by id without projecting it. That's perfectly legal (unlike what happened with SELECT DISTINCT in 02-04): ORDER BY can use any column from the FROM's tables, whether or not it appears in the result.

You can also repeat the full expression instead of using the alias. The result is identical:

(First 5 of 20 rows.)

This is the ranking by percentage margin, not by absolute margin, and it gives a very different result: the bamboo toothbrush, which only leaves €2.30 per unit, is the product that contributes the highest margin percentage (65.7 %). Sorting by the right metric matters as much as calculating it correctly.

SQL lets you sort by the number of the column within the SELECT:

It's equivalent to ORDER BY price DESC, because price is the second column in the list.

It works, it's standard and it's very widespread in quick, exploratory queries. But it's fragile and it shouldn't reach code that gets saved:

Course rule: ORDER BY 2 to explore in psql; a column name or alias in any query you're going to save.

A nuance that catches people out: the ordinal only works in ORDER BY (and in GROUP BY, module 4). In WHERE it means nothing, and ORDER BY 2 + 1 does not sort by the third column: PostgreSQL reads that as a constant expression (the number 3) and ignores it as a sort criterion, because it's the same for every row.

A NULL isn't greater or smaller than anything: it's unknown. But to sort you have to put it somewhere, so each engine makes an arbitrary decision. PostgreSQL considers NULL to be the largest value.

That's where its defaults come from:

Let's see it with orders.employee_id, which is NULL on the ten orders that came in through the web:

The ten orders handled by sales reps (4, 5 and 6) first, and the ten web orders at the end. If you want the nulls at the top, you ask for it explicitly:

Now orders 1, 3, 5, 7, 9, 11, 13, 15, 17 and 19 head the list, followed by sales rep 4's, then 5's and then 6's.

The same with customers.referred_by_id:

The seven customers who arrived on their own at the top, and below them the eight who came by recommendation, grouped by who brought them in: Lucía has brought three (Carlos, Marta and Inés), which makes her GreenStore's best referrer.

This is one of the dialect differences that causes the most headaches when migrating queries:

In other words: the same query ORDER BY employee_id puts the ten web orders at the end in PostgreSQL and at the start in MySQL. If your report shows the first ten rows, you'll see completely different data.

On engines that don't accept NULLS FIRST/LAST you emulate it with an auxiliary sort column, taking advantage of the fact that a boolean sorts false before true:

The expression employee_id IS NULL is false (0) for the rows with a value and true (1) for the null ones, so sorting by it pushes the nulls to the end. It's a trick worth keeping in your pocket.

Sorting numbers or dates is arithmetic. Sorting text is culture, and that's where things get interesting.

A collation is the set of rules that decides whether 'apple' comes before or after 'Banana', whether 'café' and 'CAFE' count as equal when sorting, and whether 'ch' counts as one letter or two. Every PostgreSQL database is created with a default collation, which you can check:

The two usual scenarios:

You can check it on your installation with a direct comparison:

On a database created with a linguistic collation (the usual case):

'apple' < 'Banana' is true with linguistic rules —the letter a comes before b, and case only breaks ties— and false with the C collation, because in UTF-8 the capital B is encoded as byte 0x42, well below the 0x61 of lowercase a. The same query, two different orders, depending on how the database was created.

To force a particular collation in a query:

COLLATE "en-US-x-icu" applies English rules through the ICU library, which PostgreSQL has bundled since version 10. Notice Moreau before Moreira: they share More, and then a comes before i.

You can see the collations available on your server with:

Practical notes about collations:

• Collation also affects WHERE, LIKE and indexes. An index created with one collation is no use for sorting with another (module 8).
• Sorting with an explicit COLLATE is slower than using the default collation, because it stops the index being reused.
• Changing the collation of an existing database is a major operation: every text index has to be rebuilt. It's a decision you make when creating the database.
• If you need an order "without accents or case", the modern route in PostgreSQL is non-deterministic collations (CREATE COLLATION ... deterministic = false); the classic route is sorting by LOWER(unaccent(column)), with functions from module 6.

Dates sort chronologically, with no surprises, because the DATE type is a number on the inside:

(First 10 of 20 rows.)

The orders from the most recent to the oldest: the natural order of any work queue. In this dataset the date grows with the id, so sorting by date descending coincides with sorting by id descending; in a real database that needn't hold (an order can be recorded with a backdated date) and relying on it would be a mistake.

Important warning: this works because the columns are of type DATE. If a date were stored as text, the order would be alphabetical:

It's one more reason to store dates in date-typed columns, as was decided in 01-04 and 01-06.

ORDER BY is the second-to-last step: it runs after the rows have been filtered, projected and deduplicated, and only LIMIT comes afterwards.

Consequences you've already seen in action:

And a performance consideration you'll pick up again in module 8: sorting costs. PostgreSQL can avoid the work if an index already returns the rows in the requested order; if not, it has to sort the whole set in memory (or on disk, if it doesn't fit). An ORDER BY over a table of millions of rows without a suitable index is one of the most frequent causes of slow queries.

One last note: if a query with ORDER BY is used as a subquery (module 7) or inside a view (module 10), that order doesn't necessarily propagate to the outer query. The ORDER BY that rules is the outermost one.

• Relying on the order without ORDER BY. It works until the day it doesn't. It's the most expensive mistake in this lesson.
• Forgetting the tie-breaker. Two rows with the same value can come out in any order, and that order can change between runs. Always finish with a unique column.
• Writing DESC once and thinking it affects the whole list. ORDER BY a, b DESC sorts a ascending and b descending. If you want both descending: ORDER BY a DESC, b DESC.
• Sorting by ordinal in production code. ORDER BY 2 breaks silently when somebody reorders the SELECT.
• Assuming nulls go where you think. PostgreSQL puts them at the end in ASC; MySQL, at the start. If it matters, write NULLS FIRST or NULLS LAST.
• Sorting dates stored as text. Alphabetical order, not chronological. Use the DATE type.
• Sorting numbers stored as text. '10' < '9' is true alphabetically. The same problem.
• Being surprised by where capitals or accented letters land. It depends on the database's collation. Check it with SHOW lc_collate;.
• Tip: use COLLATE only when you really need it, because it stops indexes being used.
• Tip: for reports, sort by what the reader is looking for. A product listing is sorted by name if they're going to look for a specific one, and by price if they're going to compare.
• Tip: check the order at the extremes. Look at the first and last row: they usually give away an inverted ASC/DESC or misplaced nulls immediately.

Management wants the catalogue listing as it will appear on the website: active products only, grouped by category from lowest to highest, and within each category in alphabetical order by name. Show category_id, id, name and price. Explain why the result has 19 rows.

HR asks for the payroll sorted by salary from highest to lowest, showing name, last_name, job_title, salary and manager_id. Add whatever is needed for the order to be deterministic and answer: where does Rosa Alcázar Vives appear and why doesn't her null manager_id affect the order?

On order_lines, get the lines sorted by amount from highest to lowest, showing id, order_id, product_id, quantity and the amount rounded to two decimals. Write the query in two ways —using the alias and repeating the expression— and explain why both work here but only one of them would work in a WHERE.

Solution 1

19 rows because WHERE active discards product 20 (Spirulina capsules), the only discontinued one. And that's why category 6 doesn't appear in the listing: it was its only product.

Two observations about the order:

• Within each category, the id is no longer increasing: category 2 starts with product 8 because "Almond body oil" comes alphabetically before "Aloe vera", "Calendula" and "Rosemary". It's the proof that the order is dictated by the ORDER BY and not by the table.
• In category 4, "Ceremonial matcha" comes before "Cold-pressed orange juice" because they share the initial C and the second letter breaks the tie (e before o); accents play no part here because they sit beyond the point where the order has already been decided. If some product began with a lowercase or an accented letter, in a database with the C collation it would appear at the very end of the whole listing. That's the effect from section 6.

Solution 2

Rosa Alcázar Vives appears first, and not because of her null manager_id but because she has the highest salary (€62,000). Her null manager_id has no influence whatsoever on the order, because that column takes no part in the ORDER BY: it appears in the result but not as a sort criterion. It's an important distinction: nulls only alter the order of the columns you sort by.

The trailing , id guarantees determinism. In this data no salary repeats, so it changes nothing today; the day two sales reps are hired on the same pay, the listing will still always come out the same.

Solution 3

The version with an alias:

The version with the expression repeated:

Both return the same thing. The first ten rows of the 47:

Why both work here. ORDER BY runs after SELECT, so by that point the alias amount already exists and the expression can also be re-evaluated. Both forms are valid and PostgreSQL generates the same plan.

Why only one would work in WHERE. WHERE runs before SELECT, so the alias hasn't been born yet: WHERE amount > 30 would give column "amount" does not exist. Only the version with the full expression can be used to filter, as you saw in 02-03. This whole lesson rests on that same asymmetry in the logical execution order.

Notice as well the tie in rows 7 and 8: lines 9 and 42 are both worth exactly €22.00 (one unit of matcha in each case). The trailing , id is what decides that 9 comes out first. Without it, the order between the two would be unpredictable, and a paginated report could end up showing the same line twice or not at all.

You're now in control of the order of your results:

• Without ORDER BY there's no guaranteed order. An UPDATE, an index or parallelism can change it with no warning.
• ASC (the default) and DESC decide the direction, and each ORDER BY column carries its own.
• Sorting by several columns resolves ties, and finishing with a unique column makes the order deterministic: essential for pagination and for tests not to fail at random.
• You can sort by alias, by expression and by ordinal position; the last one only for exploring, because it breaks silently.
• Nulls go at the end in ASC and at the start in DESC in PostgreSQL, exactly the opposite of MySQL, SQLite and SQL Server. NULLS FIRST / NULLS LAST makes it explicit.
• The order of text depends on the database's collation: with C, capitals come first and accented letters go to the very end; with linguistic or ICU rules, everything lands where you'd expect. COLLATE "en-US-x-icu" forces English rules.
• Dates sort chronologically if —and only if— they're stored in date-typed columns.
• ORDER BY is step 4 of the logical order: the aliases already exist, every column is still visible, and only LIMIT lies ahead.

In the module's last lesson, Limiting Results with LIMIT, you'll add step 5 and close the full cycle of a query. You'll see why LIMIT is the first thing you write when exploring an unfamiliar table, how pagination is built with OFFSET, why that pagination degrades and drifts out of sync on large tables, and what serious applications do instead.