In ORDER BY last_name, first_name DESC, which columns sort descending, and how do you reverse both?

## What ORDER BY is asked to do ORDER BY is the only construct in SQL that constrains the order of the rows a query returns. It takes a comma-separated list of *sort specifications*, and each specification has the shape: ```sql <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ``` The crucial detail — and the one this question is really testing — is that ASC and DESC are part of a single sort specification. They attach to the expression they follow, not to the whole ORDER BY list. There is no "set the direction for everything" switch in standard SQL. ## Reading the clause left to right The sort keys form a lexicographic (dictionary-style) comparison. To order two rows, the engine compares them on the first key. If they differ, that decides the order and no further key is examined. Only if they are equal on the first key does the second key get a vote, and so on. Every key after the first is therefore a *tie-breaker*, applied to a progressively smaller set of tied rows. This is exactly how a phone book works: surname decides most of the ordering, and given names only matter among people who share a surname. ## Walking through the example Take these rows: ```text (Adams, Zoe) (Adams, Amy) (Baker, Carl) ``` `ORDER BY last_name, first_name DESC` produces: ```text (Adams, Zoe) (Adams, Amy) (Baker, Carl) ``` The Adams rows come before Baker because `last_name` is ascending. Within the two Adams rows, `first_name DESC` puts Zoe before Amy. Nothing about the DESC touched `last_name`. Swap the intent and you must swap the syntax: ```sql ORDER BY last_name DESC, first_name DESC -- Baker, then Adams/Zoe, then Adams/Amy ``` ## Why the mistake is so easy In English, "sort by last name and first name, descending" naturally reads as if *descending* covers the whole phrase. SQL parses it the other way: the modifier is local. The bug is quiet — the query runs, returns rows, and the first column simply happens to be in the wrong direction — so it usually survives review and is discovered by someone reading a report. ## Mixed directions are a feature, not a workaround Many real orderings genuinely mix directions: ```sql SELECT department, employee_name, salary FROM employees ORDER BY department ASC, salary DESC, employee_name ASC; ``` Departments in alphabetical order; inside each department, highest salary first; and among people on identical salaries, alphabetical by name. Writing ASC explicitly, as above, costs nothing and makes the intent unmistakable to the next reader. ## The direction applies to the whole key expression A sort key can be an expression, and the direction applies to that expression's value, not to any part of it: ```sql ORDER BY (unit_price * quantity) DESC, order_id ASC; ``` There is no way to say "descending on the multiplication but ascending on the price" — if you need that, they are two separate keys. ## What extra keys buy you Each additional key resolves more ties. Rows that remain equal on *every* key you listed are in an unspecified order: SQL makes no promise about their relative position, and the same query can return them differently on a later run. If a stable, repeatable order matters, the last key should be something unique for the row, such as the primary key. ## Practical checklist - Write ASC explicitly when a list mixes directions — it removes all doubt. - Read every comma as "and then, for ties, ...". - Remember that DESC never leaks left to the previous key or right to the next one. - Finish the key list with a unique column when you need the same output every time. ## A note on what ORDER BY does not do ORDER BY orders the rows the query already computed; it does not filter them, does not change which rows appear, and does not affect grouping. Adding a second sort key never adds or removes a row — it only decides which of two tied rows is printed first.