Learn to use SQL ORDER BY like a PRO with Our Guide

Introduction to SQL ORDER BY

The SQL ORDER BY clause is essential for sorting the results of your queries in a specific order, which can be either ascending or descending. It allows you to organize the retrieved data in a way that makes it more meaningful and easier to analyze. By default, the SQL ORDER BY clause sorts the records in ascending order, but it can also sort them in descending order using the DESC keyword.

Syntax Overview

The basic syntax for the SQL ORDER BY clause is as follows:

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

Here, table_name is the name of the table you want to query, and column1, column2, etc., are the names of the columns you want to retrieve and sort the results by. The ASC keyword is used for ascending order, and DESC is used for descending order.

 

Sample Tables for Examples

We will create some SQL Tables which will be used through out this article for demonstrating different examples:

Table-1: Persons

CREATE TABLE Persons (
    PersonID INT PRIMARY KEY NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Age INT,
    City VARCHAR(50)
);

INSERT INTO Persons (PersonID, FirstName, LastName, Age, City) VALUES 
(1, 'Aarav', 'Sharma', 29, 'Delhi'),
(2, 'Vihaan', 'Patel', 22, 'Mumbai'),
(3, 'Aditya', 'Sengupta', 35, 'Bangalore'),
(4, 'Ishaan', 'Deshmukh', 42, 'Hyderabad'),
(5, 'Sai', 'Naidu', 31, 'Chennai');

Table-2: Orders

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY NOT NULL,
    PersonID INT,
    OrderDate DATE,
    OrderAmount DECIMAL(10, 2),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

INSERT INTO Orders (OrderID, PersonID, OrderDate, OrderAmount) VALUES 
(101, 1, '2023-10-01', 150.75),
(102, 2, '2023-10-21', 200.50),
(103, 3, '2023-09-15', 75.00),
(104, 4, '2023-08-30', 310.20),
(105, 5, '2023-10-10', 99.99);

 

Basic Usage of ORDER BY

1. Sorting in Ascending Order

Using the "SQL ORDER BY" clause in ascending order is straightforward. It is the default behavior when no sort order is specified. Here is an example using the Persons table:

SELECT FirstName, LastName, Age
FROM Persons
ORDER BY Age;

This query retrieves the names and ages from the Persons table and sorts the results in ascending order based on the Age column.

 

2. Sorting in Descending Order

To sort the results in descending order using the "SQL ORDER BY" clause, you use the DESC keyword. Here’s how you can do it:

SELECT FirstName, LastName, Age
FROM Persons
ORDER BY Age DESC;

In this query, the results are sorted in descending order of age, meaning the oldest person will appear first.

 

3. Sorting by Multiple Columns

The "SQL ORDER BY" clause is also powerful in that it allows you to sort the results by multiple columns. The query processes the sort by the first column and then sorts the ties by the next columns in the order they are listed.

SELECT FirstName, LastName, Age, City
FROM Persons
ORDER BY City, Age;

This query sorts the results primarily by City in ascending order. If there are multiple persons from the same city, it will then sort those specific results by Age in ascending order.

 

Advanced Usage of ORDER BY

1. Using Expressions

Expressions in SQL ORDER BY clause allow for sorting results based on the outcome of an expression. This means that instead of just specifying a column name to sort by, you can perform operations or calculations to sort the data.

SELECT FirstName, LastName, Age * 12 as MonthsLived
FROM Persons
ORDER BY Age * 12;

In this example, the SQL ORDER BY clause sorts the data based on an expression (Age * 12), which calculates the age of persons in months. The resulting data is ordered by the calculated age in months.

 

2. Using Aliases

Aliases can be used in conjunction with the SQL ORDER BY clause to make the queries more readable. You can assign a temporary name to columns or expressions and use these aliases in the ORDER BY clause.

SELECT FirstName, LastName, Age * 12 as MonthsLived
FROM Persons
ORDER BY MonthsLived;

