Order By - Learning Module

Loading content...

0/252

ASC and DESC: Mastering Sort Direction Control

The Two Fundamental Directions of Order

Every sorted sequence has a direction. In databases, this direction manifests as ascending (ASC) or descending (DESC) order. While this concept seems trivially simple, mastering it involves understanding:

Default behaviors that vary subtly across data types and contexts
Implicit conversions that can surprise developers
Performance implications of direction on index utilization
Semantic meanings that align sort direction with business intent

SQL's ASC and DESC keywords give you explicit control over sort direction. The keyword placement, interaction with multiple columns, and combination with NULL handling create a rich space of ordering possibilities.

This page provides exhaustive coverage of sort direction—from basic mechanics to advanced optimization considerations—ensuring you can specify precisely the order your applications require.

What You Will Master

By the end of this page, you will understand the exact semantics of ASC and DESC for all data types, know when and how defaults apply, recognize the performance implications of reverse index scans, and be able to express any required order with precision.

Basic Semantics: ASC and DESC Defined

Ascending Order (ASC)

ASC specifies that results should be sorted from the smallest/lowest value to the largest/highest value. The concept of "smallest" depends on the data type:

Data Type	ASC Order (First → Last)
Numbers (INT, DECIMAL, FLOAT)	-∞ → 0 → +∞ (e.g., -100, -1, 0, 1, 100)
Strings (VARCHAR, CHAR)	Lexicographic order based on collation ('A' < 'B' < 'a' in ASCII-based)
Dates/Times	Earlier → Later (past → future)
Booleans	FALSE → TRUE (0 → 1 in most implementations)
Binary (BLOB, BYTEA)	Byte-by-byte comparison, lower bytes first

Descending Order (DESC)

DESC specifies that results should be sorted from the largest/highest value to the smallest/lowest value—the reverse of ASC:

Data Type	DESC Order (First → Last)
Numbers	+∞ → 0 → -∞ (e.g., 100, 1, 0, -1, -100)
Strings	Reverse lexicographic order ('z' before 'a', 'Z' before 'A')
Dates/Times	Later → Earlier (future → past)
Booleans	TRUE → FALSE
Binary	Higher bytes first

asc_desc_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Numeric ordering
SELECT product_id, price 
FROM products 
ORDER BY price ASC;
-- Result: 9.99, 19.99, 29.99, 49.99, 99.99
 
SELECT product_id, price 
FROM products 
ORDER BY price DESC;
-- Result: 99.99, 49.99, 29.99, 19.99, 9.99
 
-- Date ordering
SELECT order_id, order_date 
FROM orders 
ORDER BY order_date ASC;
-- Result: 2023-01-01, 2023-03-15, 2023-06-20 (oldest first)
 
SELECT order_id, order_date 
FROM orders 
ORDER BY order_date DESC;
-- Result: 2023-06-20, 2023-03-15, 2023-01-01 (newest first)
 
-- String ordering (ASCII/UTF-8 collation)
SELECT name FROM customers ORDER BY name ASC;
-- Result: Alice, Bob, Charlie, David, Zoe
 
SELECT name FROM customers ORDER BY name DESC;
-- Result: Zoe, David, Charlie, Bob, Alice

String Ordering Depends on Collation

The 'smallest' string isn't always intuitive. Collation settings determine whether 'a' < 'A', whether accented characters sort with their base letters, and how Unicode is handled. We'll explore collation in detail shortly.

Default Sort Direction: The Implicit ASC

When you specify an ORDER BY column without an explicit direction, SQL uses ASC by default. This is mandated by the SQL standard and implemented consistently across all major database systems.

default_asc.sql

-- These two queries are semantically identical:
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary ASC;
 
-- Both order from lowest salary to highest.
 
-- Similarly for multiple columns:
SELECT * FROM employees ORDER BY department_id, salary;
SELECT * FROM employees ORDER BY department_id ASC, salary ASC;
 
-- Both are equivalent: department_id ascending, then salary ascending.

When to Be Explicit vs. Implicit

Arguments for implicit ASC:

Less verbose, cleaner queries for ascending order
Well-understood convention among SQL developers
Reduces noise in simple queries

Arguments for explicit ASC:

Self-documenting code—intent is unmistakable
Easier to spot when modifying (changing ASC to DESC is obvious)
Consistency when mixing with DESC in multi-column sorts
Helps junior developers or code reviewers understand intent

Style Recommendation

In production code with multiple ORDER BY columns where directions are mixed, explicitly write ASC for clarity. In simple single-column ascending sorts, omitting ASC is acceptable and idiomatic. The key is consistency within your codebase.

