ASC and DESC direction in MySQL - Step-by-Step Execution

Concept Flow - ASC and DESC direction

Start with unsorted data

↓

Apply ORDER BY clause

↓

Check sorting direction

↓

Sort data low->high

↓

Return sorted result

Data is sorted by a column using ORDER BY, then direction ASC sorts from low to high, DESC sorts from high to low.

Execution Sample

MySQL

SELECT name, age FROM people ORDER BY age ASC;
SELECT name, age FROM people ORDER BY age DESC;

These queries sort people by age in ascending and descending order respectively.

Execution Table

Step	Query	Data Before Sort	Sort Direction	Data After Sort
1	SELECT name, age FROM people ORDER BY age ASC;	[{"name": "Anna", "age": 30}, {"name": "Bob", "age": 25}, {"name": "Cara", "age": 35}]	ASC	[{"name": "Bob", "age": 25}, {"name": "Anna", "age": 30}, {"name": "Cara", "age": 35}]
2	SELECT name, age FROM people ORDER BY age DESC;	[{"name": "Anna", "age": 30}, {"name": "Bob", "age": 25}, {"name": "Cara", "age": 35}]	DESC	[{"name": "Cara", "age": 35}, {"name": "Anna", "age": 30}, {"name": "Bob", "age": 25}]
3	End of queries			Execution complete

💡 Queries finish after sorting data according to ASC or DESC direction.

Variable Tracker

Variable	Start	After Query 1	After Query 2	Final
people	[{"name": "Anna", "age": 30}, {"name": "Bob", "age": 25}, {"name": "Cara", "age": 35}]	[{"name": "Bob", "age": 25}, {"name": "Anna", "age": 30}, {"name": "Cara", "age": 35}]	[{"name": "Cara", "age": 35}, {"name": "Anna", "age": 30}, {"name": "Bob", "age": 25}]	No change to original data

Key Moments - 3 Insights

Why does ASC sort put smaller numbers first?

ASC means ascending order, so the database arranges values from smallest to largest as shown in execution_table row 1.

Does DESC change the original data order permanently?

No, DESC only changes the order of the result returned by the query, original data stays the same as shown in variable_tracker.

What happens if ORDER BY is omitted?

Without ORDER BY, the database returns data in any order, not guaranteed sorted, unlike rows 1 and 2 where sorting direction is applied.

Visual Quiz - 3 Questions

Look at the execution_table row 1, what is the first person in the sorted list for ASC?

A Bob

B Anna

C Cara

D Cannot tell

💡 Hint

Check the 'Data After Sort' column in row 1 for ASC sorting result.

At which step does the data get sorted from highest to lowest age?

A Step 3

B Step 1

C Step 2

D None

💡 Hint

Look at the 'Sort Direction' column in execution_table row 2.

If we remove ORDER BY, how would the variable_tracker change?

A Data after queries would be sorted ascending

B Data order would remain as original

C Data after queries would be sorted descending

D Data would be empty

💡 Hint

Without ORDER BY, the original data order stays unchanged as shown in variable_tracker.

Concept Snapshot

ORDER BY sorts query results by a column.
ASC means ascending order (smallest to largest).
DESC means descending order (largest to smallest).
Without ORDER BY, data order is not guaranteed.
Sorting affects only query output, not stored data.

Full Transcript

This lesson shows how the ORDER BY clause sorts data in SQL queries. When you use ORDER BY with ASC, the database arranges the results from smallest to largest value in the chosen column. Using DESC sorts from largest to smallest. The original data in the table does not change; only the order of the returned results changes. If you do not use ORDER BY, the database may return data in any order. The execution table shows two queries sorting a list of people by age ascending and descending. The variable tracker shows how the data order changes only in the query result, not the original data. Key moments clarify common confusions about sorting direction and data permanence. The quiz tests understanding of sorting results and effects of ORDER BY.

Practice

1. What does the ASC keyword do in a MySQL ORDER BY clause?

easy

A. Filters the data to show only ascending values

B. Sorts the data from largest to smallest or Z to A

C. Groups the data by ascending order

D. Sorts the data from smallest to largest or A to Z

Solution

1. Step 1: Understand the meaning of ASC in ORDER BY

   ASC stands for ascending order, which means sorting from smallest to largest or alphabetically from A to Z.

2. Step 2: Compare with other options

   DESC is for descending order, filtering and grouping are different SQL operations unrelated to ASC.

3. Final Answer:

   Sorts the data from smallest to largest or A to Z -> Option D