Here, the alias MonthsLived is used to refer to the expression, making the SQL ORDER BY clause more concise and the overall query more readable.

 

3. Using Conditional Order

Conditional ordering allows for sorting based on certain conditions. It is often achieved using CASE WHEN statements within the SQL ORDER BY clause.

SELECT FirstName, LastName, Age, City
FROM Persons
ORDER BY 
    CASE 
        WHEN City = 'Delhi' THEN 1
        ELSE 2
    END, 
    Age;

In this example, the SQL ORDER BY clause sorts the persons, giving priority to those from Delhi. Within each priority group, persons are sorted by age.

 

ORDER BY with Different SQL Clauses

1. ORDER BY with WHERE Clause

The WHERE clause filters the records, and the SQL ORDER BY clause sorts these filtered records.

SELECT FirstName, LastName, Age
FROM Persons
WHERE Age > 30
ORDER BY Age;

This query filters the persons to those older than 30 and sorts them in ascending order of age.

 

2. ORDER BY with GROUP BY Clause

SQL ORDER BY clause can be used after the GROUP BY clause to sort the aggregated data.

SELECT City, AVG(Age) as AverageAge
FROM Persons
GROUP BY City
ORDER BY AverageAge DESC;

This query calculates the average age of persons in each city and sorts the cities in descending order of average age.

 

3. ORDER BY with DISTINCT Clause

The SQL ORDER BY clause sorts the unique values retrieved by the DISTINCT clause.

SELECT DISTINCT City
FROM Persons
ORDER BY City;

This query retrieves unique city names from the Persons table in alphabetical order.

 

4. ORDER BY with JOINs

You can sort the results of a JOIN operation using the SQL ORDER BY clause.

SELECT Persons.FirstName, Persons.LastName, Orders.OrderAmount
FROM Persons
JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY Orders.OrderAmount DESC;

This query joins the Persons and Orders tables and sorts the results in descending order based on the order amounts.

 

Working with Different Data Types

Text

When using SQL ORDER BY with text data types, the sorting is done alphabetically (lexicographically).

SELECT FirstName, LastName 
FROM Persons
ORDER BY LastName, FirstName;

In this query, the SQL ORDER BY clause sorts the retrieved data primarily by the LastName column and then sorts any matching last names by the FirstName column.

Numeric

Numeric data types are sorted in ascending (default) or descending order as per the numerical value.

SELECT PersonID, Age 
FROM Persons
ORDER BY Age DESC;

The SQL ORDER BY clause in this query sorts persons based on their age in descending order, displaying older persons first.

Date and Time

Date and time values are sorted chronologically when using the SQL ORDER BY clause.

SELECT OrderID, PersonID, OrderDate 
FROM Orders
ORDER BY OrderDate;

In this query, the orders are sorted based on the OrderDate, organizing the data chronologically from the oldest to the newest order.

 

ORDER BY with Functions

1. Using Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. The SQL ORDER BY clause can be used to sort these aggregated results.

SELECT City, AVG(Age) as AverageAge 
FROM Persons
GROUP BY City
ORDER BY AVG(Age);

This example illustrates the SQL ORDER BY clause sorting cities based on the average age of persons, calculated using the AVG aggregate function.

 

2. Using Date and Time Functions

Date and time functions allow for the extraction and manipulation of date and time values. The SQL ORDER BY clause can sort the results based on these manipulated values.

SELECT OrderID, OrderDate, MONTH(OrderDate) as OrderMonth 
FROM Orders
ORDER BY OrderMonth;

This uses the MONTH function, to extract the month from the OrderDate column. Then, it orders the results based on the extracted month value.

 

Handling Null Values in ORDER BY

1. Default Behavior with Nulls

In SQL, when you use the ORDER BY clause to sort your results, null values are handled in a specific way. By default, null values are considered the lowest possible value and appear first when sorting in ascending order.

-- Assuming some ages are NULL
SELECT FirstName, LastName, Age 
FROM Persons
ORDER BY Age;