Common Semantic Patterns

Different business scenarios naturally align with ascending or descending order:

Common Use Cases by Sort Direction
Use Case	Typical Direction	Rationale
Most recent records first	DESC on date/timestamp	Users expect newest content at the top
Alphabetical listing	ASC on name	A-Z is the natural expectation
Top performers	DESC on score/sales	Best results first
Lowest prices first	ASC on price	E-commerce price sorting default
Highest prices first	DESC on price	Luxury items or premium filtering
Priority queues	ASC on priority (if 1=highest)	Process most important first
Oldest unprocessed	ASC on created_at	FIFO processing order
Leaderboards	DESC on rank/points	Winners at the top

Mixing ASC and DESC in Multi-Column Sorts

One of ORDER BY's most powerful features is the ability to specify different directions for different columns. Each column in the ORDER BY list independently specifies its own direction.

Syntax and Semantics

mixed_directions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Sort by department ascending, but within each department,
-- sort by salary descending (highest paid first)
SELECT employee_id, department_id, name, salary
FROM employees
ORDER BY department_id ASC, salary DESC;
 
-- Result:
-- dept_id | name       | salary
-- --------|------------|--------
-- 1       | Alice      | 95000   (highest in dept 1)
-- 1       | Bob        | 75000
-- 1       | Carol      | 65000   (lowest in dept 1)
-- 2       | David      | 120000  (highest in dept 2)
-- 2       | Eve        | 85000
-- ... and so on

Complex Mixed Ordering Scenarios

complex_mixed_ordering.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- E-commerce: Sort by category (A-Z), 
-- then by rating (best first), 
-- then by price (lowest first)
SELECT product_id, category, rating, price, name
FROM products
ORDER BY 
    category ASC,      -- Grouping by category alphabetically
    rating DESC,       -- Best-rated products first
    price ASC;         -- Among equal ratings, cheapest first
 
-- Log analysis: Most recent errors first,
-- but within the same second, show by severity (critical first)
SELECT log_id, timestamp, severity, message
FROM error_logs
ORDER BY 
    timestamp DESC,
    CASE severity 
        WHEN 'CRITICAL' THEN 1
        WHEN 'ERROR' THEN 2
        WHEN 'WARNING' THEN 3
        WHEN 'INFO' THEN 4
    END ASC;
 
-- Leaderboard: Highest score first, 
-- ties broken by who achieved it first (earlier timestamp wins)
SELECT player_id, username, score, achieved_at
FROM high_scores
ORDER BY 
    score DESC,        -- Highest scores at top
    achieved_at ASC;   -- Earlier achievement breaks ties

Index Optimization with Mixed Directions

Mixed ASC/DESC directions can prevent index usage unless the index is specifically created with matching directions. For example, ORDER BY a ASC, b DESC benefits from an index on (a ASC, b DESC), but NOT from an index on (a, b) where both are ASC. We'll cover this in depth in the performance section.

Collation and String Sorting Order

When sorting strings, the concept of "less than" or "greater than" isn't as simple as with numbers. Collation rules determine how characters are compared, affecting:

Case sensitivity ('A' vs 'a')
Accent sensitivity ('e' vs 'é')
Special character handling
Language-specific rules (German ß, Spanish ñ)

Understanding Collation Impact

collation_effects.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Binary collation (ASCII byte-by-byte comparison)
SELECT name FROM users 
ORDER BY name COLLATE utf8mb4_bin;
-- Result: Apple, Banana, Zebra, apple, banana, zebra
-- Uppercase letters come before lowercase (A-Z = 65-90, a-z = 97-122)
 
-- Case-insensitive collation
SELECT name FROM users 
ORDER BY name COLLATE utf8mb4_general_ci;
-- Result: Apple, apple, Banana, banana, Zebra, zebra
-- Case-insensitive grouping, but uppercase typically first within ties
 
-- Unicode collation for proper linguistic ordering
SELECT name FROM users 
ORDER BY name COLLATE utf8mb4_unicode_ci;
-- Handles accents, special characters according to Unicode rules
 
-- PostgreSQL equivalent
SELECT name FROM users ORDER BY name COLLATE "en_US.utf8";
SELECT name FROM users ORDER BY name COLLATE "C";  -- Binary

Common Collation Scenarios

Collation Impact Examples
Collation Type	Sample Order	Use Case
Binary (C/bin)	A, B, Z, a, b, z	Machine processing, exact matching
Case-insensitive (_ci)	a, A, b, B, z, Z	User-facing alphabetical lists
Accent-insensitive (_ai)	cafe, café treated equal	Search, name matching
Locale-specific (de_DE)	ä sorts as ae	German phonebook ordering
Dictionary order	ä sorts after a	German dictionary ordering

