SQL · Guides · October 30, 2026 · 12 min read

SQL ORDER BY: ASC, DESC, multiple columns, NULLS LAST and top N

ORDER BY sorts the rows of a query result. Write it at the end of the query, list the columns to sort by, and add ASC (ascending, the default) or DESC (descending) after each one: ORDER BY range DESC puts the longest-range aircraft first. With several columns, the second one only breaks ties in the first. Without ORDER BY, the database returns rows in no guaranteed order, so any query whose order matters, and every query with LIMIT, needs one. Two details trip people up most: where NULLs go (it differs between databases, so write NULLS FIRST or NULLS LAST), and ties at the cut-off of a top N.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output.

ORDER BY syntax

SELECT columns
FROM table
WHERE condition
ORDER BY column1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
         column2 [ASC | DESC] ...
LIMIT n;

ORDER BY comes after WHERE, GROUP BY and HAVING, and before LIMIT. It is also one of the last steps the database performs, which is why it can use aliases from SELECT (more on that below).

Ascending and descending order

The nine aircraft models, sorted by range from shortest to longest:

SELECT model, range
FROM aircrafts
ORDER BY range;

ORDER BY range is the same as ORDER BY range ASC. For longest first, write ORDER BY range DESC. Numbers sort by value, dates and timestamps from earliest to latest, and text alphabetically (with caveats covered in the section on sorting text).

ORDER BY multiple columns

List several columns separated by commas. Rows are sorted by the first; the second is used only when the first is equal, and so on. Each column gets its own direction. Aircraft grouped by manufacturer A to Z, and within each manufacturer by range from longest to shortest:

SELECT split_part(model, ' ', 1) AS maker, model, range
FROM aircrafts
ORDER BY maker, range DESC;

A frequent mistake is writing ORDER BY maker, range DESC and expecting both columns to be descending. DESC applies only to the column right before it; maker here is still ascending.

Sort by alias, column position or expression

ORDER BY can refer to:

• a column alias from SELECT: maker above is not a real column, it is an alias for an expression;
• an expression: ORDER BY split_part(model, ' ', 1) gives the same result;
• a column number: ORDER BY 2 DESC sorts by the second column of the SELECT list;
• a column that is not selected at all: SELECT city FROM airports ORDER BY timezone is valid.

Sorting a grouped result by its aggregate, by alias or by position:

SELECT status, COUNT(*) AS flights
FROM flights
GROUP BY status
ORDER BY flights DESC;   -- or ORDER BY 2 DESC, or ORDER BY COUNT(*) DESC

Positions (ORDER BY 2) are handy while exploring, but fragile in saved queries: add a column at the start of SELECT and the sort silently changes. Prefer names in anything someone else will read.

Two restrictions to know. An alias can be used on its own but not always inside an expression: PostgreSQL rejects ORDER BY flights * 2 when flights is an alias. And with SELECT DISTINCT, PostgreSQL requires every ORDER BY expression to appear in the select list (“for SELECT DISTINCT, ORDER BY expressions must appear in select list”); DuckDB is more lenient, but don’t rely on it.

Custom sort order with CASE

Alphabetical order of statuses is meaningless; the lifecycle order is what people want to see. Give each value a number with CASE and sort by it:

SELECT status, COUNT(*) AS flights
FROM flights
GROUP BY status
ORDER BY CASE status
           WHEN 'Scheduled' THEN 1
           WHEN 'On Time'   THEN 2
           WHEN 'Delayed'   THEN 3
           WHEN 'Departed'  THEN 4
           WHEN 'Arrived'   THEN 5
           ELSE 6
         END;

The ELSE puts anything unexpected at the end instead of the start. The same trick pins one value to the top: ORDER BY city = 'Moscow' DESC, city lists Moscow first and the rest alphabetically (in PostgreSQL and DuckDB, true sorts after false, hence DESC). MySQL also has ORDER BY FIELD(status, 'Scheduled', 'On Time', ...), which does not exist elsewhere. More CASE patterns are in the CASE WHEN guide.

NULLS FIRST and NULLS LAST

Which flights departed with the longest delay? 16,348 flights have not departed yet, so their actual_departure, and the computed delay, is NULL:

SELECT flight_no, departure_airport, scheduled_departure,
       EXTRACT(EPOCH FROM actual_departure - scheduled_departure) / 60 AS delay_min
FROM flights
ORDER BY delay_min DESC NULLS LAST
LIMIT 5;

Change it to NULLS FIRST and the top of the list is flights with no delay figure at all:

What happens if you write neither depends on the database:

So the same ORDER BY delay_min DESC LIMIT 5 returns the real top 5 in DuckDB but five NULLs in PostgreSQL. PostgreSQL, Oracle, DuckDB and SQLite (since 3.30) accept NULLS FIRST/NULLS LAST. MySQL and SQL Server do not; there, sort by a flag first: ORDER BY delay_min IS NULL, delay_min DESC in MySQL, or ORDER BY CASE WHEN delay_min IS NULL THEN 1 ELSE 0 END, delay_min DESC in SQL Server. The NULL guide has more on how NULL behaves.

ORDER BY with LIMIT: top N rows

ORDER BY plus LIMIT is how you get “the top N”. The four most frequent destinations from Sheremetyevo (SVO):

SELECT arrival_airport, COUNT(*) AS flights
FROM flights
WHERE departure_airport = 'SVO'
GROUP BY arrival_airport
ORDER BY flights DESC, arrival_airport
LIMIT 4;

Ties at the cut-off

