SQL - Order By Clause

The ORDER BY clause in SQL Server is used to sort the result set of a query based on one or more columns. It allows you to specify the order in which the rows should be returned.

The basic syntax of the ORDER BY clause is as follows:

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

In the above syntax, column1, column2, ... represent the columns you want to sort by, and ASC or DESC specifies the sort order for each column. ASC stands for ascending (default), which sorts the data in ascending order, and DESC stands for descending, which sorts the data in descending order.

For example, let's say you have a table called "Customers" with columns such as "CustomerID," "FirstName,", "LastName.", "City" and "Age" with the following data:

Here are some examples of using the ORDER BY clause to sort this "Customers" table:

1. Sort by a Single Column (Ascending):

To sort the table in ascending order by a single column, you can use the ORDER BY clause followed by the column name. For example, to sort by the "LastName" column:

SELECT * 
FROM Customers
ORDER BY LastName;

Result:

By default, it sorts in ascending order.

2. Sort by a Single Column (Descending):

To sort in descending order, you can use the DESC keyword after the column name:

SELECT * 
FROM Customers
ORDER BY Age DESC;

Result:

3. Sort by Multiple Columns:

You can also sort by multiple columns. Let's provide a clear example of sorting by multiple columns using the "Customers" table with different data. Suppose we have the following "Customers" table:

Now, let's sort the table by two columns: first by "City" in ascending order and then by "Age" in descending order:

SELECT * 
FROM Customers
ORDER BY City ASC, Age DESC;

Result:

In this example, the table is first sorted by the "City" column in ascending order. Within each city, it is further sorted by the "Age" column in descending order. This results in the correct order where customers within the same city are sorted by age in descending order.

It's important to note that the ORDER BY clause is applied after the WHERE clause in a SQL query. So, you can combine the WHERE and ORDER BY clauses to filter and sort the data based on your requirements.

4. 'WHERE' clause with the 'ORDER BY' clause

Let's use the "Customers" table again and demonstrate how to combine the WHERE clause with the ORDER BY clause. We'll filter the customers by a specific condition and then sort them based on a column.

Suppose we want to retrieve and sort customers who are aged 30 or older. Here's the SQL query:

SELECT * 
FROM Customers
WHERE Age >= 30
ORDER BY Age DESC;

In this query:

• 'SELECT *': Selects all columns.
• 'FROM Customers': Specifies the table to query, which is the "Customers" table in this case.
• 'WHERE Age >= 30': Filters the rows, selecting only those where the "Age" is greater than or equal to 30.
• 'ORDER BY Age DESC': Orders the filtered result set by the "Age" column in descending order.

Here's the result of this query:

In this result, only the customers who are 30 years or older are included, and they are sorted by age in descending order.