ORDER BY Clause

ORDER BY Clause in MySQL

Introduction
Sorting query results makes data more readable and useful.
The ORDER BY clause in MySQL sorts rows based on one or more columns.
It is widely used for:

• Displaying results in logical order
• Pagination with LIMIT
• Ranking or top-N queries

What is the ORDER BY Clause
The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order.
It is applied after WHERE, GROUP BY, and HAVING clauses.

Basic Syntax

sql

SELECT column_name 

FROM table_name 

ORDER BY column_name [ASC|DESC]; 

Example

sql

SELECT * FROM Students 

ORDER BY age ASC; 

This returns students sorted by age from youngest to oldest.

ORDER BY with DESC
Use DESC for descending order (highest to lowest).

sql

SELECT * FROM Students 

ORDER BY marks DESC; 

This shows the top scorers first.

ORDER BY Multiple Columns
Sort by primary column, then secondary if tied.

sql

SELECT * FROM Students 

ORDER BY age DESC, marks ASC; 

Sorts by age (oldest first), then marks (lowest to highest) within the same age.

ORDER BY with WHERE Clause
Filters first, then sorts.

sql

SELECT * FROM Students 

WHERE city = 'Delhi' 

ORDER BY marks DESC; 

ORDER BY with LIMIT
Combines sorting with row limits for pagination.

sql

SELECT * FROM Students 

ORDER BY marks DESC 

LIMIT 5; 

Gets the top 5 students by marks.

ORDER BY in Pagination
Formula for page N: ORDER BY ... LIMIT (N-1)*page_size, page_size
Page 1: ORDER BY marks DESC LIMIT 0, 10
Page 2: ORDER BY marks DESC LIMIT 10, 10

ORDER BY with JOIN
Sorts joined results.

sql

SELECT Students. name, Courses.course_name 

FROM Students 

JOIN Courses ON Students.id = Courses.student_id 

ORDER BY Students.marks DESC 

LIMIT 5; 

ORDER BY with Subqueries
Sorts subquery results.

sql

SELECT * FROM ( 

    SELECT name, AVG(marks) as avg_marks 

    FROM Students 

    GROUP BY name 

) AS temp 

ORDER BY avg_marks DESC 

LIMIT 3; 

ORDER BY with GROUP BY
Sorts grouped results.

sql

SELECT city, AVG(marks) as avg_marks 

FROM Students 

GROUP BY city 

ORDER BY avg_marks DESC; 

Execution Order with ORDER BY
ORDER BY is applied after:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Performance Considerations

• Use indexes on ORDER BY columns for speed
• Avoid sorting large datasets without LIMIT
• Multiple columns: index in sort order
• DESC sorting needs a separate index in MySQL

Important Notes

• Default is ASC if not specified
• NULL values sort first in ASC, last in DESC
• Case-sensitive with BINARY collation
• Works with SELECT, but not standalone
• Inconsistent without stable sort keys

Example Scenario
Get the top 3 students per city by marks:

sql

SELECT city, name, marks 

FROM Students 

ORDER BY city ASC, marks DESC 

LIMIT 3; 

(Adjust for full pagination)

Common Mistakes

• Forgetting ORDER BY before LIMIT (random order)
• No index on sort column (slow queries)
• Mixing ASC/DESC without testing
• Using functions in ORDER BY (prevents index use)
• Large tables without LIMIT

Key Points to Remember

• ORDER BY sorts the result set ASC (default) or DESC
• Supports multiple columns (left-to-right priority)
• Essential for pagination with LIMIT
• Place after SELECT/WHERE/GROUP BY/HAVING
• Index ORDER BY columns for performance
• NULLs first in ASC, last in DESC