Three airports, GOJ, PES and ULV, each have 122 flights, and only two fit into the top 4. Without the second sort column, arrival_airport, the database is free to pick any two of them, and it does: five runs of the query with only ORDER BY flights DESC gave five different pairs in 3rd and 4th place (PES and ULV, GOJ and PES, ULV and PES, GOJ and ULV, PES and GOJ). A report that changes on every refresh is a bug. Two fixes:

1. Add a tie-breaker that makes the order unique, as above. The result is stable, but the cut is still arbitrary: ULV is out only because of its name.
2. Keep all tied rows. PostgreSQL 13+ and Oracle support FETCH FIRST 4 ROWS WITH TIES, SQL Server has TOP 4 WITH TIES. DuckDB and MySQL have neither; use RANK():

SELECT arrival_airport, flights
FROM (
  SELECT arrival_airport, COUNT(*) AS flights,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM flights
  WHERE departure_airport = 'SVO'
  GROUP BY arrival_airport
) t
WHERE rnk <= 4
ORDER BY flights DESC, arrival_airport;

Five rows, because three airports share 3rd place. For the top N within each group (top 3 routes per airport, for example) see the window functions guide.

LIMIT in other databases

Pagination with OFFSET

OFFSET skips rows before LIMIT starts counting. Page 2 of the aircraft list, three per page:

SELECT model, range
FROM aircrafts
ORDER BY range DESC
LIMIT 3 OFFSET 3;

The standard form OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY returns the same rows and also works in SQL Server, where OFFSET requires an ORDER BY. Pagination only makes sense with a unique order: with ties, a row can appear on two pages or on none. On large tables, deep offsets are slow because the database still has to produce and skip all the earlier rows; applications usually switch to “keyset” pagination (WHERE range < last_seen_value ORDER BY range DESC LIMIT 3).

Sorting text: numbers stored as text and letter case

Text is compared character by character, so numbers stored as text sort “wrongly”. Seat numbers on the Airbus A319 are text like 1A or 10C:

SELECT seat_no FROM seats WHERE aircraft_code = '319' ORDER BY seat_no LIMIT 8;

Row 10 comes before row 1 because “1” followed by “0” sorts before “1” followed by “A”. Sort by the numeric part, then the letter:

SELECT seat_no, fare_conditions
FROM seats
WHERE aircraft_code = '319'
ORDER BY CAST(regexp_extract(seat_no, '^[0-9]+') AS INTEGER), right(seat_no, 1)
LIMIT 8;

In PostgreSQL, replace regexp_extract(seat_no, '^[0-9]+') with substring(seat_no FROM '^[0-9]+'). The same problem hits IDs and amounts that were loaded as text: '10' < '9'. The real fix is to store them as numbers.

Letter case is the other surprise. DuckDB compares text by character codes, so all capitals come before all lower-case letters:

SELECT x FROM (VALUES ('banana'), ('Cherry'), ('apple'), ('Apple')) t(x)
ORDER BY x;

PostgreSQL with a typical en_US collation returns apple, Apple, banana, Cherry; MySQL and SQL Server with their default case-insensitive collations treat apple and Apple as equal. To get the same order everywhere, sort by lower(x), with x as a tie-breaker:

Random order

To pick random rows, sort by a random number:

SELECT member_id, signup_channel
FROM loyalty_members
ORDER BY random()
LIMIT 3;

Your three rows will differ; that is the point. The function is random() in PostgreSQL, DuckDB and SQLite, RAND() in MySQL, and SQL Server uses ORDER BY NEWID(). Sorting a whole large table to keep three rows is expensive; for a sample of a big table, PostgreSQL and DuckDB offer TABLESAMPLE (and DuckDB also USING SAMPLE).

Common mistakes

• Relying on the “natural” order. Without ORDER BY the order is whatever the engine finds convenient, and it can change between runs or after an update.
• ORDER BY in a subquery or view. Most databases do not promise to keep it in the outer query (SQL Server rejects it without TOP). Sort in the outermost query.
• DESC on one column, expecting it on all. Each column needs its own direction.
• No tie-breaker before LIMIT. Ties make top N and pagination unstable.
• Forgetting where NULLs go. Write NULLS LAST or NULLS FIRST whenever the sort column can be empty.
• Sorting numbers stored as text. Cast them, or fix the column type.

These rules come up again in the basic SQL queries guide and the SQL cheat sheet.

FAQ

What is the default sort order in SQL ORDER BY?

Ascending (ASC): smallest numbers, earliest dates and the start of the alphabet first. DESC reverses it. Where NULLs go by default depends on the database, so state it with NULLS FIRST or NULLS LAST.

How do I ORDER BY multiple columns?

List them separated by commas, each with its own direction: ORDER BY maker ASC, range DESC. The second column only decides the order among rows that are equal in the first.

Can I use a column alias in ORDER BY?

Yes. ORDER BY runs after SELECT, so SELECT COUNT(*) AS flights ... ORDER BY flights DESC works in all major databases. You cannot use the alias in WHERE, which runs before SELECT.

How do I put NULL values last?

Add NULLS LAST after the column: ORDER BY delay_min DESC NULLS LAST. In MySQL and SQL Server, which lack this syntax, sort by an “is null” flag first, for example ORDER BY delay_min IS NULL, delay_min DESC in MySQL.

Can I ORDER BY a column that is not in SELECT?

Yes, except with SELECT DISTINCT (in PostgreSQL and SQL Server) or when the column is not available after GROUP BY. In a grouped query, you can sort by a grouped column or by an aggregate such as COUNT(*).

Does ORDER BY slow down a query?

Sorting costs time on large results, especially without LIMIT. With ORDER BY ... LIMIT n databases use a top-N sort that keeps only n rows in memory, and an index on the sort column can avoid sorting altogether in PostgreSQL, MySQL or SQL Server.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文