Microsoft SQL Server

In Microsoft SQL Server, the ORDER BY clause is part of the Data Query Language (DQL) and is used to sort the result set of a SELECT query based on one or more columns. Sorting helps in organizing the data in either ascending or descending order. For beginners, learning how to use the ORDER BY clause is important when you need to retrieve data in a specific order, whether by names, dates, or numerical values.

Below is a breakdown of how to use the ORDER BY clause in SQL Server, with examples and explanations.

1. Basic Syntax of ORDER BY

The basic syntax of the ORDER BY clause is:

SELECT column_name 
FROM table_name 
ORDER BY column_name [ASC | DESC];

• Replace column_name with the column you want to sort by.
• By default, ORDER BY sorts in ascending order (ASC), but you can specify descending order (DESC).
• Replace table_name with the actual name of the table.

Example:

SELECT ProductName, Price 
FROM Products 
ORDER BY Price ASC;

This query will return the product names and prices from the Products table, sorted by price in ascending order.

2. Using ORDER BY with Multiple Columns

You can also sort the result set by multiple columns by listing them in the ORDER BY clause. SQL Server will sort the rows first by the first column and then by the second column if there are ties.

SELECT ProductName, Category, Price 
FROM Products 
ORDER BY Category ASC, Price DESC;

This query will first sort the products by category in ascending order, and within each category, it will sort the products by price in descending order.

3. Sorting in Descending Order

To explicitly sort the result set in descending order, use the DESC keyword.

SELECT CustomerName, Country 
FROM Customers 
ORDER BY Country DESC;

This query will return the customer names and their countries, sorted by country in descending order (Z to A).

4. Using ORDER BY with WHERE Clause

You can combine the ORDER BY clause with the WHERE clause to filter and sort the data at the same time.

SELECT ProductName, Price 
FROM Products 
WHERE Price > 50 
ORDER BY Price DESC;

This query retrieves all products that have a price greater than 50 and sorts them in descending order based on the price.

5. Using ORDER BY with TOP

When limiting the number of rows using TOP, it is common to use the ORDER BY clause to control which rows are returned.

SELECT TOP 5 CustomerName, TotalSales 
FROM Customers 
ORDER BY TotalSales DESC;

This query retrieves the top 5 customers based on their total sales, ordered from the highest to the lowest sales.

6. Sorting with Aliases

You can also use column aliases in the ORDER BY clause if you have defined aliases in your query.

SELECT ProductName AS Product, Price AS Cost 
FROM Products 
ORDER BY Cost ASC;

In this query, Cost is an alias for Price, and the results are sorted by Cost in ascending order.

7. Best Practices for Using ORDER BY

1. Always Use ORDER BY for Consistent Results – SQL Server does not guarantee the order of rows without an ORDER BY clause. To ensure consistent results, always specify an ORDER BY when needed.

2. Use Indexes to Improve Sorting Performance – Sorting large datasets can be resource-intensive. Ensure that frequently sorted columns are indexed to improve query performance.

3. Be Mindful of Sorting on Large Data Sets – Sorting large datasets with multiple columns can affect performance. If possible, try to reduce the data before applying the ORDER BY clause by using filters (WHERE clause).

4. Combine TOP and ORDER BY for Paging – When implementing pagination in web applications, use TOP in combination with ORDER BY to retrieve a specific set of records in a controlled order.

By mastering the ORDER BY clause, you’ll be able to retrieve and organize data in a meaningful way, making your queries more useful and tailored to specific needs.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT *
FROM act_order
ORDER BY order_date ASC;

SELECT *
FROM act_order
ORDER BY order_date ASC;

SELECT *
FROM act_order
ORDER BY order_date DESC;

SELECT *
FROM prd_product
ORDER BY product_name ASC;

SELECT *
FROM prd_product
ORDER BY product_name DESC;

SELECT order_detail_id, product_id, quantity, unit_rate, (quantity * unit_rate) AS total_price
FROM act_order_detail
ORDER BY (quantity * unit_rate) ASC;

SELECT order_detail_id, product_id, quantity, unit_rate, (quantity * unit_rate) AS total_price
FROM act_order_detail
ORDER BY (quantity * unit_rate) DESC;

SELECT a.product_name, SUM(b.quantity) AS units_sold
FROM prd_product a
INNER JOIN act_order_detail b ON b.product_id = a.product_id
GROUP BY a.product_name
ORDER BY SUM(b.quantity) DESC;

SELECT a.first_name AS client_name, b.order_number
FROM org_client a
INNER JOIN act_order b ON b.client_id = a.client_id
ORDER BY b.order_date DESC;

SELECT client_id, birth_year, first_name, last_name
FROM org_client
ORDER BY birth_year DESC, first_name ASC, last_name ASC;

Example 1:

Let's understand the process of extracting data from employee table using ORDER BY sql clause. In this case, we aim to sort the data by employee's last name.

Example 1 - Raw data from employee table

Example 1 - Query

SELECT employee_number, first_name, last_name FROM org_employee ORDER BY first_name;

Example 1 - Query data mapping

In the depicted illustration, the data highlighted in green represents the selected information that aligns with our query criteria. Additionally, the red box signifies the column requiring alphabetical sorting prior to generating the ultimate output.

Example 1 - Query Output

Example 2:

Let's explore the procedure of retrieving data from the client's table using the SQL ORDER BY clause. In this example, our initial objective is to arrange the data in descending order based on the year of birth. Following that, we proceed to sort the output from step 1 based on the first name in ascending order.

Example 2 - Raw data from client table

Example 2 - Query

SELECT client_id, birth_year, first_name, last_name FROM org_client ORDER BY birth_year DESC, first_name ASC;

Example 2 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query. Red box indicates that as step1 we need to sort the data by year of birth in descending order.

Example 2 - Step 2

In the depicted image, the data has been arranged in descending order based on the specified year of birth, and an additional sorting is necessary based on the first name in ascending order.

Example 2 - Query Output