ORDER BY Clause in MySQL | Sorting Data in Ascending and Descending Order

When data is retrieved from a database, the records are not always displayed in the required order. SQL provides the ORDER BY clause to arrange records in ascending or descending order. Sorting data makes reports easier to read, analyze, and interpret.

The ORDER BY clause is commonly used in schools, banks, hospitals, shopping websites, and business applications to display records alphabetically, numerically, or chronologically.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of the ORDER BY clause.
• Sort records in ascending order.
• Sort records in descending order.
• Sort data using one or more columns.
• Apply ORDER BY in real-life SQL queries.

---

Sample Student Table

Roll_No	Name	Class	Marks	City
101	Aarav	XI	91	Jaipur
102	Diya	XI	84	Delhi
103	Kabir	XII	95	Mumbai
104	Riya	XII	78	Ajmer
105	Vivaan	XI	88	Udaipur

---

What is ORDER BY?

---

Syntax

SELECT column_name
FROM table_name
ORDER BY column_name;

---

Ascending Order (ASC)

The ASC keyword arranges data from the smallest to the largest value or from A to Z. It is the default sorting order in SQL.

Syntax

SELECT *
FROM Student
ORDER BY Marks ASC;

Result

Name	Marks
Riya	78
Diya	84
Vivaan	88
Aarav	91
Kabir	95

---

Descending Order (DESC)

The DESC keyword arranges records from the largest to the smallest value or from Z to A.

Syntax

SELECT *
FROM Student
ORDER BY Marks DESC;

Result

Name	Marks
Kabir	95
Aarav	91
Vivaan	88
Diya	84
Riya	78

---

Sorting Alphabetically

The ORDER BY clause can also arrange text values alphabetically.

SELECT *
FROM Student
ORDER BY Name;

The records are displayed from A to Z based on the Name column.

---

Sorting Using Multiple Columns

You can sort records using more than one column. SQL first sorts using the first column and then sorts records having the same value using the next column.

Syntax

SELECT *
FROM Student
ORDER BY Class ASC, Marks DESC;

In this query, students are first grouped according to Class. Within each class, they are arranged according to Marks in descending order.

---

---

Examples of ORDER BY Clause

Example 1: Sort Students by Roll Number

SELECT *
FROM Student
ORDER BY Roll_No;

Displays all student records in ascending order of their Roll Numbers.

---

Example 2: Sort Students by Marks (Highest to Lowest)

SELECT Name, Marks
FROM Student
ORDER BY Marks DESC;

Displays the names of students starting from the highest scorer.

---

Example 3: Sort Students Alphabetically

SELECT *
FROM Student
ORDER BY Name ASC;

Displays student names in alphabetical order from A to Z.

---

Example 4: Sort Cities in Reverse Alphabetical Order

SELECT Name, City
FROM Student
ORDER BY City DESC;

Displays cities from Z to A.

---

Example 5: Sort Using Multiple Columns

SELECT *
FROM Student
ORDER BY Class ASC, Marks DESC;

Students are first arranged according to their class. Within each class, students are sorted according to marks in descending order.

---

Comparison of ASC and DESC

Feature	ASC	DESC
Full Form	Ascending	Descending
Numbers	Smallest to Largest	Largest to Smallest
Text	A to Z	Z to A
Default Order	Yes	No
Example	ORDER BY Marks ASC	ORDER BY Marks DESC

---

Comparison of Single and Multiple Column Sorting

Single Column Sorting	Multiple Column Sorting
Sorts using only one column.	Sorts using two or more columns.
Easy to understand.	Useful when multiple records have the same value.
Example: ORDER BY Marks	Example: ORDER BY Class, Marks DESC

---

Solved Example 1

Display all students arranged according to their names.

Solution:

SELECT *
FROM Student
ORDER BY Name;

---

Solved Example 2

Display students according to their marks from highest to lowest.

Solution:

SELECT *
FROM Student
ORDER BY Marks DESC;

---

Solved Example 3

Display Roll Number and Name sorted according to Roll Number.

Solution:

SELECT Roll_No, Name
FROM Student
ORDER BY Roll_No;

---

Solved Example 4

Arrange students first according to Class and then according to Name.

Solution:

SELECT *
FROM Student
ORDER BY Class ASC, Name ASC;

---

Solved Example 5

Arrange students according to City in reverse alphabetical order.

Solution:

SELECT *
FROM Student
ORDER BY City DESC;

---

Real-Life Applications

Organization	Purpose	Example Query
School	Display merit list.	ORDER BY Marks DESC
Bank	Sort customers by account balance.	ORDER BY Balance DESC
Hospital	Arrange patients alphabetically.	ORDER BY Patient_Name
Library	Display books by title.	ORDER BY Book_Name
Online Store	Sort products by price.	ORDER BY Price ASC

