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.
Suggested blogs
Recent blogs