In this query, using the SQL ORDER BY clause, any person with a null age would appear first in the results, followed by records sorted by age in ascending order.

 

2. Custom Sorting with Nulls

You can customize the sort order of null values using conditional sorting. You might want to treat null values as the highest possible values instead of the lowest.

SELECT FirstName, LastName, Age 
FROM Persons
ORDER BY 
    CASE 
        WHEN Age IS NULL THEN 1
        ELSE 0
    END, 
    Age;

Here, the SQL ORDER BY clause, combined with a CASE statement, ensures that null values appear last in the sorted result set, offering flexibility in handling nulls during sorting.

 

Pagination with ORDER BY

Pagination is crucial in various scenarios, especially when dealing with large datasets, as it allows you to retrieve and display data in smaller, more manageable chunks. SQL Server employs a distinct syntax incorporating the OFFSET and FETCH clauses for pagination in conjunction with the ORDER BY clause.

Using OFFSET and FETCH

• OFFSET: This clause is used to specify the number of rows to skip before starting to return rows from the query.
• FETCH: This clause specifies the maximum number of rows to return after the OFFSET clause has been processed.

Here’s how we can utilize both clauses with the ORDER BY clause for effective pagination in SQL Server:

-- To retrieve the first five rows
SELECT FirstName, LastName, Age 
FROM Persons
ORDER BY Age
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

-- To retrieve five rows after skipping the first ten rows
SELECT FirstName, LastName, Age 
FROM Persons
ORDER BY Age
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

In these examples, the ORDER BY clause is used first to sort the data, followed by OFFSET and FETCH to return a specific subset of the sorted data.

 

SQL ORDER BY in Different Databases

Different databases may have subtle syntax variations and functionalities when it comes to using the SQL ORDER BY clause. Here’s a brief look at how it is implemented across various popular databases such as MySQL, PostgreSQL, SQL Server, and Oracle.

MySQL and PostgreSQL

• These databases have quite similar syntaxes. Both support the LIMIT and OFFSET clauses for pagination in combination with ORDER BY.
• Example: In both MySQL and PostgreSQL, you might use ORDER BY column_name LIMIT 5 OFFSET 10 to paginate results.

SQL Server

• SQL Server uses the TOP keyword to limit the number of rows returned, and for pagination, the OFFSET and FETCH clauses are used.
• Example: In SQL Server, you might use ORDER BY column_name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY.

Oracle

• Oracle database uses the ROWNUM or the FETCH FIRST clauses for limiting the results.
• Example: In Oracle, you might use ORDER BY column_name FETCH FIRST 10 ROWS ONLY.

 

Frequently Asked Questions (FAQs)

 

Summary and Best Practices

In conclusion, the SQL ORDER BY clause is indispensable for sorting retrieved database records in a query, offering a broad spectrum of functionalities to achieve meaningful data presentation and analysis. Below are some best practices to consider when using the SQL ORDER BY clause:

• Explicit Sorting: Always explicitly define the sort order using ASC or DESC, ensuring the data is presented as intended, enhancing readability and maintainability.
• Utilize Aliases: Make use of aliases for column names or calculations to keep the ORDER BY clause concise and the queries clean and understandable.
• Handling Null Values: Be mindful of how null values are handled in sorting. Customize the sort order of null values as per requirements, ensuring they don’t affect the analysis.
• Combination with Other Clauses: Understand how the ORDER BY clause interacts with other SQL clauses like GROUP BY, WHERE, and JOIN, ensuring they complement each other effectively.
• Optimize for Performance: Keep performance in mind, especially when dealing with large datasets. Utilize indexes effectively to optimize the sorting performance.
• Database Specific Syntax: Be aware of specific syntax and functionalities offered by different databases, ensuring cross-compatibility and making full use of available features.

Additional Resources for Further Reading

• MySQL: ORDER BY Optimization
• PostgreSQL: ORDER BY Clause
• SQL Server: ORDER BY Clause (Transact-SQL)
• Oracle: ORDER BY Clause