ORDER BY in SQL: Ascending, Descending, and Multi-Column Sorting

A WHERE clause decides which rows come back, but says nothing about what order they appear in — and by default, that order isn't guaranteed at all. ORDER BY is SQL's dedicated sorting clause, giving precise control over how results are arranged, whether by a single column or by several columns in priority order.

What Is ORDER BY?

ORDER BY is a clause added to a SELECT statement that sorts the result set by one or more specified columns. Sorting can be ascending (ASC, the default) or descending (DESC), and multiple columns can be combined to define a precise, layered sort priority.

What You'll Learn

• Sort query results in ascending and descending order.
• Sort by multiple columns with correctly layered priority.
• Use column aliases and position numbers within ORDER BY.
• Understand how NULL values are positioned during sorting.

Key Terms to Know

• ORDER BY: A clause that sorts a query's result set by one or more columns.
• ASC: Ascending sort order (smallest/earliest first), the default if not specified.
• DESC: Descending sort order (largest/latest first).
• Sort priority: When multiple columns are listed in ORDER BY, the first column determines primary order, with later columns breaking ties.

Basic Ascending and Descending Sorting

SELECT product_name, price FROM products ORDER BY price; sorts results by price from lowest to highest, since ASC is the implicit default when no direction is specified. Adding DESC explicitly, as in ORDER BY price DESC;, reverses that to highest-to-lowest.

