SQL ORDER BY Clause — Sorting Results in SQL Queries

A school prize list sorts marks from high to low, but two students have the same mark. Their order needs a clear rule.

ORDER BY defines the ordering of a query result. Multiple sort expressions provide rules for breaking ties.

DESC puts larger non-null numeric values first; ASC reverses that direction. A later ordering expression is used when earlier values tie. Sorting the result does not permanently rearrange a table into a guaranteed order for future queries.

Related concepts: Limit Result Rows and Window Functions.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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');

SQL · portable concepts; preserved examples identify their engine

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

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

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.

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.

Better to use explicit column names for clarity.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• 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.

Use a stable unique tie-breaker when LIMIT or pagination depends on reproducible results. NULL ordering and text collation vary by engine. Do not assume that an index, insertion order or a previously sorted query guarantees this query’s order.

Sort the three students by marks descending and then roll number ascending.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name, marks FROM practice_students ORDER BY marks DESC, roll_no ASC;

The first sort expression ties Aarav and Sneha at 85. Their roll numbers 1 and 3 settle that tie before the 70-mark row.

ORDER BY defines the ordering of a query result. Multiple sort expressions provide rules for breaking ties.

The first sort expression ties Aarav and Sneha at 85. Their roll numbers 1 and 3 settle that tie before the 70-mark row.

Continue your understanding with Limit Result Rows and Window Functions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.