SQL order by descending: how it works, and where NULL goes

In this article

1. Before you start
2. Why interviewers ask this
3. The SQL order by descending clause from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

Why interviewers ask this

A candidate who writes order by score desc and stops has answered the easy half. Screens in the SQL interview round, for analyst and data engineering roles, and the SQL rounds inside a coding interview loop, use sorting to check whether you know what the database promises and what it does not. The usual prompts are "return the top three scores", "why did my top-N query give different rows on a rerun", and "where do the missing values go".

A weak answer names the keyword. A strong answer says rows have no order without the clause, states where NULL goes on the engine in use, and adds a second sort column so ties cannot change the result. The interview loops hub shows where such a screen sits in a hiring process.

The SQL order by descending clause from first principles

In plain words

Order by clause

An order by clause is the final part of a select statement that sorts the result rows by one or more expressions before they are returned. Without it, the database may return rows in any order.

Tie-breaker

A tie-breaker is a sort key added last, usually a unique column such as an id, so that two rows with equal earlier keys always come back in the same order.

Picture a stack of exam papers. Sorting from the highest mark down is descending order, and from the lowest up is ascending. Papers with no mark yet are awkward: do they go on top or at the bottom?

The analogy breaks on a tie. A teacher would shuffle two equal papers by hand, but a database makes no promise about which comes first.

Precisely, order by sorts the result of the query by its keys, ascending unless desc is written for that key, with NULL placement and ties settled by the engine's rules.

The examples use one small table of six players with illustrative scores: Ada 70, Ben 85, Cy 85, Di with a NULL score, Eve 92 and Fay 70.¹

CREATE TABLE players(id INTEGER, name TEXT, team TEXT, score INTEGER);
INSERT INTO players VALUES
 (1,'Ada','red',70),(2,'Ben','blue',85),(3,'Cy','red',85),
 (4,'Di','blue',NULL),(5,'Eve','red',92),(6,'Fay','blue',70);

Step 1: Without order by, the row order is not promised

A select statement with no order by clause returns rows in an order the database is free to choose. The SQLite documentation says the order "is undefined", and the PostgreSQL documentation says the order depends on scan plans and disk order and "must not be relied on".

In the run, a plain select of names returned Ada, Ben, Cy, Di, Eve, Fay, which is the insertion order.¹ That is an accident of a tiny table with no indexes, not a promise. A bigger table, a new index or a different plan can change it, so a query whose answer depends on the order needs the clause every time.

The input is six rows. The output is the same six rows in whatever order the engine happens to produce.

Step 2: The key picks the value and desc puts large values first

An order by clause names a sort key, and desc after it returns the largest value first. The PostgreSQL documentation says ascending order is the default, ascending "puts smaller values first", and descending order is determined with the greater-than operator.

Sorting the players by score descending returned Eve 92, then Ben and Cy at 85, then Ada and Fay at 70, then Di with a NULL score last.¹ Writing asc explicitly returned the same order as writing nothing, so asc is only for readability.

SELECT name, score FROM players ORDER BY score DESC;

The same keyword also works on text and dates. For text, the order follows the collation. Collation is the rule that decides whether an uppercase letter sorts before a lowercase one. In SQLite's default binary collation, ascending order returned Apple, Banana, apple, cherry, with every capital letter before every lowercase one, and desc returned the exact reverse: cherry, apple, Banana, Apple. Adding a case-insensitive collation interleaved Apple and apple.¹

The input is the six rows. The output has the keys 92, 85, 85, 70, 70 and then the NULL.

Step 3: NULL rows go first or last, depending on the engine

The PostgreSQL 18 manual sorts NULL "as if larger than any non-null value", so nulls first is the default for descending order and nulls last otherwise. The SQLite documentation "considers NULL values to be smaller than any other values", so NULL ends a descending sort. The MySQL 8.4 manual puts NULL first in ascending and last in descending order.

