ORDER BY Clause

Introduction

The ORDER BY clause is used to sort the result of a query by one or more columns. This clause helps organize the data in either ascending or descending order based on the values in the specified columns. By default, if not specified, it will sort the results in ascending order.

Why Use the ORDER BY Clause?

• Sorting Data: It allows you to organize query results in a specific order, making it easier to analyze or present the data.
• Improved Data Visualization: Data ordered by specific criteria can be more readable and provide better insights in charts or reports.
• Critical for Pagination: When implementing pagination in applications, ordered results help you display consistent subsets of data across different pages.

Syntax

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

• column1, column2, ...: The columns to sort the data by.
• ASC: Sorts in ascending order (default).
• DESC: Sorts in descending order.

Tables

Customers

Orders

Example: Ordering Data by a Single Column

SELECT order_id, total_amount
FROM Orders
ORDER BY total_amount;

In this query, the ORDER BY clause sorts the Orders by their total_amount (ascending order).

Ordering Data by Multiple Columns

You can also sort the data by more than one column. For example, if you want to sort by last_name first, and then by first_name in case two Customers have the same last name:

SELECT first_name, last_name
FROM Customers
ORDER BY last_name, first_name;

`

Here, if two Customers have the same last name (Brown), the first_name column is used as a tiebreaker to determine the order.

Descending Order

To sort the Orders table by total_amount in descending order (highest to lowest):

SELECT *
FROM Orders
ORDER BY total_amount DESC;

The DESC keyword is used to sort the total_amount column from the highest to the lowest value.

Combining Ascending and Descending Orders

You can combine both ascending and descending orders on different columns. For example, to order the Customers by last_name in ascending order and by date_created in descending order for Customers with the same last name:

SELECT first_name, last_name, date_created
FROM Customers
ORDER BY last_name ASC, date_created DESC;

In this case, the ORDER BY clause sorts last_name in ascending order, but if two Customers share the same last name, their date_created is sorted in descending order.

Sorting by Column Aliases

Sometimes, when you use column aliases (renaming columns using AS), you can also sort by the alias name instead of the original column name.

SELECT first_name + ' ' + last_name AS Fullname
FROM Customers
ORDER BY Fullname;

This query will order the results by the Fullname alias, which is the combination of first_name and last_name.

Conclusion

The ORDER BY clause in MS SQL is a versatile and essential tool for ordering query results, whether you're dealing with single or multiple columns, expressions, or handling NULL values. Mastering this clause is important for building clean, organized, and meaningful query results.

Asked in Interview

• How does the ORDER BY clause work in SQL?
• What is the default sorting order in the ORDER BY clause?
• Can you use the ORDER BY clause with column aliases?
• What happens when you use ORDER BY on columns with NULL values?
• Can you use the ORDER BY clause with expressions?
• How can you implement custom sorting when handling NULL values?