ORDER BY Clause

The ORDER BY clause in SQL Server is used to sort the result set of a query by one or more columns. Sorting can be done in ascending (ASC) or descending (DESC) order. By default, the ORDER BY clause sorts in ascending order if no explicit order is specified.

Basic Syntax

SELECT column1, column2, ...

FROM table_name

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

• Scenario 1: Simple Ordering

Example: Sorting Employees by Last Name

SELECT EmployeeID, FirstName, LastName

FROM Employees

ORDER BY LastName;

• This query sorts the employees by their last names in ascending order.

• Scenario 2: Ordering in Descending Order

Example: Sorting Products by Price in Descending Order

SELECT ProductID, ProductName, Price

FROM Products

ORDER BY Price DESC;

• This query sorts the products by their price in descending order.

• Scenario 3: Ordering by Multiple Columns

Example: Sorting Orders by CustomerID and then by OrderDate

SELECT OrderID, CustomerID, OrderDate

FROM Orders

ORDER BY CustomerID, OrderDate;

• This query sorts the orders first by CustomerID and then by OrderDate within each CustomerID.

Master SQL Server with Hands-On Training at TrainingHub.io - Enroll Now!

• Scenario 4: Ordering by Expressions

Example: Sorting by Calculated Values

SELECT ProductID, ProductName, Price, Quantity, (Price * Quantity) AS TotalValue

FROM Products

ORDER BY TotalValue DESC;

• This query sorts the products by their total value (calculated as Price * Quantity) in descending order.

• Scenario 5: Ordering with NULL Values

Example: Sorting with NULL Values First

SELECT EmployeeID, FirstName, LastName, DepartmentID

FROM Employees

ORDER BY DepartmentID ASC NULLS FIRST;

• In SQL Server, you can control the ordering of NULLs using specific expressions in more complex scenarios, although NULLS FIRST and NULLS LAST are not standard in SQL Server directly.

• Scenario 6: Ordering with CASE Statements

Example: Custom Sorting Order

SELECT EmployeeID, FirstName, LastName, Role

FROM Employees

ORDER BY

CASE

WHEN Role = 'Manager' THEN 1

WHEN Role = 'Developer' THEN 2

WHEN Role = 'Analyst' THEN 3

ELSE 4

END;

• This query sorts the employees by their role in a custom order: Manager first, then Developer, Analyst, and others.

• Scenario 7: Ordering in Aggregated Queries

Example: Sorting Aggregated Results

SELECT CustomerID, SUM(TotalAmount) AS TotalSales

FROM Sales

GROUP BY CustomerID

ORDER BY TotalSales DESC;

• This query calculates the total sales for each customer and then sorts the customers by total sales in descending order.

• Scenario 8: Top-N Queries with ORDER BY

Example: Top 5 Highest-Priced Products

SELECT TOP 5 ProductID, ProductName, Price

FROM Products

ORDER BY Price DESC;

• This query selects the top 5 highest-priced products.

Important Notes

1. Order of Evaluation: The ORDER BY clause is evaluated after the SELECT statement, including any GROUP BY or HAVING clauses.

2. Aliases in ORDER BY: You can use column aliases defined in the SELECT statement in the ORDER BY clause.

3. Index Usage: Proper indexing can significantly improve performance for queries using the ORDER BY clause.

The ORDER BY clause is fundamental for organizing query results in a readable and meaningful way, and it is widely used in data presentation and reporting.

TrainingHub.io provides flexible IT online courses in Canada, perfect for busy professionals.