SQL ORDER BY: Ascending and Descending Explained Completely

After this guide, you’ll be able to sort any query result exactly the way you want it — smallest to largest, newest to oldest, alphabetically or reverse-alphabetically, by more than one column at once, and with full control over where missing values (NULLs) land in that order. That’s the whole scope of ORDER BY, and it’s smaller than most tutorials make it feel. What trips people up isn’t the concept; it’s a handful of specific edge cases that rarely get explained clearly. This post works through those questions one at a time.

What does ORDER BY actually do to my query results?

ORDER BY controls the sequence in which rows appear in your result set. Without it, a database makes no promise about row order at all — even if your data happens to come back sorted by insertion date most of the time, that’s an accident of how the engine chose to execute the query that day, not a guarantee. Add or remove an index, upgrade your database version, or change the query plan slightly, and that “accidental” order can shift without warning.

ORDER BY removes that uncertainty. You name a column (or several), and the database sorts every row in your result set according to that column before handing the results back to you. It’s the last clause the database logically applies, after filtering with WHERE, after grouping with GROUP BY, and after any HAVING conditions — so by the time sorting happens, you’re arranging the final shape of the output, not influencing what makes it into that output in the first place.

How do I sort in ascending order versus descending order?

Ascending order is the default. If you write ORDER BY sale_amount, the database sorts from smallest to largest with no extra keyword required. Numbers go low to high, dates go earliest to latest, and text goes A to Z.

Descending order needs one explicit word: DESC. Write ORDER BY sale_amount DESC and the same column now sorts largest to smallest instead. You can also write ASC explicitly for ascending order, but since it’s the default behavior, most SQL writers only bother typing it when they want to be unambiguous in a query someone else will read later.

A useful way to remember which is which: ascending “climbs” upward from low to high, the way a staircase ascends. Descending does the opposite — it descends from high to low. If you ever second-guess yourself mid-query, picture the staircase rather than trying to recall the word definitions cold.

Can I sort by more than one column at the same time?

Yes, and this is where ORDER BY starts doing real work. List multiple columns separated by commas, and the database sorts by the first column, then uses the second column only to break ties within groups of rows that shared the same value in the first column.

Picture a table of employees with a department column and a salary column. Writing ORDER BY department, salary DESC sorts all rows first by department alphabetically, and within each department, sorts salaries from highest to lowest. Every department’s employees cluster together, and inside that cluster, the highest earner appears first.

Each column in that list can carry its own direction independently. ORDER BY department ASC, salary DESC sorts departments alphabetically while sorting salaries within each department in reverse — nothing forces every column in a multi-column sort to share the same direction.

What happens to NULL values when I sort a column that contains them?

This is the part most explanations skip, and it causes real confusion the first time someone hits it. NULL represents a missing or unknown value, and different database systems place NULLs in different spots by default when you sort.

PostgreSQL and Oracle put NULLs last by default in an ascending sort, and first by default in a descending sort. MySQL and SQL Server, on the other hand, treat NULLs as the lowest possible value — so they show up first in an ascending sort and last in a descending one. Run the identical query against two different database engines and you can get a different arrangement of NULL rows purely because of this default, with no bug in your query at all.

If your query’s correctness depends on where NULLs land, don’t rely on the default. Most systems that follow the SQL standard support NULLS FIRST or NULLS LAST appended directly to your ORDER BY clause — for example, ORDER BY sale_amount DESC NULLS LAST, which keeps missing sales figures out of the way at the bottom regardless of what the engine would have done on its own. MySQL lacks this exact syntax, but the same effect is achievable with an expression like ORDER BY sale_amount IS NULL, sale_amount DESC, which sorts non-NULL rows first, in order, before letting NULLs settle at the end.

Can I sort by a column that isn’t even in my SELECT list?

Usually, yes. Most databases allow you to sort by any column in the underlying table, even one you haven’t selected for display. A query like SELECT name FROM employees ORDER BY hire_date DESC runs fine in most systems: you see only names in the output, but they’re arranged by hire date behind the scenes.

There’s one notable exception. If your query uses DISTINCT, some databases — PostgreSQL among them — require that any column named in ORDER BY also appear in the SELECT list. The reasoning is that DISTINCT can only guarantee a consistent, well-defined result once every value used to determine row order is also part of what defines a row’s uniqueness. Hit an error along these lines and the fix is almost always to add the missing column to your SELECT list.

Can I sort using a calculated value instead of a plain column?

Yes, and this is one of ORDER BY’s more underused capabilities. You can sort by an expression — a calculation, a function result, or even a CASE statement — rather than a raw column pulled straight from a table.

Say you want to sort products by profit margin, but margin isn’t a stored column; it’s calculated from price and cost. Write ORDER BY (price - cost) DESC and the database computes that subtraction for every row before sorting by the result, with no need to store the calculation anywhere first.

CASE statements inside ORDER BY unlock custom sort orders that don’t map to plain alphabetical or numeric sequence. Suppose a status column holds values like “Urgent,” “Normal,” and “Low,” and you want Urgent rows first regardless of alphabetical order — sorted alphabetically, “Low” would beat “Urgent,” which isn’t what you want. A CASE expression inside ORDER BY (assigning Urgent the number 1, Normal the number 2, Low the number 3, then sorting by that number) lets you define exactly the sequence you need, independent of how the text itself would naturally sort.

Can I sort by column position instead of column name?

You can, using a shorthand where ORDER BY 2 sorts by whichever column is second in your SELECT list, ORDER BY 1 sorts by the first, and so on. It works, and it’s slightly shorter to type than the column name.

It’s also fragile in a way that tends to cause quiet bugs. If someone reorders the SELECT list later — adding a new column near the front, say — the numeric reference in ORDER BY silently starts pointing at a different column, and the query keeps running without any error message to flag the change. For anything beyond a quick one-off query typed directly into a console, naming the column explicitly is worth the few extra characters.

Does ORDER BY slow down my query?

Sorting has a real cost, and it grows with the number of rows involved. For a small result set, that cost is negligible — you won’t notice it. For a large table sorted without any supporting index, the database may need to load a substantial chunk of matching rows into memory (or temporary disk space) purely to arrange them, and that work sits on top of whatever the WHERE clause and any joins already cost.

An index on the column you’re sorting by can let the database read rows in close to the order you want directly off the index, skipping a separate sort step entirely. This matters most on large tables where the same ORDER BY pattern runs repeatedly — a “most recent transactions first” query hit constantly by an application, for instance, is a strong candidate for an index on the date column driving that sort. For a report you run once a month, the extra sort cost is rarely worth the effort of maintaining a dedicated index just for it.

Where does ORDER BY belong relative to LIMIT, WHERE, and GROUP BY?

Written order in a query is WHERE, then GROUP BY, then HAVING, then ORDER BY, then LIMIT (or its equivalent — TOP, FETCH FIRST, depending on the database). ORDER BY comes right before any row-limiting clause, and that positioning isn’t arbitrary.

Combining ORDER BY with LIMIT is how you answer “top N” questions — the five highest sales, the ten most recent signups, the three lowest-scoring products. Sort first, in whichever direction surfaces what you want at the top, then limit to however many rows you need. Get the ORDER BY direction backward and LIMIT will confidently hand you the bottom of the list instead of the top, with no error to warn you — a mistake that’s easy to make and easy to miss until someone questions the numbers in a report.

Quick Reference

Which part of your own query is giving you trouble right now — getting NULLs to land where you want them, sorting by more than one column correctly, or something with a calculated sort order? Describe what you’re seeing and I can help you work out the exact ORDER BY clause for it.