MySQL ORDER BY Keyword

  1. Overview
  2. MYSQL
  3. MySQL SQL
  4. MySQL ORDER BY Keyword

The ORDER BY keyword in MySQL is used to sort the records retrieved from a database query. It provides flexibility to organize the result set either in ascending or descending order based on one or more columns.

Key Points About ORDER BY

  1. Default Sorting:

    • Records are sorted in ascending order by default when the ORDER BY keyword is used.

  2. Descending Order:

    • To sort records in descending order, the DESC keyword is added after the column name.

  3. Multiple Columns:

    • Sorting can be applied to multiple columns. When multiple columns are specified, the results are sorted by the first column and then by the subsequent columns.

Syntax

For Single Column

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

For Multiple Columns

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

Examples

1. Default (Ascending) Order

Retrieve all customers from the "Customers" table, sorted by their CustomerName in ascending order:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY CustomerName;

2. Sorting in Descending Order

Retrieve all customers, sorted by CustomerID in descending order:

SELECT CustomerName, CustomerID
FROM Customers
ORDER BY CustomerID DESC;

3. Sorting by Multiple Columns

Retrieve customers, sorted first by Country in ascending order and then by City in descending order:

SELECT CustomerName, Country, City
FROM Customers
ORDER BY Country ASC, City DESC;

Sample Data

CustomerID CustomerName City Country
1 Alfreds Futterkiste Berlin Germany
2 Ana Trujillo Emparedados y helados México D.F. Mexico
3 Antonio Moreno Taquería México D.F. Mexico
4 Around the Horn London UK
5 Berglunds snabbköp Luleå Sweden

Example Results

Query:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY Country ASC, City DESC;

Result:

CustomerName City Country
Alfreds Futterkiste Berlin Germany
Berglunds snabbköp Luleå Sweden
Around the Horn London UK
Antonio Moreno Taquería México D.F. Mexico
Ana Trujillo Emparedados y helados México D.F. Mexico

Key Notes

  1. Case Sensitivity: Sorting is case-insensitive for text-based columns unless specified otherwise by collation settings.
  2. Null Values: MySQL treats NULL values as the lowest value in ascending order and the highest value in descending order.
  3. Index Optimization: Sorting can be faster when an indexed column is used in the ORDER BY clause.

With the ORDER BY keyword, you can efficiently organize and present your query results in a structured manner.

Was this article helpful?