ORDER BY in SQL

The ORDER BY clause is used to arrange records according to the value of one or more columns.

SELECT *
FROM Students
ORDER BY name;

This displays students sorted by their names.

SELECT column1, column2
FROM table_name
ORDER BY column_name;

The column specified after ORDER BY determines how the result is sorted.

ASC means ascending order.

SELECT *
FROM Students
ORDER BY name ASC;

Names are displayed from A to Z.

DESC means descending order.

SELECT *
FROM Students
ORDER BY name DESC;

Names are displayed from Z to A.

If you do not specify ASC or DESC, ascending order is used by default.

SELECT *
FROM Students
ORDER BY name;

This is generally equivalent to:

SELECT *
FROM Students
ORDER BY name ASC;

ORDER BY can sort numeric columns.

SELECT name, age
FROM Students
ORDER BY age ASC;

Students are displayed from the lowest age to the highest age.

SELECT name, age
FROM Students
ORDER BY age DESC;

Students are displayed from the highest age to the lowest age.

SELECT name, course, fee
FROM Students
ORDER BY fee ASC;

This displays students starting from the lowest fee.

SELECT name, course, fee
FROM Students
ORDER BY fee DESC;

This displays the highest fee first.

ORDER BY can be used after a WHERE condition.

SELECT name, age, city
FROM Students
WHERE city = 'Patna'
ORDER BY name ASC;

This finds students from Patna and sorts them by name.

SELECT name, fee
FROM Students
WHERE course = 'Python'
ORDER BY fee DESC;

This displays Python students with the highest fee first.

ORDER BY is commonly combined with LIMIT when you want only a certain number of records.

SELECT name, fee
FROM Students
ORDER BY fee DESC
LIMIT 5;

In MySQL, this returns the five students with the highest fee.

You can sort records using more than one column.

SELECT name, city, age
FROM Students
ORDER BY city ASC, name ASC;

The records are first sorted by city and then by name within each city.

SELECT name, city, age
FROM Students
ORDER BY city ASC, age DESC;

Cities are sorted alphabetically, while students within each city are sorted by age from highest to lowest.

SELECT DISTINCT city
FROM Students
ORDER BY city ASC;

This displays unique city names in alphabetical order.

SELECT name, city, course
FROM Students
WHERE course IN ('Python', 'Java')
ORDER BY name ASC;

This displays Python and Java students sorted by name.

SELECT name, age, fee
FROM Students
WHERE age BETWEEN 18 AND 30
ORDER BY age ASC;

This finds students between 18 and 30 years old and sorts them by age.

SELECT name, city
FROM Students
WHERE name LIKE 'A%'
ORDER BY name ASC;

This finds names starting with A and sorts them alphabetically.

SELECT name, email
FROM Students
ORDER BY email ASC;

If the column contains NULL values, their position in the sorted result depends on the database system and its sorting rules.

ORDER BY can be used to sort dates.

SELECT name, admission_date
FROM Students
ORDER BY admission_date ASC;

This displays the oldest admission dates first.

SELECT name, admission_date
FROM Students
ORDER BY admission_date DESC;

This displays the latest admission dates first.

ORDER BY can also sort results produced by aggregate functions.

SELECT city, COUNT(*) AS total_students
FROM Students
GROUP BY city
ORDER BY total_students DESC;

This displays cities with the highest number of students first.

SELECT course, SUM(fee) AS total_fee
FROM Students
GROUP BY course
ORDER BY total_fee DESC;

This displays courses according to their total fee, from highest to lowest.

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

This sorts courses according to their average fee.

Some SQL systems allow sorting by the position of a selected column.

SELECT name, city, fee
FROM Students
ORDER BY 3 DESC;

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

You can sort using a column alias defined in the SELECT list.

SELECT name, fee * 12 AS annual_fee
FROM Students
ORDER BY annual_fee DESC;

The result is sorted using the calculated annual_fee value.

A common mistake is placing ORDER BY before WHERE.

Incorrect order:

SELECT *
FROM Students
ORDER BY name
WHERE city = 'Patna';

Correct order:

SELECT *
FROM Students
WHERE city = 'Patna'
ORDER BY name;

WHERE comes before ORDER BY in the SQL query structure.

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

The records are grouped by course and then sorted by the number of students in each course.

Suppose an institute wants to display students from Patna who are studying Python or Java, with the highest fee first.

SELECT name, city, course, fee
FROM Students
WHERE city = 'Patna'
AND course IN ('Python', 'Java')
ORDER BY fee DESC;

This query filters the students and then sorts the matching records by fee.

SELECT name, age, course, city, fee, admission_date
FROM Students
WHERE course IN ('Python', 'Java')
AND age BETWEEN 18 AND 30
AND city LIKE 'P%'
ORDER BY fee DESC, name ASC
LIMIT 10;

This query:

• Finds students studying Python or Java.
• Checks students between 18 and 30 years old.
• Finds cities beginning with P.
• Sorts students by fee from highest to lowest.
• Sorts students with the same fee alphabetically by name.
• Returns up to 10 records.