4. Quick Check:

   ASC = ascending order = smallest to largest [OK]

Hint: ASC means ascending order: smallest to largest [OK]

Common Mistakes:

• Confusing ASC with DESC
• Thinking ASC filters data
• Mixing sorting with grouping

2. Which of the following is the correct syntax to sort a table named employees by the salary column in descending order?

easy

A. SELECT * FROM employees ORDER BY salary DESC;

B. SELECT * FROM employees ORDER BY salary ASC;

C. SELECT * FROM employees ORDER salary DESC;

D. SELECT * FROM employees SORT BY salary DESC;

Solution

1. Step 1: Identify correct ORDER BY syntax

   The correct syntax to sort results is using ORDER BY followed by the column name and then ASC or DESC.

2. Step 2: Check each option

   SELECT * FROM employees ORDER BY salary ASC; uses ASC, not DESC. SELECT * FROM employees ORDER salary DESC; misses BY keyword. SELECT * FROM employees SORT BY salary DESC; uses SORT BY which is invalid in MySQL. SELECT * FROM employees ORDER BY salary DESC; is correct.

3. Final Answer:

   SELECT * FROM employees ORDER BY salary DESC; -> Option A

4. Quick Check:

   ORDER BY + column + DESC is correct syntax [OK]

Hint: Use ORDER BY column DESC to sort descending [OK]

Common Mistakes:

• Omitting BY after ORDER
• Using SORT BY instead of ORDER BY
• Mixing ASC and DESC keywords

3. Given the table products with column price, what will be the order of prices returned by this query?

SELECT price FROM products ORDER BY price DESC;

medium

A. Prices sorted randomly

B. Prices sorted from lowest to highest

C. Prices sorted from highest to lowest

D. Prices grouped by price value

Solution

1. Step 1: Analyze ORDER BY with DESC

   ORDER BY price DESC sorts the prices from the largest value to the smallest value.

2. Step 2: Eliminate other options

   Prices sorted from lowest to highest is ascending order, Prices sorted randomly is incorrect because ORDER BY sorts results, Prices grouped by price value is grouping which is unrelated.

3. Final Answer:

   Prices sorted from highest to lowest -> Option C

4. Quick Check:

   DESC means descending order = highest to lowest [OK]

Hint: DESC sorts from highest to lowest [OK]

Common Mistakes:

• Confusing DESC with ASC
• Expecting random order without ORDER BY
• Thinking ORDER BY groups data

4. Identify the error in this query that tries to sort students by age in descending order:

SELECT * FROM students ORDER age DESC;

medium

A. The table name students is invalid

B. Missing BY keyword after ORDER

C. ORDER BY should be replaced with SORT BY

D. DESC cannot be used with ORDER

Solution

1. Step 1: Check ORDER BY syntax

   The correct syntax requires ORDER BY, not just ORDER, before the column name.

2. Step 2: Validate other parts

   DESC is valid with ORDER BY. SORT BY is not valid in MySQL. Table name is valid as given.

3. Final Answer:

   Missing BY keyword after ORDER -> Option B

4. Quick Check:

   ORDER BY is required, not just ORDER [OK]

Hint: Always use ORDER BY, not just ORDER [OK]

Common Mistakes:

• Omitting BY after ORDER
• Using SORT BY instead of ORDER BY
• Misunderstanding DESC usage

5. You want to list all orders sorted first by order_date ascending, then by total_amount descending. Which query correctly achieves this?

hard

A. SELECT * FROM orders ORDER BY order_date ASC, total_amount DESC;

B. SELECT * FROM orders ORDER BY order_date DESC, total_amount ASC;

C. SELECT * FROM orders ORDER BY total_amount DESC, order_date ASC;

D. SELECT * FROM orders ORDER BY total_amount ASC, order_date DESC;

Solution

1. Step 1: Understand multi-column ORDER BY

   ORDER BY can sort by multiple columns in sequence: first column sorts first, then second column breaks ties.

2. Step 2: Match sorting directions

   We want order_date ascending first, then total_amount descending. SELECT * FROM orders ORDER BY order_date ASC, total_amount DESC; matches this order and directions exactly.

3. Final Answer:

   SELECT * FROM orders ORDER BY order_date ASC, total_amount DESC; -> Option A

4. Quick Check:

   First ASC on order_date, then DESC on total_amount [OK]

Hint: List columns in ORDER BY with directions in desired priority [OK]

Common Mistakes:

• Swapping column order in ORDER BY
• Mixing ASC and DESC directions incorrectly
• Using DESC when ASC is needed and vice versa