Q: Primary key vs. tiebreaker sort

When sorting by multiple columns, the first column is the primary sort key. Within each dept group, rows are sorted by the second key. Eng comes before Sales alphabetically. Within Eng, Bob (90000) outranks Carol (80000) on DESC salary. Within Sales, Alice and Dave both earn 70000, so they are tied on salary - their relative order is database-defined, but both come after Eng.

Q: Filtering before you sort

WHERE filters first - only rows where amount > 200 survive. That removes Alice's 200-dollar order and Carol's 150-dollar order. The remaining two rows are then sorted by amount DESC: 500 before 350. ORDER BY runs after WHERE in the logical query order.

Question 1

Sorting a text column

Accepted Answer

Sorting a text column alphabetically works the same as sorting numbers - ASC goes A to Z. Brazil < Denmark < France in alphabetical order. The database uses locale-aware collation rules, but for plain ASCII letters the result is standard alphabetical order.

Question 2

Primary key vs. tiebreaker sort

Accepted Answer

When sorting by multiple columns, the first column is the primary sort key. Within each dept group, rows are sorted by the second key. Eng comes before Sales alphabetically. Within Eng, Bob (90000) outranks Carol (80000) on DESC salary. Within Sales, Alice and Dave both earn 70000, so they are tied on salary - their relative order is database-defined, but both come after Eng.

Question 3

Filtering before you sort

Accepted Answer

WHERE filters first - only rows where amount > 200 survive. That removes Alice's 200-dollar order and Carol's 150-dollar order. The remaining two rows are then sorted by amount DESC: 500 before 350. ORDER BY runs after WHERE in the logical query order.

Question 4

Which way does DESC go?

Accepted Answer

ORDER BY price DESC sorts from highest to lowest. Gadget (25.00) is first, Doohick (15.00) second, Widget (10.00) last. DESC must be written explicitly - there is no default descending sort.

Question 5

Reading a salary sort

Accepted Answer

ORDER BY salary ASC sorts rows from lowest to highest salary. Bob (75000) comes first, Carol (82000) second, Alice (90000) last. ASC is the default direction and can be omitted - ORDER BY salary is equivalent.

Question 6

Multi-column sort with different directions

Accepted Answer

Level sorts A before B (ascending). Within level A, scores sort descending: Carol (500) before Alice (300). Within level B, scores sort descending: Dave (400) before Bob (200). Each sort key applies independently within the groups created by the keys before it.

Question 7

Sort by a computed value

Accepted Answer

You can ORDER BY any expression, not just a column name. The totals are: Alice 80000+5000=85000, Bob 70000+15000=85000, Carol 90000+2000=92000. Sorted DESC: Carol (92000) first, then Alice and Bob tied at 85000. Ties are resolved by storage order - Alice (id 1) comes before Bob (id 2). The result is Carol, Alice, Bob.

Question 8

Forcing NULLs to the front

Accepted Answer

NULLS FIRST overrides the default NULL ordering and puts NULL values before all non-NULL values regardless of ASC/DESC. Alice has NULL manager_id, so she comes first. Bob and Carol both have manager_id = 1, so their relative order is stable - Bob (id 2) before Carol (id 3) by insertion order.

Question 9

Where does NULL land in a sort?

Accepted Answer

In PostgreSQL, NULLs sort last for ASC and first for DESC by default. The SQL standard treats NULLs as greater than any other value for sorting purposes. You can override this with NULLS FIRST or NULLS LAST: ORDER BY salary ASC NULLS FIRST puts NULLs at the top even in an ascending sort. MySQL sorts NULLs first for ASC, which differs from PostgreSQL - always test when portability matters.

Question 10

What does ORDER BY 2 sort on?

Accepted Answer

An integer in ORDER BY refers to the position of the column in the SELECT list - 1 is the first column, 2 is the second, and so on. ORDER BY 2 DESC is equivalent to ORDER BY salary DESC here. This syntax is valid but discouraged because adding or reordering SELECT columns silently changes what you sort by. Prefer referencing the column by name for clarity.

Question 11

Who are the top two scorers?

Accepted Answer

ORDER BY score DESC ranks players from highest to lowest score, then LIMIT 2 takes only the top 2. Carol (95) is first, Alice (90) is second. This top-N pattern - ORDER BY ... DESC LIMIT N - is the standard way to fetch the highest-ranked rows.

Question 12

Sort by name length

Accepted Answer

LENGTH(name) computes the number of characters in each name: Bob = 3, Alice = 5, Charlotte = 9. Sorting by that expression ascending puts the shortest name first. Any scalar function or arithmetic expression is valid in ORDER BY - you are not limited to raw column references.

Question 13

Adding a column shifts the sort

Accepted Answer

Positional ORDER BY refers to the nth column in the current SELECT list. After inserting dept as the first column, name moves to position 2. The number 2 in ORDER BY now points to name, not salary - silently changing the sort without any error. This is exactly why naming columns explicitly (ORDER BY salary) is safer than using positional references.

Question 14

Lowest-stock products first

Accepted Answer

To find the bottom-N rows, sort ASC and limit. Stock sorted ascending: Gamma (2), Alpha (5), Delta (8), Beta (12). LIMIT 2 keeps only the two lowest-stock products - Gamma then Alpha. The bottom-N pattern mirrors top-N exactly: just flip DESC to ASC.

Question 15

Result order without ORDER BY

Accepted Answer

Without ORDER BY, the SQL standard makes no guarantee about row order. The database is free to return rows in whatever order is most efficient - heap scan order, index scan order, or something else entirely. In practice the order often looks consistent across repeated runs, which fools people into depending on it. Any code that relies on unordered output is a latent bug waiting to surface after an index change, a vacuuming event, or a schema migration.