SQL Order By: how to sort rows ascending and descending

In this article

1. Before you start
2. Why interviewers ask this
3. SQL order by 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

Sorting is one clause of a query; the SQL interview questions guide covers the other clauses around it.

Why interviewers ask this

A query that "looks sorted" but is not is a classic bug: the output is right on the developer's laptop and wrong in production. Interviewers use order by questions to check whether a candidate knows that row order is never implied, that ties and nulls need a decision, and that sorting happens late in a query.

A weak answer says "it sorts the table." A strong answer says it sorts the result, not the table, names the direction keywords, and mentions what happens to ties and to missing values. A Staff-level answer adds that a deterministic result needs a sort that ends in a unique column, which matters for paging through results and for tests that compare output.

SQL order by from first principles

Sort key

A sort key is a column or expression whose values the database compares to decide the order of result rows, so that rows with a smaller key value come first in ascending order and later in descending order.

Tie

A tie is a pair or group of result rows whose sort key values are equal, so the sort key alone cannot say which row comes first.

In plain words

Think of a stack of index cards on a desk. Putting them in order by surname is sorting. Without a rule, the cards stay in whatever order they landed. The analogy breaks in one place: a database table is not a physical stack with a fixed order. The PostgreSQL manual says that without sorting, the rows come back in an unspecified order. That order depends on the scan and join plan types and the order on disk. You must not rely on it.

In one precise sentence, order by takes the rows a query has already produced and arranges them by the sort keys you list, left to right, each with its own direction.

The general shape, from the PostgreSQL syntax, is select select_list from table_expression order by sort_expression1 [asc | desc], followed by more sort expressions after commas. The SQLite grammar has the same ingredients. It adds an optional collation (the rule for comparing text) on each term.

Step 1: A sort key and a direction set the main order

order by salary sorts the rows by the salary column. Writing asc means ascending, smallest first; leaving the keyword out gives the same result, so order by salary and order by salary asc are the same sort. Writing desc means descending, largest first.

