SQL ORDER BY Clause - Sorting Results in SQL Queries

Introduction

When you run a SQL query, the results may seem... a bit random. But what if you want to see students in alphabetical order, or arrange marks from highest to lowest? That’s where ORDER BY steps in — a simple yet powerful clause that helps you control the order of your output.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Sample Table – Students

Let’s use a simple students table as our base. We shall use this table in the following examples.

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 15, 'Delhi'),
(2, 'Diya Iyer', '9B', 14, 'Chennai'),
(3, 'Rohit Menon', '10A', 15, 'Kochi'),
(4, 'Sneha Patil', '8C', 13, 'Pune'),
(5, 'Mehul Agarwal', '9B', 14, 'Delhi');

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

1. ORDER BY in Ascending Order (Default)

Use case: A school administrator wants to view the list of all students sorted by their age to identify the youngest and oldest students.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table and sort the result in ascending order based on the age column.

SELECT name, age FROM students
ORDER BY age;

Result description: Result contains the names and ages of all students, sorted from the youngest to the oldest.

Query Result

name	age
Sneha Patil	13
Diya Iyer	14
Mehul Agarwal	14
Aarav Sharma	15
Rohit Menon	15

2. ORDER BY in Descending Order

Use case: A school administrator wants to view a list of all students sorted by age in descending order to identify the oldest students first.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table and sort the result by age in descending order.

SELECT name, age FROM students
ORDER BY age DESC;

Result description: Result contains the names and ages of all students, arranged from the oldest to the youngest.

Query Result

name	age
Aarav Sharma	15
Rohit Menon	15
Diya Iyer	14
Mehul Agarwal	14
Sneha Patil	13

3. ORDER BY Alphabetically

Use case: A school administrator wants to generate an alphabetically sorted list of all students to create an organized student directory.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and class columns from the students table and sort the results by the name column in ascending order.

SELECT name, class FROM students
ORDER BY name;

Result description: Result contains the names and class details of all students, sorted alphabetically by name.

Query Result

name	class
Aarav Sharma	10A
Diya Iyer	9B
Mehul Agarwal	9B
Rohit Menon	10A
Sneha Patil	8C

4. Sorting by Multiple Columns

Use case: A school administrator wants to generate a sorted list of all students, grouped by class and alphabetically ordered by name within each class, for reporting or display purposes.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and class columns from the students table and sort the results first by class and then by name in ascending order.

SELECT name, class FROM students
ORDER BY class, name;

Result description: Result contains the names and class details of all students, sorted by class and then by student name within each class.

Query Result

name	class
Aarav Sharma	10A
Rohit Menon	10A
Sneha Patil	8C
Diya Iyer	9B
Mehul Agarwal	9B

5. Mixing ASC and DESC

Use case: A school administrator wants to generate a sorted list of all students, grouped by class in ascending order and within each class sorted by age in descending order, to identify the oldest students in each class.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name, class, and age columns from the students table, and sort the results first by class in ascending order and then by age in descending order.

SELECT name, class, age FROM students
ORDER BY class ASC, age DESC;

Result description: Result contains the names, classes, and ages of all students, ordered by class (A–Z) and within each class, by age from oldest to youngest.

Query Result

name	class	age
Aarav Sharma	10A	15
Rohit Menon	10A	15
Sneha Patil	8C	13
Diya Iyer	9B	14
Mehul Agarwal	9B	14

6. ORDER BY with WHERE

You can combine ORDER BY with WHERE to filter and then sort:

Use case: A school administrator wants to generate a list of students who are 14 years old or older, sorted alphabetically by name, to plan age-specific programs or activities.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table where the age is greater than or equal to 14, and then sort the results in ascending order by name.

SELECT name, age FROM students
WHERE age >= 14
ORDER BY name;

Result description: Result contains the names and ages of students who are at least 14 years old, displayed in alphabetical order by name.

Query Result

name	age
Aarav Sharma	15
Diya Iyer	14
Mehul Agarwal	14
Rohit Menon	15

7. ORDER BY Column Number (Not Recommended)

You can technically sort using column numbers from the SELECT clause, but it’s risky and non-descriptive:

Use case: A school administrator wants to view a list of students sorted by age in descending order to identify the oldest students in the school.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table and sort the results by the second selected column (age) in descending order.

SELECT name, age FROM students
ORDER BY 2 DESC;

Result description: Result contains the names and ages of all students, sorted from the oldest to the youngest.

Query Result

name	age
Aarav Sharma	15
Rohit Menon	15
Diya Iyer	14
Mehul Agarwal	14
Sneha Patil	13

Better to use explicit column names for clarity.

Best Practices

• Always specify column names in ORDER BY for readability.
• Use multiple columns to define detailed sorting logic.
• ASC is the default — you can skip it unless needed for clarity.

Summary

The ORDER BY clause brings structure and sense to your result sets. Whether you're ranking top students, grouping city-wise attendance, or alphabetizing names for reports — this clause transforms chaos into clarity.

What’s Next?

Now that your data is sorted and structured, it's time to explore the LIMIT clause — a way to fetch only the top results from a large dataset.