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

When data is retrieved from a database, the records are not always displayed in the desired order. The ORDER BY clause is used to sort the result of a query in ascending or descending order based on one or more columns. It makes reports easier to read, compare, and analyze.

For example, a school may want to display students according to their marks, arrange names alphabetically, or list fees from highest to lowest. All these tasks can be performed using the ORDER BY clause.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of the ORDER BY clause.
• Sort records in ascending and descending order.
• Sort records using multiple columns.
• Use ORDER BY with WHERE, GROUP BY, and HAVING clauses.
• Write practical SQL queries using ORDER BY.

---

What is ORDER BY?

---

Sample Student Table

Roll_No	Name	Class	Section	Marks	Fee
101	Aarav	XI	A	91	45000
102	Diya	XI	A	84	45000
103	Kabir	XI	B	88	45000
104	Riya	XII	A	95	48000
105	Vivaan	XII	B	82	48000
106	Ananya	XII	B	90	48000

---

Basic Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name ASC;

The keyword ASC is optional because MySQL sorts data in ascending order by default.

---

Ascending Order (ASC)

Ascending order arranges data from the smallest value to the largest value. For text values, it arranges data alphabetically from A to Z.

Example 1: Display Students According to Marks (Lowest to Highest)

SELECT *
FROM Student
ORDER BY Marks ASC;

Output

Name	Marks
Vivaan	82
Diya	84
Kabir	88
Ananya	90
Aarav	91
Riya	95

---

Descending Order (DESC)

Descending order arranges data from the largest value to the smallest value. For text values, it arranges data from Z to A.

Example 2: Display Students According to Marks (Highest to Lowest)

SELECT *
FROM Student
ORDER BY Marks DESC;

Output

Name	Marks
Riya	95
Aarav	91
Ananya	90
Kabir	88
Diya	84
Vivaan	82

---

Sorting Text Values

The ORDER BY clause can also sort text columns such as names, cities, or departments.

Example 3: Display Student Names Alphabetically

SELECT Name
FROM Student
ORDER BY Name;

---

Sorting Using Multiple Columns

You can sort records using more than one column. MySQL first sorts using the first column. If two or more records have the same value, it then sorts those records using the next column.

Example 4: Sort by Class and Then by Marks

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

In this query, students are first arranged class-wise. Within each class, students are arranged according to marks in descending order.

---

Comparison: ASC vs DESC

ASC	DESC
Ascending order	Descending order
Smallest to Largest	Largest to Smallest
A to Z (Text)	Z to A (Text)
Default sorting order	Must be specified using DESC

---

ORDER BY with WHERE Clause

The ORDER BY clause is often used with the WHERE clause to display filtered records in a specific order.

Example 5

SELECT *
FROM Student
WHERE Class='XI'
ORDER BY Marks DESC;

This query displays only Class XI students and arranges them from the highest to the lowest marks.

---

---

ORDER BY with GROUP BY

The ORDER BY clause is frequently used along with the GROUP BY clause to sort grouped results. After the records are grouped, the groups can be arranged in ascending or descending order.

Example 1: Display Number of Students in Each Class

Arrange the classes alphabetically.

SELECT Class,
       COUNT(*) AS Total_Students
FROM Student
GROUP BY Class
ORDER BY Class;

---

Example 2: Display Average Marks Class-wise

Arrange the classes according to their average marks in descending order.

SELECT Class,
       AVG(Marks) AS Average_Marks
FROM Student
GROUP BY Class
ORDER BY Average_Marks DESC;

---

ORDER BY with HAVING Clause

The ORDER BY clause can also be used after the HAVING clause to sort only those groups that satisfy a given condition.

Example 3

SELECT Class,
       AVG(Marks) AS Average_Marks
FROM Student
GROUP BY Class
HAVING AVG(Marks) > 85
ORDER BY Average_Marks DESC;

---

Sorting Using Multiple Columns

When multiple columns are specified, MySQL sorts the records according to the first column. If two or more records have the same value in the first column, the next column is used for sorting.

Example 4

Arrange students class-wise and, within each class, according to marks in descending order.

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

---

Sorting Numeric, Text and Date Values

Data Type	Ascending Order	Descending Order
Number	Smallest to Largest	Largest to Smallest
Text	A to Z	Z to A
Date	Oldest to Newest	Newest to Oldest

---

Solved Example 1

Display all students arranged alphabetically by their names.

Solution:

SELECT *
FROM Student
ORDER BY Name;

---

Solved Example 2

Display students according to marks from highest to lowest.

Solution:

SELECT *
FROM Student
ORDER BY Marks DESC;

---

Solved Example 3

Display students of Class XII arranged by their names.

Solution:

SELECT *
FROM Student
WHERE Class='XII'
ORDER BY Name;

---

Solved Example 4

Display the average marks of each class arranged from highest to lowest.

Solution:

SELECT Class,
AVG(Marks) AS Average_Marks
FROM Student
GROUP BY Class
ORDER BY Average_Marks DESC;

---

Solved Example 5

Display sections having an average marks greater than 85 and arrange them in descending order.

Solution:

SELECT Section,
AVG(Marks) AS Average_Marks
FROM Student
GROUP BY Section
HAVING AVG(Marks) > 85
ORDER BY Average_Marks DESC;

---

Real-Life Applications

Organization	Requirement	Example
School	Prepare merit list	ORDER BY Marks DESC
School	Arrange student names alphabetically	ORDER BY Name ASC
Library	Sort books by title	ORDER BY Title
Company	Arrange employees by salary	ORDER BY Salary DESC
Hospital	Arrange patients by admission date	ORDER BY Admission_Date DESC
Bank	Arrange customers by account balance	ORDER BY Balance DESC

---

Common Errors

Mistake	Correct Practice
Writing ORDER BY before WHERE.	Always write WHERE before ORDER BY.
Using DESC without ORDER BY.	DESC must always follow ORDER BY.
Misspelling ASC or DESC.	Use the correct keywords ASC and DESC.
Using commas incorrectly while sorting multiple columns.	Separate column names with commas.
Assuming DESC is the default order.	Ascending (ASC) is the default sorting order.

---

Interview Corner

---

---

Competency-Based Questions

1. A school wants to prepare a merit list by displaying students in descending order of marks. Write the SQL query.
2. Write an SQL query to display the names of all students in alphabetical order.
3. A company wants to display employees department-wise and, within each department, according to salary in descending order. Write the SQL query.
4. Differentiate between ASC and DESC with suitable examples.
5. Explain how the ORDER BY clause works when multiple columns are specified.

---

Multiple Choice Questions

1. The ORDER BY clause is used to:
   • (a) Delete records
   • (b) Sort records
   • (c) Create tables
   • (d) Update records
2. The default sorting order in MySQL is:
   • (a) ASC
   • (b) DESC
   • (c) RANDOM
   • (d) NONE
3. Which keyword is used to sort records in descending order?
   • (a) ASC
   • (b) DESC
   • (c) SORT
   • (d) ORDER
4. Which query displays student names alphabetically?
   • (a) SELECT * FROM Student ORDER Name;
   • (b) SELECT Name FROM Student ORDER BY Name;
   • (c) SELECT Name GROUP BY Name;
   • (d) SELECT Name SORT BY Name;
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 GROUP BY Marks;
   • (d) SELECT * FROM Student HAVING Marks DESC;
6. To sort data using more than one column, the column names are separated by:
   • (a) Semicolon (;)
   • (b) Comma (,)
   • (c) Colon (:)
   • (d) Slash (/)
7. Which clause is executed after the HAVING clause?
   • (a) ORDER BY
   • (b) WHERE
   • (c) GROUP BY
   • (d) SELECT
8. Which statement is correct?
   • (a) DESC is the default sorting order.
   • (b) ASC is the default sorting order.
   • (c) ORDER BY is compulsory in every SQL query.
   • (d) ORDER BY is used only with numeric columns.
9. Which SQL clause is mainly used to arrange grouped results?
   • (a) WHERE
   • (b) HAVING
   • (c) ORDER BY
   • (d) INSERT
10. Which of the following SQL clauses is generally written last in a SELECT query?
    • (a) WHERE
    • (b) GROUP BY
    • (c) HAVING
    • (d) ORDER BY

---

Quick Revision

Keyword / Clause	Purpose
ORDER BY	Sorts query results.
ASC	Sorts data in ascending order (default).
DESC	Sorts data in descending order.
Multiple Columns	Sorts first by one column, then by the next.
ORDER BY + WHERE	Sorts filtered records.
ORDER BY + GROUP BY	Sorts grouped results.
ORDER BY + HAVING	Sorts filtered groups.

---

Important Points to Remember

• The ORDER BY clause is used to sort the records returned by a SELECT query.
• Ascending (ASC) is the default sorting order in MySQL.
• Use the DESC keyword to arrange records in descending order.
• The ORDER BY clause can sort numeric, text, and date values.
• Multiple columns can be specified in the ORDER BY clause by separating them with commas.
• When multiple columns are used, sorting is performed on the first column, followed by the second, third, and so on if required.
• The ORDER BY clause is usually written at the end of the SQL query.
• ORDER BY can be combined with WHERE, GROUP BY, and HAVING to produce meaningful and organized reports.

---

CBSE Exam Tips

• Remember that ASC is optional because it is the default sorting order.
• Always write DESC explicitly when descending order is required.
• Practice SQL queries involving sorting by one column as well as multiple columns.
• Revise the correct sequence of SQL clauses: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY.
• Carefully read whether the question asks for ascending or descending order before writing the query.
• Expect competency-based questions involving school databases, employee records, banking systems, and library management.

---

Summary

The ORDER BY clause is used to arrange query results in a meaningful order. It supports both ascending (ASC) and descending (DESC) sorting and can be applied to numeric, text, and date columns. It also allows sorting by multiple columns and works effectively with WHERE, GROUP BY, and HAVING clauses. Mastering the ORDER BY clause enables you to create well-organized reports and write efficient SQL queries for real-world database applications as well as CBSE examinations.

---

What's Next?

In the next chapter, you will learn the JOINS in MySQL. You will understand how to retrieve related data from multiple tables using different types of joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN, along with practical examples and CBSE-oriented questions.