---

Common Errors

Mistake	Correct Practice
Using ORDER instead of ORDER BY.	Always write ORDER BY.
Misspelling column names.	Use the exact column names from the table.
Using ASCENDING or DESCENDING.	Use only ASC or DESC.
Forgetting commas while sorting by multiple columns.	Separate column names using commas.
Expecting DESC as default.	The default sorting order is ASC.

---

Interview Corner

---

---

Competency-Based Questions

1. A school wants to prepare a merit list of students in descending order of marks. Write the SQL query.
2. Write an SQL query to display all students arranged alphabetically by their names.
3. A library wants to display books in ascending order of their prices. Write the SQL query.
4. Differentiate between ASC and DESC with suitable examples.
5. Write an SQL query to display students first according to their class and then according to their names.

---

Multiple Choice Questions

1. The ORDER BY clause is used to:
   • (a) Delete records
   • (b) Arrange records in a specified order
   • (c) Update records
   • (d) Create tables
2. Which keyword sorts records in descending order?
   • (a) DOWN
   • (b) DESC
   • (c) REVERSE
   • (d) SORT
3. Which keyword represents ascending order?
   • (a) ASC
   • (b) DESC
   • (c) UP
   • (d) HIGH
4. What is the default sorting order in SQL?
   • (a) ASC
   • (b) DESC
   • (c) RANDOM
   • (d) NONE
5. Which query displays students according to marks from highest to lowest?
   • (a) SELECT * FROM Student ORDER BY Marks;
   • (b) SELECT * FROM Student ORDER BY Marks DESC;
   • (c) SELECT * FROM Student SORT Marks;
   • (d) SELECT * FROM Student DESC Marks;
6. Which clause allows sorting using more than one column?
   • (a) ORDER BY column1, column2
   • (b) GROUP BY
   • (c) WHERE
   • (d) HAVING
7. Which SQL statement arranges names alphabetically?
   • (a) SELECT * FROM Student ORDER BY Name;
   • (b) SELECT Name FROM Student SORT;
   • (c) SELECT * FROM Student GROUP BY Name;
   • (d) SELECT * FROM Student WHERE Name;
8. Which keyword is mandatory when sorting from highest to lowest?
   • (a) ASC
   • (b) DESC
   • (c) ORDER
   • (d) SORT
9. When sorting by multiple columns, the column names are separated using:
   • (a) Semicolon (;)
   • (b) Comma (,)
   • (c) Colon (:)
   • (d) Slash (/)
10. Which SQL query arranges students first by Class and then by Marks in descending order?
    • (a) SELECT * FROM Student ORDER BY Class ASC, Marks DESC;
    • (b) SELECT * FROM Student ORDER Class Marks;
    • (c) SELECT * FROM Student SORT BY Class, Marks;
    • (d) SELECT * FROM Student WHERE Class, Marks;

---

Quick Revision

Clause / Keyword	Purpose
ORDER BY	Sorts the records retrieved by the SELECT statement.
ASC	Sorts data in ascending order (default).
DESC	Sorts data in descending order.
Single Column	Sorts records using one column.
Multiple Columns	Sorts records using two or more columns.

---

Important Points to Remember

• The ORDER BY clause is used with the SELECT statement to sort records.
• Ascending order (ASC) is the default sorting order in SQL.
• Descending order requires the DESC keyword.
• Numbers are sorted from smallest to largest in ASC and largest to smallest in DESC.
• Text values are sorted alphabetically (A–Z) in ASC and reverse alphabetically (Z–A) in DESC.
• Multiple columns can be used for sorting by separating them with commas.
• SQL sorts records using the first column first. If duplicate values exist, it uses the next specified column.
• The column used for sorting does not necessarily have to appear in the SELECT list.
• The ORDER BY clause is generally written as the last clause in a SELECT statement.

---

CBSE Exam Tips

• Remember that ASC is the default order, so writing it is optional.
• Use DESC explicitly whenever descending order is required.
• Practice writing queries that sort data using both single and multiple columns.
• Read the question carefully to determine whether alphabetical, numerical, ascending, or descending sorting is required.
• Do not confuse the ORDER BY clause with the WHERE clause. WHERE filters records, whereas ORDER BY arranges them.
• Revise the syntax thoroughly, as syntax-based questions are common in CBSE examinations.

---

Summary

The ORDER BY clause is used to arrange records returned by the SELECT statement in a meaningful order. By default, SQL sorts data in ascending (ASC) order, while the DESC keyword is used for descending order. Records can be sorted using one or multiple columns, making reports easier to understand and analyze. The ORDER BY clause is widely used in real-world database applications such as student result preparation, customer reports, inventory management, and financial records. A clear understanding of ORDER BY is essential for writing efficient SQL queries and scoring well in the CBSE Class 11 Informatics Practices examination.