SQL ORDER BY

SQL ORDER BY Keyword

The ORDER BY keyword in SQL is used to sort the rows of a result set in either ascending or descending order based on one or more columns. By default, the sorting is done in ascending order, but you can explicitly specify descending order using the DESC keyword. Sorting data helps to organize it for better readability and analysis.

Why Use the ORDER BY Keyword?

1. Organize Data: Displays data in a logical order, making it easier to understand.
2. Sort Numerically: Arrange numerical values from smallest to largest or vice versa.
3. Sort Alphabetically: Order text values alphabetically for better structure.
4. Sort by Multiple Columns: Organize data hierarchically by applying sorting to multiple columns.

Syntax for ORDER BY

1
2
3
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Key Components:

• ORDER BY column1: Specifies the column to sort by.
• ASC: (Optional) Specifies ascending order (default).
• DESC: Sorts the column in descending order.

Examples of SQL ORDER BY Keyword

Sort Data by a Numeric Column

This query sorts the records in ascending order based on a numerical column.

1
2
3
SELECT * 
FROM Sales
ORDER BY SaleAmount;

Explanation: This query displays all sales records, sorted by the SaleAmount column in ascending order (smallest to largest).

Sort Data by a Numeric Column in Descending Order

You can sort the same numeric column in descending order using DESC.

1
2
3
SELECT * 
FROM Sales
ORDER BY SaleAmount DESC;

Explanation: This query displays all sales records, but now sorted by SaleAmount from largest to smallest.

Sort Alphabetically by a Text Column

When sorting a text column, ORDER BY arranges the data alphabetically by default.

1
2
3
SELECT * 
FROM Employees
ORDER BY LastName;

Explanation: This query retrieves all employee records and orders them alphabetically by LastName.

Sort Alphabetically in Reverse Order

To sort text data in reverse alphabetical order, use the DESC keyword.

1
2
3
SELECT * 
FROM Employees
ORDER BY LastName DESC;

Explanation: This query orders employee records by LastName in reverse alphabetical order.

Sort by Multiple Columns

The ORDER BY keyword can sort data based on multiple columns. The data is first sorted by the first column, and then the second column is used to sort rows with identical values in the first column.

1
2
3
SELECT * 
FROM Customers
ORDER BY Country, CustomerName;

Explanation: This query sorts customer records by Country in ascending order. If multiple customers are from the same country, they are further sorted alphabetically by CustomerName.

Use Both ASC and DESC for Multiple Columns

You can specify different sorting orders for multiple columns.

1
2
3
SELECT * 
FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Explanation: This query sorts customer records by Country in ascending order. For customers from the same country, it sorts their names in descending order.

Best Practices for ORDER BY Keyword

1. Specify Sorting: Always use ASC or DESC to avoid confusion about the default sorting order.
2. Sort Only Required Columns: Avoid unnecessary sorting as it can increase query execution time.
3. Use Indexes: Ensure columns used in ORDER BY are indexed to improve performance.
4. Combine with Filtering: Use WHERE to limit data before sorting for better efficiency.

Key Points to Remember

• The ORDER BY keyword organizes data in ascending (default) or descending order.
• It can be used with both numeric and text columns.
• Multiple columns can be sorted in a hierarchical order by specifying their sort directions.
• Proper indexing and query optimization can enhance sorting performance.