Di therefore lands at the bottom in SQLite and MySQL and at the top in PostgreSQL. PostgreSQL and SQLite document a clause to override the default (nulls last), which changes nothing in SQLite here because its default already puts NULL last.¹ The MySQL 8.4 select syntax shows asc or desc only, so it has no nulls last.

The portable fix is one extra sort key placed before the real one: a test that is 1 for a NULL score and 0 otherwise. Rows with 0 sort first, so every NULL row ends last on any engine.

SELECT name, score FROM players
ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score DESC;

In the run this returned Eve 92, Ben 85, Cy 85, Ada 70, Fay 70 and Di with the NULL last.¹

Text description of the diagram

• PostgreSQL sorts NULL as larger than any value, so ascending puts NULL last and descending puts NULL first.
• SQLite and MySQL sort NULL as smaller, so ascending puts NULL first and descending puts NULL last.
• PostgreSQL and SQLite document nulls first and nulls last; the MySQL 8.4 syntax shows asc or desc only.

Step 4: A second sort key orders the ties

When two rows are equal on the first key, the later keys decide, and if all keys are equal the order is undefined. The PostgreSQL documentation says "the later values are used to sort rows that are equal according to the earlier values", and the SQLite page says two rows with all equal keys are returned in an undefined order.

Direction applies to each key on its own. The PostgreSQL documentation notes that order by x, y desc means x ascending and y descending, which differs from descending on both. Sorting the players by team, then score descending, then name returned the blue team first (Ben 85, Fay 70, Di NULL) and then red (Eve 92, Cy 85, Ada 70).¹ Putting desc on the team key as well reversed the team order and left the score order alone.

The input is rows tied on team. The output uses the score to order the rows inside each team, and the name would settle any rows still tied on both.

Step 5: A limit keeps the top rows, so the ties must be broken first

A limit clause keeps only the first rows of the sorted result, which makes top-N queries order-dependent. The PostgreSQL page on limit and offset warns that with a limit it is important to use an order by "that constrains the result rows into a unique order", otherwise "you will get an unpredictable subset".

Ben and Cy both score 85, so a top-two query has two valid answers unless a tie-breaker is present. With the name as a tie-breaker, the run returned Eve 92 and Ben 85.¹ Without it, a top-three query still returned the scores 92, 85, 85, but top-two could keep either 85. A tie-breaker column makes a limit repeatable.

SELECT name, score FROM players
ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score DESC, name
LIMIT 2;

The input is the fully sorted rows. The output is the first two, and the tie-breaker makes them the same on every run.

1 · No promise2 · Key and direction3 · NULL placement4 · Ties5 · LimitReplay

Step-by-step description

1. Without order by, the row order is not promised.
2. The key picks the value; descending puts large values first.
3. Where NULL goes depends on the engine unless you say.
4. A second sort key orders rows that tie on the first.
5. A limit keeps the top rows, so ties must be broken first.

• Without an order by clause the row order is not promised.
• Descending puts the largest value first.
• A tie-break column makes a limit repeatable.

Worked example

The first two rows show that descending is the exact reverse of ascending on the keys, with the NULL moving from first to last.¹

Text description of the diagram

• Eve scores 92, Ben and Cy score 85, and Ada and Fay score 70.
• Equal scores sit next to each other, and a second sort key fixes their order.

Numbers stored as text sort as text. Three values stored as text, 9, 10 and 2, came back as 10, 2, 9 in ascending order, and casting them to integers and sorting descending returned 10, 9, 2.¹

Trade-offs and edge cases

The keyword is simple, and the traps are around it.