collation_practical.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Check current database/column collation
-- MySQL
SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = DATABASE();
 
SHOW FULL COLUMNS FROM users;  -- Shows collation per column
 
-- PostgreSQL
SELECT datcollate FROM pg_database WHERE datname = current_database();
 
-- SQL Server
SELECT name, collation_name 
FROM sys.columns 
WHERE object_id = OBJECT_ID('users');
 
-- Override collation in ORDER BY (performance warning!)
SELECT name FROM users 
ORDER BY name COLLATE utf8mb4_unicode_ci;
-- This may prevent index usage if column has different collation

Collation and Performance

Using COLLATE in ORDER BY can prevent index utilization. For best performance, define column collation at table creation time to match your sorting needs, then indexes will respect that collation naturally.

Sort Direction and Index Optimization

The direction specified in ORDER BY directly impacts whether an index can be used to avoid sorting. Understanding this relationship is crucial for query performance.

B-Tree Index Ordering

B-tree indexes (the most common type) store entries in sorted order. By default:

A B-tree index on column X stores entries in ascending order of X
The index can be scanned forward (ASC) or backward (DESC)

However, there are subtleties with multi-column indexes and mixed directions.

index_direction.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Single column index: Forward and backward scans work
CREATE INDEX idx_price ON products(price);
 
-- These both benefit from the index:
SELECT * FROM products ORDER BY price ASC LIMIT 10;   -- Forward scan
SELECT * FROM products ORDER BY price DESC LIMIT 10;  -- Backward scan
 
-- Multi-column index: Directions must match or mirror
CREATE INDEX idx_dept_salary ON employees(department_id, salary);
 
-- This uses the index (forward scan):
SELECT * FROM employees ORDER BY department_id ASC, salary ASC;
 
-- This uses the index (backward scan on both):
SELECT * FROM employees ORDER BY department_id DESC, salary DESC;
 
-- This CANNOT use the index efficiently:
SELECT * FROM employees ORDER BY department_id ASC, salary DESC;
-- Mixed directions don't match the index order, requires filesort

Creating Indexes with Explicit Direction

explicit_direction_indexes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- PostgreSQL: Indexes with explicit direction
CREATE INDEX idx_mixed ON employees(department_id ASC, salary DESC);
 
-- Now this query can use the index:
SELECT * FROM employees ORDER BY department_id ASC, salary DESC;
 
-- SQL Server: Indexes with explicit direction
CREATE INDEX idx_mixed ON employees(department_id ASC, salary DESC);
 
-- MySQL 8.0+: Descending indexes supported
CREATE INDEX idx_mixed ON employees(department_id ASC, salary DESC);
 
-- MySQL 5.7 and earlier: DESC is parsed but ignored!
-- The index is always ascending regardless of syntax.
 
-- Oracle: Indexes with DESC
CREATE INDEX idx_mixed ON employees(department_id, salary DESC);

MySQL Version Caveat

Before MySQL 8.0, you cannot create truly descending indexes. The DESC keyword in CREATE INDEX is silently ignored. If you need mixed-direction index scans in MySQL 5.x, consider storing negated values or upgrading to MySQL 8.0+.

Reading Execution Plans for Sort Operations

explain_sort_direction.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- PostgreSQL: Check if sort is avoided
EXPLAIN SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;
 
-- Look for:
-- "Index Scan Backward" = Good! Using index in reverse
-- "Sort" = Bad! Explicit sort operation needed
 
-- MySQL: Check for filesort
EXPLAIN SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;
 
-- Look for:
-- "Using index" in Extra = Good!
-- "Using filesort" in Extra = Sort operation needed
 
-- SQL Server: Check execution plan
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM employees ORDER BY hire_date DESC;
GO
SET SHOWPLAN_TEXT OFF;
GO
-- Look for "Sort" operators (expensive) vs. "Index Scan" (ordered)

Index/ORDER BY Direction Rules

•Single-column index: Both ASC and DESC ORDER BY can use the index (forward/backward scan)
•Multi-column index (all same direction): ORDER BY must match index direction OR be complete reverse
•Multi-column index (mixed directions): ORDER BY must exactly match the index column directions
•Partial column match: ORDER BY can use index if columns are a prefix of the index columns
•With LIMIT: Top-N optimization works with either direction on single-column indexes

Special Cases and Edge Behaviors