The sort key sets the main order of the result. Input: the rows the query produced. Output: the same rows in key order. The key can be any expression valid in the select list, for example order by a + b, as the PostgreSQL manual shows. Text sorts by its collation (SQLite's default, the binary collation, puts uppercase before lowercase), dates sort by time, and numbers sort by value. Many engines also accept a position, so order by 1 sorts by the first output column; names are safer, because a position silently changes meaning when the select list changes. You now know the main order; the next step covers what happens when two rows share a key.

Step 2: A second column settles ties

When two rows have equal keys, they are a tie, and the database may return them in either order: SQLite's documentation says the order of rows that are equal on every order by expression is undefined. Run the same query twice and the tied rows can swap places, so output that looks fine in a quick test can break later. The fix is to add more sort keys after commas: order by salary desc, name asc. The database compares the first key; only for rows that tie does it look at the second, then the third.

Input: rows tied on the first key. Output: those rows ordered by the next key. To make a whole result deterministic, end the list with a column that is unique, such as an id. Direction is chosen per column, so order by dept asc, salary desc mixes both. You now know how to order any result completely; the last step covers missing values.

Step 3: Nulls need a placement

A sort must place nulls at the top or the bottom of each key's order. A null is a marker meaning "no value known"; it is not zero and not an empty string. In PostgreSQL, by default nulls sort as if larger than any non-null value: nulls first is the default for desc and nulls last otherwise. You can override this with nulls first or nulls last, and the options apply independently to each sort column. The SQLite grammar also lists both options, but SQLite treats a null as smaller than any other value, so nulls come first under asc and last under desc, the opposite of PostgreSQL's default. Check your engine's manual or state the placement explicitly.

Input: a sort key containing nulls. Output: nulls at the top or the bottom of that key's order. The full clause grammar is on the PostgreSQL reference page for the select statement, if you need the formal syntax.

1 · Sort key2 · Tie-break3 · Null placementReplay

Step-by-step description

1. The sort key sets the main order.
2. A second column settles ties.
3. Nulls get a placement, at the top or the bottom.

• The sort key sets the main order.
• A second column settles ties.
• Nulls need a placement at the top or the bottom of the order.

Where does the sort happen in a query?

The sort runs near the end. The PostgreSQL manual says the output table is sorted after the select list has been processed. Two consequences follow: order by can use a column alias (a name given with as in the select list), and where cannot, because the filter runs earlier, before the alias exists. In PostgreSQL an output alias works in order by only as a bare name, not inside an expression, so order by total + 1 fails when total is an alias, as the select reference explains. The diagram below is simplified: limit runs after the sort, as the next section shows.

Text description of the diagram

• The sort runs after the select list is built.
• The sort can use an alias but the filter cannot.

How do order by and limit work together?

The limit clause keeps only the first rows of a sorted result, so order by runs first and limit cuts afterwards; standard SQL spells the cut fetch first n rows only, and offset skips rows before it. On the six rows below, order by salary desc nulls last, name limit 2 returns Asha and Eli.¹ This is the usual shape of a top-N or "second-highest" question.

The sort must be complete. The PostgreSQL manual on limit and offset says that an unconstrained order gives an unpredictable subset of rows. Without a unique last key, a tied row can appear on two pages of results or on none, because each page can break the tie differently. End the sort with a unique column such as an id.

Worked example

Take six rows of an employees table. The names, departments and salaries are illustrative, chosen to include ties and one null.¹

Four queries on this table, with results computed by script.¹ Names joined by a slash are tied, so their order is not promised.

A fifth query, order by dept, salary desc, name, gives Asha, Eli, Ben (the eng rows), then Dev, Cara, Fay (the ops rows).¹ Read it left to right: department first, then salary high to low inside each department, then name for the tied pair.

Text description of the diagram

• Under desc the null row comes first by default in PostgreSQL.
• Only a sort ending in a unique column promises the order of tied rows.

Trade-offs and edge cases

Sorting costs work. Every sorted query makes the database order its result, so sort the smallest result you can: filter with where first, and sort only what you return. A query that joins several tables sorts the joined result, so the same rule applies.

An order inside a subquery or view is not a promise. Only the outermost order by decides the order the reader sees: the PostgreSQL select reference says an order by and limit attached in parentheses apply to that subexpression only (as of October 2026). Some engines go further: Microsoft's Transact-SQL reference says the clause is not valid in views, inline functions, derived tables and subqueries unless top or offset and fetch are also given (as of October 2026). Put the order by on the outermost select.

Nulls differ by engine. The PostgreSQL rule above is one choice; SQLite treats nulls as smaller than any value, so they come first under asc. When a query must run on more than one engine, write nulls first or nulls last where supported, or sort on a helper expression such as the result of a case test; the conditional-total guide shows how case when builds such expressions.

Custom orders. Ordering by a conditional (case) expression lets you rank values that have no natural order, for example putting one status first. The expression is just another sort key; the guide to case statements in SQL shows how to write one.

Sorting is not a snapshot. Rows changed by another transaction between two runs can change the output, even with a perfect sort; transaction control language covers how changes are grouped and committed.

How to answer it in an interview

The spoken structure: say what it sorts, say the default and desc, then ties, then nulls.

Common mistakes

• "A table is stored in order, so select returns it sorted." (wrong) Why: the manual says the order is unspecified unless you sort. "Row order is never implied; I add order by whenever order matters." (right)
• "order by name fully orders my result." (wrong) Why: rows with equal names can swap between runs. "I end the sort with a unique column so the result is deterministic." (right)
• "Nulls always sort last." (wrong) Why: the placement depends on the engine and on the direction. "I check the default for my engine, or I write nulls last explicitly." (right)
• "order by is the same as group by." (wrong) Why: order by arranges rows, while group by collapses rows into groups. "Sorting changes the order of rows; grouping changes how many rows come back." (right)

Follow-up questions

How do you sort descending in SQL?

Add desc after the sort key: order by salary desc puts the largest value first. The keyword applies only to the column right before it, so in order by dept, salary desc the department still sorts ascending. Ranking rows inside groups builds on the same ordering ideas in window functions.

Is ascending the default in SQL order by?

Yes: leaving out the keyword gives ascending order, smallest value first, so order by salary and order by salary asc return the same order. Writing asc anyway makes the intent plain to the next reader.

Can you order by more than one column?

Yes. List the keys after commas; the database compares the first, and only tied rows are compared on the second, then the third. Each column has its own direction.

Where do nulls go when you sort?

It depends on the engine. PostgreSQL treats a null as larger than any value, so it comes last in ascending order and first in descending order unless you write nulls first or nulls last.

Does a select without order by return rows in a fixed order?

No. The PostgreSQL manual calls the order unspecified and says it must not be relied on, so any test or page that needs an order must ask for it. Changes committed by other sessions are one reason the order can move; see how transactions are committed.

Sources

1. 7.5. Sorting Rows (ORDER BY), PostgreSQL documentation — PostgreSQL Global Development Group, 13 Aug 2026
2. SELECT, SQLite query language — SQLite, 26 Oct 2026
3. SELECT, PostgreSQL reference — PostgreSQL Global Development Group, 13 Aug 2026
4. SELECT - ORDER BY clause (Transact-SQL), Microsoft Learn — Microsoft, 26 Oct 2026
5. 7.6. LIMIT and OFFSET, PostgreSQL documentation — PostgreSQL Global Development Group, 26 Oct 2026