• Engines differ on NULL. The same descending query puts NULL first in PostgreSQL and last in SQLite and MySQL, so state the engine in an interview or write the explicit nulls clause where it exists. Where the engine has none, as in MySQL, the case test from Step 3, placed before the real key, moves the NULL rows by hand.
• Order is not inherited. An order by inside a view or subquery gives no promise about the outer result unless the outer query repeats it. Put the clause on the outermost select.
• Ordering by a position or an alias is allowed but fragile. SQLite treats a constant integer K as the K-th result column and also accepts an output alias. The run gave the same order with order by 2 desc and with the alias.¹ A later edit to the column list changes what a number means.
• Sorting is not free. The database must see all rows before it can return the first one, unless an index already holds them in order. That is why a limit on a large table is usually paired with an index on the sort key, a topic the system design hub takes further.
• Text order follows collation. Capital letters, accents and locale rules change the order, so compare engines before relying on text sorts.
• Joins add their own row-count surprises. Sorting never changes how many rows come back; the SQL join Venn diagram page explains why a join can.

How to answer it in an interview

The spoken structure is: write the query, name the tie risk, name the NULL risk, then add the key that fixes both.

Common mistakes

• "Rows come back in the order I inserted them" (wrong). No engine promises that, and a plan change can break it (right: add order by whenever the order matters).
• "Descending always puts NULL last" (wrong). It does in SQLite and MySQL but not in PostgreSQL (right: name the engine or add the case key from Step 3).
• "Order by score desc, name desc sorts by score descending and name ascending" (wrong). Each key carries its own direction, so name is descending too (right: write desc only on the keys that need it).
• "A limit gives the top N rows" (wrong when ties sit at the cut-off). The subset is unpredictable without a unique order (right: end the order by with a unique column).
• "Sorting the text 10, 2, 9 gives 2, 9, 10" (wrong). Text sorts character by character, so 10 comes before 2 (right: cast to a number before sorting).

Follow-up questions

What if two players tie for the last place in a top-three query?

A limit cuts after the third row, so a tie at the cut-off keeps an arbitrary tied row; the PostgreSQL 18 select page offers fetch first n rows with ties, which returns "any additional rows that tie for the last place". The portable route is the rank window function: rank() over (order by score desc) <= 2 keeps every row tied with the last place, so it returned Eve, Ben and Cy because Ben and Cy tie for second.¹ DENSE_RANK answers a different question, the top three distinct scores 92, 85 and 70, which covers five players.¹

What is the default sort order in SQL?

Ascending, smallest value first, as in PostgreSQL's documentation, which says ascending order is the default; SQLite and MySQL state the same. In the run, writing asc gave the same rows as writing nothing, and only descending reversed them.

How do I sort one column descending and another ascending?

Give each key its own direction: order by team, then score desc. Direction applies to the key it follows, so team stayed ascending and score ran descending in the example, with blue before red.

Does a view or subquery keep its order by?

Not as a promise. Only the outermost order by controls the order of the final result, so repeat the clause on the query that the application reads.

Why do my numbers sort as 10, 2, 9?

Because the column holds text, and text compares one character at a time, so the character 1 sorts before 2. Casting the column to an integer sorted 10, 9, 2 in descending order in the example. Storing numbers in a numeric column avoids the cast.

Sources

1. PostgreSQL 18 documentation, 7.5. Sorting Rows (ORDER BY) — PostgreSQL Global Development Group (page shows version 18.6; read 2026-10-25), 25 Oct 2026
2. PostgreSQL 18 documentation, 7.6. LIMIT and OFFSET — PostgreSQL Global Development Group (read 2026-10-25), 25 Oct 2026
3. SQLite documentation, SELECT: the ORDER BY clause — SQLite project (read 2026-10-25), 25 Oct 2026
4. MySQL 8.4 Reference Manual, 5.3.4.8 Sorting Rows (tutorial) — Oracle (MySQL documentation; read 2026-10-25), 25 Oct 2026
5. MySQL 8.4 Reference Manual, Working with NULL Values — Oracle (MySQL documentation; read 2026-10-25), 25 Oct 2026
6. PostgreSQL 18 documentation, SELECT (FETCH FIRST ... WITH TIES) — PostgreSQL Global Development Group (read 2026-10-25), 25 Oct 2026
7. MySQL 8.4 Reference Manual, SELECT Statement syntax — Oracle (MySQL documentation; read 2026-10-25), 25 Oct 2026