Several edge cases in ASC/DESC ordering deserve explicit attention.

Boolean Ordering

Boolean values order as FALSE < TRUE in ascending order:

boolean_ordering.sql
1
2
3
4
5
6
7
8
9
-- PostgreSQL, MySQL (with BOOLEAN type)
SELECT name, is_active FROM users ORDER BY is_active ASC;
-- Result: Inactive users first (FALSE = 0), then active (TRUE = 1)
 
SELECT name, is_active FROM users ORDER BY is_active DESC;
-- Result: Active users first (TRUE = 1), then inactive (FALSE = 0)
 
-- Common pattern: Show active items first
SELECT * FROM products ORDER BY is_available DESC, name ASC;

Negative Numbers and Absolute Values

Numeric ordering with negatives follows mathematical order:

negative_ordering.sql
1
2
3
4
5
6
7
8
9
-- Transactions with debits (negative) and credits (positive)
SELECT transaction_id, amount FROM transactions ORDER BY amount ASC;
-- Result: -500, -100, -50, 0, 25, 100, 500
 
-- To order by absolute magnitude:
SELECT transaction_id, amount 
FROM transactions 
ORDER BY ABS(amount) DESC;
-- Result: 500, -500, 100, -100, 50, -50, 25, 0

Floating-Point Considerations

IEEE 754 floating-point ordering includes special values:

float_ordering.sql
1
2
3
4
5
6
7
8
9
10
11
12
-- Floating point special values ordering (PostgreSQL)
SELECT val FROM test_floats ORDER BY val ASC;
-- Order: -Infinity, negative numbers, -0, 0, positive numbers, +Infinity, NaN
 
-- NaN (Not a Number) has special behavior:
-- In PostgreSQL: NaN is greater than all other values including Infinity
-- In some systems: NaN comparisons are undefined
 
-- Be explicit about NaN handling if your data may contain it:
SELECT val FROM test_floats 
WHERE val IS NOT NULL AND val = val  -- NaN != NaN filters out NaN
ORDER BY val ASC;

Empty Strings vs. NULL

Empty strings and NULL are distinct in ordering:

empty_vs_null.sql
1
2
3
4
5
6
7
8
9
10
-- In most databases, empty string '' < 'A' in ASC order
-- But NULL has special handling (see next page)
 
SELECT name FROM users ORDER BY name ASC;
-- Could be: NULL, '', 'Alice', 'Bob' (NULL behavior varies by DBMS)
-- Or: '', 'Alice', 'Bob', NULL (depends on NULLS FIRST/LAST)
 
-- To treat empty strings as NULL for ordering:
SELECT name FROM users 
ORDER BY NULLIF(name, '') ASC;  -- '' becomes NULL, sorts accordingly

NULL Handling Is Its Own Topic

NULL ordering (NULLS FIRST, NULLS LAST, default behaviors) is complex enough to warrant its own page. We'll cover it extensively in Page 4.

Practical Guidelines and Summary

Let's consolidate the key principles for using ASC and DESC effectively.

Best Practices for Sort Direction

•Match business semantics — DESC for 'top/best/newest', ASC for 'first/oldest/lowest'
•Be explicit with mixed directions — Write 'column1 ASC, column2 DESC' instead of 'column1, column2 DESC' for clarity
•Consider index alignment — Create indexes that match your most common ORDER BY directions
•Remember default is ASC — Omitting direction is idiomatic for ascending, but be consistent
•Account for collation — String ordering depends on collation; test with representative data
•Handle NULLs explicitly — Use NULLS FIRST/LAST or COALESCE for predictable NULL positioning

•Match ORDER BY to existing indexes
•Use explicit direction for clarity
•Create mixed-direction indexes for common queries
•Test collation effects on string data
•Consider backward index scans for single-column DESC

DON'T

•Assume MySQL 5.x DESC indexes work
•Ignore collation differences across environments
•Use COLLATE in ORDER BY for performance-critical queries
•Mix directions without considering index support
•Forget about floating-point edge cases

Key Takeaways

ASC is the default — Omitting direction means ascending order
Each column specifies its own direction — Independent ASC/DESC per column
Direction affects index usability — Match ORDER BY direction to index definition
String ordering depends on collation — Binary vs. linguistic ordering varies significantly
Special values have defined ordering — Booleans, negatives, floats, empty strings all order predictably

Page Complete

You now have comprehensive knowledge of ASC and DESC semantics, default behaviors, performance implications, and edge cases. Next, we'll explore multi-column sorting strategies in depth—how SQL prioritizes columns and resolves ties.