SQL ORDER BY: Ascending and Descending Explained Completely

ORDER BY is genuinely one of the simpler clauses in SQL, and I do not want to artificially inflate its complexity. But there are still a handful of specific behaviors — particularly around NULL values and sorting by multiple columns — that consistently come up as questions even from students who otherwise feel confident with basic SQL, so this tutorial covers those specifics directly rather than just repeating the basic syntax.

The Basic Syntax

Adding ORDER BY at the end of your query, followed by a column name, sorts your results according to that column. By default, without specifying anything further, this sorts in ascending order — smallest to largest for numbers, earliest to latest for dates, and alphabetically from A to Z for text.

Adding the word DESC immediately after the column name reverses this into descending order instead — largest to smallest for numbers, latest to earliest for dates, and alphabetically from Z to A for text. Adding the word ASC explicitly is also valid syntax for ascending order, though since ascending is already the default behavior, most people simply omit it entirely and only write DESC when they specifically want the reversed descending order.

Sorting by Multiple Columns

You are not limited to sorting by just one single column. Listing multiple column names within your ORDER BY clause, separated by commas, sorts primarily by the first column listed, and only uses each subsequent column to break ties among rows that share an identical value in every column listed before it.

For example, ORDER BY listing a department column followed by a salary column sorts your results primarily by department (grouping all rows from the same department together), and within each department group, further sorts by salary. Any two rows from completely different departments will always be ordered according to their department first, regardless of their relative salary values, since department is the primary sort criterion listed first.

You can mix ascending and descending independently for different columns within the same ORDER BY clause. For example, sorting departments alphabetically (the default ascending order) while sorting salary from highest to lowest within each department requires adding DESC specifically after the salary column only, while leaving the department column without DESC, since you want that one to remain in its default ascending order.

How NULL Values Are Sorted

This is the specific behavior that most commonly surprises people, since it is not always immediately obvious and varies between different database systems.

In most database systems, NULL values are treated as either the lowest or highest possible value during sorting, though which one specifically depends on your particular database system’s default behavior. PostgreSQL, for example, sorts NULL values as if they were larger than any actual value by default in ascending order (meaning NULLs appear last in an ascending sort, and first in a descending sort). MySQL, by contrast, sorts NULL values as if they were smaller than any actual value by default (meaning NULLs appear first in an ascending sort, and last in a descending sort).

This difference between database systems is genuinely one of those details that can cause confusion or even subtle bugs if you assume identical behavior across different platforms, particularly if you are testing locally on one database system and then deploying queries against a different one in production.

Most database systems provide an explicit way to control NULL positioning directly, rather than relying on that system’s particular default behavior. PostgreSQL, for example, supports adding NULLS FIRST or NULLS LAST directly after your ORDER BY column specification, explicitly controlling where NULL values appear regardless of what the default behavior would otherwise produce. Not every database system supports this exact syntax, so checking your specific system’s documentation is worth doing if NULL positioning genuinely matters for your particular use case, rather than assuming the default behavior you might be used to from one system will carry over identically to another.

Sorting by a Column Not Included in Your SELECT

A detail that surprises some beginners: in most database systems, you can sort by a column that does not actually appear in your SELECT statement’s list of displayed columns, as long as that column does genuinely exist in the table (or tables, if you are joining) your query is selecting from.

For example, you could SELECT just a customer’s name and email, while still adding ORDER BY based on their signup date, even though signup date itself is not one of the two columns being displayed in your actual results. The sorting still applies correctly based on that underlying column’s actual values, even though the column itself remains invisible in your final displayed output.

One specific exception worth knowing: if your query includes a GROUP BY clause, the columns available for use in ORDER BY become more restricted, generally limited to columns that are either part of your GROUP BY clause itself, or are aggregate function results (SUM, COUNT, and so on) that are actually included in your SELECT statement, since sorting by some other raw column that exists outside the grouped, aggregated result no longer has a single consistent value per group to sort by.

Sorting by Column Position Instead of Column Name

A less commonly used, somewhat older syntax allows sorting by a column’s numeric position within your SELECT list, rather than by typing out its actual name. Writing ORDER BY followed simply by the number 2, for example, sorts by whichever column happens to be the second one listed in your SELECT statement, regardless of what that column’s actual name is.

I generally do not recommend this approach for queries you intend to keep or maintain over time, even though it technically works in most database systems, since it creates a fragile dependency on the exact order of your SELECT list. If someone later reorders or adds columns to that SELECT list, the ORDER BY clause silently starts sorting by an entirely different column than originally intended, without producing any error message to flag that the meaning has quietly changed. Writing out the actual column name explicitly avoids this entire fragility, at the minor cost of slightly more typing, and I consider that tradeoff clearly worth it for any query meant to be reused or maintained by anyone over any meaningful period of time.

Sorting Using a Calculated Expression or CASE WHEN

As covered in more detail in the CASE WHEN tutorial, you are not limited to sorting only by raw existing columns directly. You can sort by the result of a calculated expression, including a CASE WHEN expression that converts categorical values into a custom, business-logic-driven sort order that would not be achievable through standard alphabetical or numeric sorting alone.

This is particularly useful for status or priority columns, where the natural business-meaningful order (“Urgent” before “Pending” before “Completed”, for example) does not happen to match standard alphabetical order, and a direct ORDER BY on that raw text column alone would produce a technically valid but practically nonsensical sort sequence for that specific use case.

Performance Considerations With Large Result Sets

Sorting a genuinely large number of rows requires computational work, and on very large tables, an ORDER BY clause without any LIMIT can become a noticeably slower part of your overall query execution, particularly if the column you are sorting by does not already have a database index supporting efficient sorting.

For exploratory queries where you are just trying to understand your data, combining ORDER BY with a reasonably small LIMIT (covered in the first SELECT tutorial) is a practical habit that avoids unnecessarily sorting and then displaying far more rows than you actually need to look at in that specific moment, while still letting you see the most relevant top or bottom results according to whatever sort order you have specified.

For production queries that genuinely need to sort and display a very large number of rows regularly, working with whoever manages your specific database (or consulting its documentation directly) about whether an appropriate index exists on your sort column is worth doing once performance becomes a noticeable concern, though this moves beyond the scope of writing the query itself and into database administration and optimization, which is a separate, more advanced topic area.

A Quick Reference

The One Habit Worth Building

Whenever NULL values might exist in a column you are sorting by, and their position in your results genuinely matters for how that data will be read or used, explicitly checking (rather than assuming) how your specific database system handles NULL sorting by default is worth the few minutes it takes, given how this behavior genuinely differs across systems and has caused confusion for students moving between different database platforms in my own teaching experience.

Which column are you trying to sort by, and does that column potentially contain NULL values? Describe your situation and I can help you write the exact ORDER BY clause, including any NULL handling you might need.