Sorting Data with ORDER BY Clause

When working with data, it’s not just about retrieving it—you often need to organize the results to make them easier to read, understand, or analyze. That’s where the ORDER BY clause comes in.

🎯 What You’ll Learn

• How to sort data in ascending or descending order
• How to sort by one or multiple columns
• How to use column positions and aliases in sorting

🔄 Using Our Consistent Example Table: students

CREATE TABLE students ( 
id INT , 
name VARCHAR ( 50 ), 
age INT , 
course VARCHAR ( 30 ), 
marks DECIMAL ( 5 , 2 ) );  

Sample data:

id	name	age	course	marks
1	Rahul Sharma	21	SQL	85.50
2	Anjali Mehta	22	SQL	91.75
3	Vikas Gupta	23	SQL	88.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	SQL	76.00

🧮 Basic Syntax

SELECT * FROM table_name ORDER BY column_name [ ASC | DESC ];  

• ASC→ Ascending (default)
• DESC→ Descending

🔹 Sorting by a Single Column

Sort by Marks in Ascending Order

SELECT * FROM students ORDER BY marks;  

Default is ascending ( ASC ) even if not written.

Sort by Age in Descending Order

SELECT * FROM students ORDER BY age DESC ;  

🔸 Sorting by Multiple Columns

You can sort by more than one column to create secondary sorting .

Sort by Course (A-Z), then Marks (High to Low)

SELECT * FROM students ORDER BY course ASC , marks DESC ;  

Within each course, students will be listed with highest to lowest marks.

🔁 Sorting Using Column Position (Not Recommended but Possible)

You can sort by column position number from the SELECT statement:

SELECT name, age, course FROM students ORDER BY 2 DESC ;  -- sorts by age (2nd column)  

⚠️ Note: Not very readable or safe. Prefer column names.

🧾 Sorting with Aliases

If you've renamed a column using AS , you can sort by that alias:

SELECT name, marks AS percentage FROM students ORDER BY percentage DESC ;  

🧪 Practical Examples

1. List students sorted by name A–Z:

SELECT * FROM students ORDER BY name;  

1. List students in SQL course sorted by marks high to low:

SELECT * FROM students WHERE course = 'SQL' ORDER BY marks DESC ;  

1. List all students sorted by course (A-Z) and age (youngest to oldest):

SELECT * FROM students ORDER BY course ASC , age ASC ;  

⚠️ Common Mistakes to Avoid

Mistake	Correction
Forgetting DESC for reverse sort	Always specify DESC if needed
Sorting by incorrect column name	Use the exact column or its alias
Using numbers in ORDER BY blindly	Prefer column names over positions

📌 Summary

• ORDER BY helps organize data results by one or more columns.
• Default sort order is ascending ( ASC ), use DESC for reverse.
• You can sort using column names, aliases, or even positions .