Sorting Data with ORDER BY0%

Sorting Data with ORDER BY

Beginner12 min read•Updated: 2026-09-12

Sorting Data with ORDER BY: Ascending & Descending Sequences

In relational database theory, tables are defined as unordered mathematical sets. When you execute a basic SELECT query without an explicit sorting instruction, the database engine returns rows in arbitrary order (typically corresponding to the physical sequence of disk blocks or index order). To guarantee a predictable, deterministic sequence of records, you must use the ORDER BY clause.

1. Syntax of the `ORDER BY` Clause

The ORDER BY clause is specified toward the end of your query (after FROM and WHERE):

1SELECT column_1, column_2
2FROM table_name
3[WHERE condition]
4ORDER BY sort_column [ASC | DESC];

Sorting Directions:

ASC (Ascending): Smallest to largest (e.g., 1 to 100, A to Z, oldest date to newest). This is the default direction if omitted.
DESC (Descending): Largest to smallest (e.g., 100 to 1, Z to A, newest date to oldest).

1-- Sort employees by salary ascending (lowest to highest):
2SELECT full_name, salary FROM employees ORDER BY salary ASC;
3-- Equivalent (ASC is default):
4SELECT full_name, salary FROM employees ORDER BY salary;
5 
6-- Sort products by price descending (most expensive first):
7SELECT product_name, price FROM products ORDER BY price DESC;

2. Sorting by Column Aliases & Positional Indexes

Unlike the WHERE clause (which executes before SELECT), the ORDER BY clause executes AFTER SELECT in the logical query lifecycle. This means you can sort by column aliases defined in your SELECT statement!

1-- Sorting by a computed column alias:
2SELECT 
3    product_name,
4    unit_price * stock_quantity AS total_inventory_value
5FROM products
6ORDER BY total_inventory_value DESC;
7 
8-- Sorting by positional index number (1 = first column, 2 = second column):
9-- (Supported, but discouraged in production for readability):
10SELECT product_name, price FROM products ORDER BY 2 DESC;

3. Sorting Text and Collations

When sorting character strings, the sorting order depends on the column's Collation:

In utf8mb4_0900_ai_ci (case-insensitive), 'apple' and 'Apple' are considered identical in sort weight.
In utf8mb4_bin (binary), uppercase letters (ASCII 65-90) sort before lowercase letters (ASCII 97-122).

4. How NULL Values are Sorted in MySQL

In ASC order, MySQL places NULL values at the top (treated as smaller than any value).
In DESC order, MySQL places NULL values at the bottom.

1-- Force NULL values to the bottom during an ASC sort:
2SELECT full_name, commission_pct
3FROM sales_reps
4ORDER BY commission_pct IS NULL ASC, commission_pct ASC;

5. Best Practices & Common Pitfalls

Never Assume Physical Order: Never rely on default insertion order without an explicit ORDER BY. As soon as rows are updated, deleted, or indexes are rebuilt, unordered SELECT output order will change unpredictably.
Index Your Sort Columns: Sorting unindexed columns on tables with 500,000+ rows forces the database to perform an expensive in-memory or disk-based Filesort algorithm, causing high CPU spikes.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the default sorting direction if neither ASC nor DESC is explicitly specified in an ORDER BY clause?

Question 2

In what logical order of operations is the ORDER BY clause evaluated relative to the SELECT clause?

Question 3

How does MySQL place NULL values when executing ORDER BY score DESC?

Question 4

Which query sorts products with the most expensive items listed first?

Question 5

Why can sorting large unindexed tables degrade database server performance?

Next Lesson

Multi-Column Sorting

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Handling NULL Values (IS NULL)	Multi-Column Sorting

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Multi-Column Sorting

Sorting Data with ORDER BY

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Sorting Data with ORDER BY: Ascending & Descending Sequences

1. Syntax of the `ORDER BY` Clause

The ORDER BY clause is specified toward the end of your query (after FROM and WHERE):

1SELECT column_1, column_2
2FROM table_name
3[WHERE condition]
4ORDER BY sort_column [ASC | DESC];

Sorting Directions:

ASC (Ascending): Smallest to largest (e.g., 1 to 100, A to Z, oldest date to newest). This is the default direction if omitted.
DESC (Descending): Largest to smallest (e.g., 100 to 1, Z to A, newest date to oldest).

1-- Sort employees by salary ascending (lowest to highest):
2SELECT full_name, salary FROM employees ORDER BY salary ASC;
3-- Equivalent (ASC is default):
4SELECT full_name, salary FROM employees ORDER BY salary;
5 
6-- Sort products by price descending (most expensive first):
7SELECT product_name, price FROM products ORDER BY price DESC;

2. Sorting by Column Aliases & Positional Indexes

1-- Sorting by a computed column alias:
2SELECT 
3    product_name,
4    unit_price * stock_quantity AS total_inventory_value
5FROM products
6ORDER BY total_inventory_value DESC;
7 
8-- Sorting by positional index number (1 = first column, 2 = second column):
9-- (Supported, but discouraged in production for readability):
10SELECT product_name, price FROM products ORDER BY 2 DESC;

3. Sorting Text and Collations

When sorting character strings, the sorting order depends on the column's Collation:

In utf8mb4_0900_ai_ci (case-insensitive), 'apple' and 'Apple' are considered identical in sort weight.
In utf8mb4_bin (binary), uppercase letters (ASCII 65-90) sort before lowercase letters (ASCII 97-122).

4. How NULL Values are Sorted in MySQL

In ASC order, MySQL places NULL values at the top (treated as smaller than any value).
In DESC order, MySQL places NULL values at the bottom.

1-- Force NULL values to the bottom during an ASC sort:
2SELECT full_name, commission_pct
3FROM sales_reps
4ORDER BY commission_pct IS NULL ASC, commission_pct ASC;

5. Best Practices & Common Pitfalls

Never Assume Physical Order: Never rely on default insertion order without an explicit ORDER BY. As soon as rows are updated, deleted, or indexes are rebuilt, unordered SELECT output order will change unpredictably.
Index Your Sort Columns: Sorting unindexed columns on tables with 500,000+ rows forces the database to perform an expensive in-memory or disk-based Filesort algorithm, causing high CPU spikes.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the default sorting direction if neither ASC nor DESC is explicitly specified in an ORDER BY clause?

Question 2

In what logical order of operations is the ORDER BY clause evaluated relative to the SELECT clause?

Question 3

How does MySQL place NULL values when executing ORDER BY score DESC?

Question 4

Which query sorts products with the most expensive items listed first?

Question 5

Why can sorting large unindexed tables degrade database server performance?

Next Lesson

Multi-Column Sorting

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Handling NULL Values (IS NULL)	Multi-Column Sorting

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Sorting Data with ORDER BY: Ascending & Descending Sequences

1. Syntax of the ORDER BY Clause

Sorting Directions:

2. Sorting by Column Aliases & Positional Indexes

3. Sorting Text and Collations

4. How NULL Values are Sorted in MySQL

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Multi-Column Sorting

Related Lessons

Practice Quiz

Loading Page Content...

Sorting Data with ORDER BY: Ascending & Descending Sequences

1. Syntax of the ORDER BY Clause

Sorting Directions:

2. Sorting by Column Aliases & Positional Indexes

3. Sorting Text and Collations

4. How NULL Values are Sorted in MySQL

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Multi-Column Sorting

Related Lessons

Practice Quiz

1. Syntax of the `ORDER BY` Clause

1. Syntax of the `ORDER BY` Clause