Text columns sort alphabetically by default (technically, according to the column's collation rules), so ORDER BY full_name; arranges names from A to Z, while ORDER BY full_name DESC; arranges them Z to A.

Multi-Column Sorting and Priority

ORDER BY category, price DESC; sorts primarily by category (alphabetically, ascending by default), and within each category group, sorts by price from highest to lowest. The first listed column establishes the primary sort order; each subsequent column only matters for breaking ties when the previous column's value is identical between rows.

Each column in a multi-column ORDER BY can independently specify ASC or DESC — there's no requirement that all columns share the same direction, which is exactly what allows category ASC combined with price DESC in the same clause.

Sorting by Alias, Position, and NULL Placement

ORDER BY can reference a column alias defined in the SELECT list, such as SELECT price * quantity AS line_total FROM order_items ORDER BY line_total DESC;, sorting by the computed value directly. MySQL also supports sorting by column position number, like ORDER BY 2 DESC;, referring to the second column in the SELECT list, though using actual column names or aliases is generally clearer.

By default in MySQL, NULL values are treated as the smallest possible value during sorting, so they appear first under ASC and last under DESC, unless explicitly handled differently using an expression.

Visual Summary

Picture a deck of cards being sorted first by suit, then within each suit by rank. ORDER BY suit, rank works exactly the same way: cards are grouped by suit first (the primary sort), and only within an identical suit does rank decide their relative order (the tie-breaking secondary sort) — switching the order of the two columns in ORDER BY would produce a meaningfully different overall arrangement.

ORDER BY Variations

Clause	Effect
ORDER BY price	Ascending by price (default direction)
ORDER BY price DESC	Descending by price, highest first
ORDER BY category, price DESC	Primary: category ASC; tie-break: price DESC
ORDER BY line_total DESC (alias)	Sorts by a computed/aliased column

SQL Example

-- Ascending by price (default)
SELECT product_name, price FROM products ORDER BY price;

-- Descending by price
SELECT product_name, price FROM products ORDER BY price DESC;

-- Multi-column: category ascending, then price descending within each category
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

-- Sorting by a computed alias
SELECT product_name, price, stock_qty, (price * stock_qty) AS inventory_value
FROM products
ORDER BY inventory_value DESC;

The first two queries show basic ascending and descending price sorting. The third demonstrates true multi-column priority: products are grouped alphabetically by category first, and within each identical category, the most expensive items appear first. The fourth sorts by a value computed entirely within the SELECT statement, showing ORDER BY's compatibility with aliases.

Real-World Examples

• E-commerce 'sort by price: low to high / high to low' filters map directly onto ORDER BY price ASC/DESC.
• Leaderboard features use ORDER BY score DESC to rank players from highest to lowest score.
• Inventory reports use multi-column ORDER BY category, stock_qty ASC to spot low-stock items grouped logically by category.
• Customer lists in admin panels commonly use ORDER BY last_name, first_name for predictable alphabetical sorting.

Best Practices and Pro Tips

• Always pair ORDER BY with explicit ASC or DESC when the direction matters for clarity, even though ASC is the default — it removes any doubt for future readers of the query.
• When sorting matters for pagination (combined with LIMIT/OFFSET, covered in the next lesson), always include a unique tie-breaking column in ORDER BY to guarantee a consistent, repeatable row order across pages.
• Prefer sorting by actual column names or aliases over positional numbers (ORDER BY 2) for long-term query readability and resilience to SELECT list changes.

Common Mistakes to Avoid

• Assuming query results come back in a predictable order without ORDER BY — without it, row order is not guaranteed and can change between runs.
• Forgetting that each column in a multi-column ORDER BY can have its own independent ASC/DESC direction, and mistakenly assuming one DESC applies to the whole list.
• Using ORDER BY with column position numbers in a way that silently breaks if the SELECT list's column order changes later.
• Not accounting for how NULL values are positioned by default when a sorted column might contain them, leading to confusing-looking results at the top or bottom of a list.

Interview Questions

Q1. What is the default sort direction in SQL if ASC or DESC isn't specified?

Ascending (ASC) is the default direction in MySQL, meaning the smallest or earliest values appear first, unless DESC is explicitly specified.

Q2. In ORDER BY category, price DESC, what determines the primary sort order?

category is the primary sort column, sorted ascending by default since no direction was specified for it. price DESC only comes into play as a tie-breaker for rows that share the exact same category value.

Q3. Can different columns in the same ORDER BY clause use different sort directions?

Yes, each column listed in ORDER BY can independently specify ASC or DESC, allowing combinations like ORDER BY category ASC, price DESC in a single clause.

Q4. Without an ORDER BY clause, is the order of returned rows guaranteed?

No, without ORDER BY, MySQL does not guarantee any particular row order, and the order can vary between executions, especially as data changes or the query optimizer chooses different execution plans.

Practice MCQs

1. Which clause sorts products by price from highest to lowest?

1. ORDER BY price
2. ORDER BY price ASC
3. ORDER BY price DESC
4. GROUP BY price

Answer: C. ORDER BY price DESC

Explanation: DESC explicitly sorts in descending order, placing the highest values first.

2. In ORDER BY category, price DESC, which column acts as the tie-breaker?

1. category
2. price
3. Both equally
4. Neither, they're independent

Answer: B. price

Explanation: category establishes the primary sort order; price DESC only resolves ties between rows that share the same category value.

3. What is the default sort direction in MySQL's ORDER BY if not specified?

1. DESC
2. ASC
3. Random
4. Depends on data type

Answer: B. ASC

Explanation: Ascending (ASC) order is the implicit default whenever a direction isn't explicitly stated in ORDER BY.

Quick Revision Points

• ASC is the default sort direction; DESC must be specified explicitly for descending order.
• Multi-column ORDER BY uses the first column as primary sort, later columns as tie-breakers.
• Each column in ORDER BY can have its own independent direction.
• Without ORDER BY, row order is not guaranteed.

Conclusion

• ORDER BY is the only reliable way to guarantee a specific row order in query results.
• Multi-column sorting with mixed directions models real-world sorting needs precisely.
• Pairing ORDER BY with a unique tie-breaker matters especially once pagination is involved.

ORDER BY gives SQL precise control over result ordering, supporting both simple single-column sorts and layered multi-column priority sorting with independently chosen ASC/DESC directions per column. Combined with column aliases and an understanding of default NULL placement, it's a small clause with outsized day-to-day importance. The next lesson covers LIMIT and OFFSET, which work hand-in-hand with ORDER BY to implement pagination.