• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL ORDER BY

← Previous Next →

In MySQL, the ORDER BY clause is used to sort the result set of a query. By default, SQL returns rows in the order they are stored in the table, which may not be meaningful. ORDER BY allows you to organize the data in ascending or descending order based on one or more columns.

Sorting data is essential for analysis, reporting, and presenting results in a human-readable format. For example, you might want to list students from the youngest to the oldest, or sort products by price from highest to lowest.

Basic Syntax

The general syntax of ORDER BY is:

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

  • SELECT column1, column2 specifies the columns to retrieve.

  • FROM table_name indicates the table.

  • ORDER BY column1 specifies the column used for sorting.

  • ASC (ascending) or DESC (descending) determines the order.

  • If you omit ASC or DESC, ascending order is default.

Example:

SELECT name, age FROM students
ORDER BY age ASC;

This sorts students from youngest to oldest.

Sorting in Descending Order

To sort in descending order, use the DESC keyword:

SELECT name, age FROM students
ORDER BY age DESC;

This lists students from oldest to youngest.

Sorting by Multiple Columns

You can sort by more than one column. MySQL sorts the first column and, if there are duplicates, sorts by the next column.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

Example:

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

  • Students are first sorted by city alphabetically.

  • Within the same city, students are sorted by age in descending order.

Sorting by Column Aliases

If you use column aliases, you can sort using the alias name:

SELECT name AS StudentName, age AS StudentAge
FROM students
ORDER BY StudentAge ASC;

This improves query readability and is helpful in reports.

Sorting by Expressions

ORDER BY can also sort by expressions or calculations:

SELECT name, age, age + 2 AS AgeAfterTwoYears
FROM students
ORDER BY AgeAfterTwoYears DESC;

Here, students are sorted based on the calculated age two years from now.

Sorting Text Columns

MySQL can sort text columns alphabetically:

SELECT name, city FROM students
ORDER BY city ASC;

  • Sorting is case-insensitive by default.

  • You can also sort in reverse alphabetical order using DESC.

Using ORDER BY with LIMIT

The LIMIT clause is often used with ORDER BY to get top or bottom records:

SELECT name, age FROM students
ORDER BY age DESC
LIMIT 3;

  • This fetches the three oldest students.

  • Similarly, you can skip records with an offset:

SELECT name, age FROM students
ORDER BY age ASC
LIMIT 2, 3;

  • Skips the first 2 rows and returns the next 3.

Sorting Null Values

In MySQL, NULL values are treated differently depending on the column type and sorting direction:

  • In ascending order (ASC), NULLs appear first.

  • In descending order (DESC), NULLs appear last.

Example:

SELECT name, city FROM students
ORDER BY city ASC;

Students with NULL in city will appear at the top.

Best Practices

  1. Always specify the sort direction (ASC or DESC) to avoid confusion.

  2. Sort by indexed columns for faster query performance.

  3. Combine ORDER BY with LIMIT to retrieve top or bottom results efficiently.

  4. Avoid sorting very large datasets without indexes, as it can slow down queries.

  5. Use aliases for readability when sorting by expressions or calculations.

Real-Life Example

Suppose you have a students table:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 Delhi
3 Pooja 18 Pune
4 Riya 21 Mumbai
5 Ananya 22 Delhi

Example 1 – Sort by age ascending:

SELECT name, age FROM students
ORDER BY age ASC;

Result: Pooja, Aditi, Meera, Riya, Ananya

Example 2 – Sort by city ascending and age descending:

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

Result:

  • Delhi: Ananya, Meera

  • Mumbai: Riya, Aditi

  • Pune: Pooja

Example 3 – Top 2 oldest students:

SELECT name, age FROM students
ORDER BY age DESC
LIMIT 2;

Result: Ananya, Riya

Summary of the Tutorial

  • The ORDER BY clause sorts query results in ascending (ASC) or descending (DESC) order.

  • You can sort by single or multiple columns, expressions, or aliases.

  • ORDER BY works well with LIMIT to fetch top or bottom records.

  • Null values are sorted first in ascending order and last in descending order.

  • Proper use of ORDER BY improves data readability and is crucial for reports and analysis.

Practice Questions

  1. Retrieve all students from the students table and sort them by age in ascending order.

  2. List all students sorted by name in descending order.

  3. Select name and city of students and sort first by city ascending, then by age descending.

  4. Retrieve the top 3 oldest students using ORDER BY and LIMIT.

  5. Fetch students whose city is NULL and sort by age ascending.

  6. Select name and calculated age (age + 2) and sort the results in descending order of calculated age.

  7. Retrieve all students and sort by city alphabetically.

  8. Fetch students and sort by name ascending, then by age ascending.

  9. Select students and display only the 4 youngest using ORDER BY and LIMIT.

  10. Retrieve all students and sort by age descending while handling NULL values correctly.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top