MySQL ORDER BY

The ORDER BY clause sorts records returned by a SELECT query.

SELECT *
FROM students
ORDER BY name;

This sorts the students by name in ascending order.

The basic syntax is:

SELECT column1, column2
FROM table_name
ORDER BY column_name;

You can specify ASC or DESC after the column name.

ASC means ascending order.

SELECT *
FROM students
ORDER BY name ASC;

Names are arranged from A to Z.

DESC means descending order.

SELECT *
FROM students
ORDER BY name DESC;

Names are arranged from Z to A.

If you do not specify ASC or DESC, MySQL uses ascending order.

SELECT *
FROM students
ORDER BY name;

This is equivalent to:

SELECT *
FROM students
ORDER BY name ASC;

ORDER BY can sort numeric columns.

SELECT *
FROM students
ORDER BY age ASC;

This displays students from the lowest age to the highest age.

SELECT *
FROM students
ORDER BY age DESC;

This displays students from the highest age to the lowest age.

ORDER BY is commonly used to arrange fees.

SELECT name, fee
FROM students
ORDER BY fee ASC;

This displays the lowest fees first.

SELECT name, fee
FROM students
ORDER BY fee DESC;

This displays students with the highest fee first.

Date columns can also be sorted.

SELECT name, admission_date
FROM students
ORDER BY admission_date ASC;

This displays the earliest admission dates first.

SELECT name, admission_date
FROM students
ORDER BY admission_date DESC;

This displays the most recent admission dates first.

ORDER BY can be used after a WHERE condition.

SELECT *
FROM students
WHERE course = 'Python'
ORDER BY fee DESC;

This finds Python students and displays the highest fee first.

ORDER BY and LIMIT are often used together.

SELECT *
FROM students
ORDER BY fee DESC
LIMIT 5;

This returns the five students with the highest fees.

Use ASC with LIMIT to find the lowest values.

SELECT name, fee
FROM students
ORDER BY fee ASC
LIMIT 5;

This returns the five lowest fees.

You can sort by more than one column.

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

MySQL first sorts by course and then sorts names within each course.

Each column can have its own sorting direction.

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

Courses are sorted alphabetically, while fees within each course are sorted from highest to lowest.

You can sort the result even when the ORDER BY column is not displayed.

SELECT name, course
FROM students
ORDER BY fee DESC;

The results are sorted by fee even though fee is not included in the SELECT list.

ORDER BY can be combined with DISTINCT.

SELECT DISTINCT course
FROM students
ORDER BY course ASC;

This displays unique course names in alphabetical order.

ORDER BY can be combined with LIKE.

SELECT name, course
FROM students
WHERE name LIKE 'A%'
ORDER BY name ASC;

This finds names beginning with A and sorts them alphabetically.

ORDER BY can also be used with IN.

SELECT name, course, fee
FROM students
WHERE course IN ('Python', 'Java')
ORDER BY fee DESC;

This displays Python and Java students from highest fee to lowest fee.

You can sort by a calculated expression.

SELECT name, fee, fee * 0.90 AS discounted_fee
FROM students
ORDER BY discounted_fee ASC;

The result is sorted using the calculated discounted fee.

A column alias can be used in ORDER BY.

SELECT name, fee * 0.90 AS discounted_fee
FROM students
ORDER BY discounted_fee ASC;

The alias discounted_fee is used for sorting.

ORDER BY can sort grouped or aggregated results.

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
ORDER BY total_students DESC;

This displays courses with the largest number of students first.

GROUP BY creates groups and ORDER BY sorts the resulting groups.

SELECT course, AVG(fee) AS average_fee
FROM students
GROUP BY course
ORDER BY average_fee DESC;

This displays courses with the highest average fee first.

MySQL allows sorting by the position of a selected column.

SELECT name, course, fee
FROM students
ORDER BY 3 DESC;

Here, 3 refers to the third selected column, which is fee.

• Forgetting to specify the correct column
• Using ASC when DESC is required
• Sorting the wrong column
• Forgetting that multiple columns are processed from left to right
• Using an incorrect alias
• Using column positions without understanding their meaning
• Expecting the original table data to be permanently rearranged

ORDER BY only changes the order of rows in the query result. It does not permanently rearrange the records stored in the table.

SELECT *
FROM students
ORDER BY name ASC;

The table remains stored in the database; the query simply returns the result in the requested order.

A simple ORDER BY workflow is:

1. Select the required columns.
2. Choose the table.
3. Apply WHERE if filtering is required.
4. Use ORDER BY for sorting.
5. Choose ASC or DESC.
6. Use LIMIT if only a specific number of records is required.

SELECT id, name, course, fee
FROM students
WHERE age >= 18
ORDER BY fee DESC
LIMIT 10;

Suppose we want to display the students with the highest fees first.

SELECT id, name, course, fee
FROM students
ORDER BY fee DESC
LIMIT 10;

This returns the top 10 students according to fee amount.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2),
    admission_date DATE
);

INSERT INTO students
(name, course, age, fee, admission_date)
VALUES
('Amit', 'Python', 22, 15000.00, '2026-01-15'),
('Priya', 'Java', 21, 18000.00, '2026-02-10'),
('Rahul', 'PHP', 24, 12000.00, '2026-03-05'),
('Neha', 'Python', 20, 20000.00, '2026-04-12'),
('Ravi', 'JavaScript', 26, 22000.00, '2026-05-20');

SELECT id, name, course, age, fee
FROM students
WHERE age >= 20
ORDER BY fee DESC
LIMIT 3;

This query selects students aged 20 or above, sorts them by fee from highest to lowest, and returns only the first three records.