SQL Server Order By Clause By Practical Examples

In SQL Server, "Sort" refers to the operation of arranging data in a specific order. Sorting data can be useful when you want to present the data in a particular way, or when you need to perform certain calculations or analysis that depend on the order of the data.

How do You Sort Items In SQL?

In SQL Server, you can use the "ORDER BY" clause to sort data in a specific way. This clause is typically used in conjunction with a "SELECT" statement to retrieve data from one or more tables.

Sort By One Or More Columns - Syntax

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], ...

SQL Server Versions And Sorting

There may be some differences in sorting behavior between different versions of SQL Server, although these are generally minor and unlikely to cause issues for most applications. Some versions sort the query results in an ascending order by the collation default. Here are a few differences to be aware of:

• Unicode sorting: SQL Server 2008 introduced new sorting rules for Unicode character data that are designed to better match the expectations of users in different regions. These new rules are used by default in SQL Server 2008 and later versions, but can be disabled if necessary.

• Collation behavior: The collation setting used in a SQL Server database can affect the way that data is sorted. In some cases, different collation settings may be used in different versions of SQL Server, which could cause differences in sorting behavior. However, this is generally a rare occurrence.

• Query optimizer changes: SQL Server's query optimizer, which is responsible for generating efficient execution plans for SQL statements, may change between versions. While these changes are unlikely to affect sorting behavior directly, they could have an impact on the performance of sorting operations.

Sample Data For Use Below Customers Table And Employee Table

Here's the T-SQL code to create the employees table:

CREATE TABLE employees (
    [First Name] VARCHAR(50),
    [Last Name] VARCHAR(50),
    [Age] INT,
    [State Name] VARCHAR(50),
    [State Abbreviation] VARCHAR(2)
);

Here's the T-SQL code to insert the data into the employees table:

INSERT INTO employees ([First Name], [Last Name], [Age], [State Name], [State Abbreviation])
VALUES
    ('John', 'Smith', 28, 'California', 'CA'),
    ('Sarah', 'Johnson', 28, 'New York', 'NY'),
    ('Alex', 'Brown', 42, 'Texas', 'TX'),
    ('Emily', 'Davis', 26, 'Florida', 'FL'),
    ('David', 'Lee', 31, 'Massachusetts', 'MA'),
    ('Michael', 'Wilson', 47, 'Pennsylvania', 'PA'),
    ('Jessica', 'Garcia', 24, 'Arizona', 'AZ');

Best Sorting features Of Sorting In SQL Server:

Sorting The FirstName Column In Descending Order And Ascending Order:

Here's the T-SQL code to select all the rows in ascending or descending order sorted alphabetically.

SELECT * 
FROM employees
ORDER BY [First Name] ASC;

SELECT * 
FROM employees
ORDER BY [First Name] DESC;

Sql Sort By Multiple Columns

You can sort data on multiple columns in SQL Server by specifying multiple column names in the ORDER BY clause. This is helpful when you want to sort data first by one column, and then by another column within the groups created by the first sort.

First, let's create the "aircraft" table with some sample data:

CREATE TABLE aircraft (
    aircraft_id int,
    manufacturer varchar(50),
    model varchar(50),
    range_in_km int,
    seating_capacity int
);

INSERT INTO aircraft (aircraft_id, manufacturer, model, range_in_km, seating_capacity)
VALUES 
(1, 'Boeing', '737', 5500, 189),
(2, 'Boeing', '777', 9700, 440),
(3, 'Airbus', 'A320', 6100, 150),
(4, 'Airbus', 'A330', 11800, 277),
(5, 'Embraer', 'E190', 3700, 114);

Now let's say we want to retrieve a list of aircraft sorted first by manufacturer, then by seating capacity, and finally by range in descending order. We can use the following SELECT statement with an ORDER BY clause: The following query will return rows in the descending order

SELECT *
FROM aircraft
ORDER BY manufacturer, seating_capacity, range_in_km DESC;

This will return the following result:

As you can see, the data is first sorted by manufacturer (Airbus, Boeing, Embraer), then by seating capacity (ascending order within each manufacturer group), and finally by range in descending order (within each seating capacity group).

Sorting On Expressions:

SQL Server allows you to sort data based on expressions that you create using mathematical operators, functions, or any other operations that produce a value. This can be useful when you want to sort data based on calculations or transformations of the underlying data.

Suppose we want to retrieve a list of aircraft sorted by the ratio of their range to seating capacity. We can use an expression in the ORDER BY clause to calculate this ratio and sort the data accordingly. Here's the SQL statement to achieve this:

SELECT *, range_in_km / seating_capacity AS range_to_capacity_ratio
FROM aircraft
ORDER BY range_to_capacity_ratio DESC;

In this example, we're selecting all columns from the "aircraft" table and also calculating a new column called "range_to_capacity_ratio" using an expression that divides the "range_in_km" column by the "seating_capacity" column. We're then sorting the results in descending order by the value of this new column.

Here's the result of the above query:

Sorting On Case-Sensitive Or Case-Insensitive Basis:

SQL Server allows you to specify whether sorting should be done in a case-sensitive or case-insensitive manner. This is particularly useful when you're sorting text data.

Suppose we want to retrieve a list of aircraft sorted by their manufacturer name, but we want to sort the data in a case-insensitive manner. We can use the COLLATE keyword in the ORDER BY clause to specify a case-insensitive collation. Here's the SQL statement to achieve this:

SELECT *
FROM aircraft
ORDER BY manufacturer COLLATE SQL_Latin1_General_CP1_CI_AS;

Here's the result of the above query:

As you can see, the data is sorted in ascending order by the "manufacturer" column, with manufacturers whose names start with the same letters appearing next to each other. The case of the letters is ignored in this sorting, so "Airbus" and "airbus" are considered equivalent.

If we want to sort the data in a case-sensitive manner, we can use a different collation that distinguishes between uppercase and lowercase letters. For example, we could use the "SQL_Latin1_General_CP1_CS_AS" collation instead. Here's the SQL statement to achieve this:

SELECT *
FROM aircraft
ORDER BY manufacturer COLLATE SQL_Latin1_General_CP1_CS_AS;

This will produce the following result:

As you can see, the data is again sorted in ascending order by the "manufacturer" column, but this time the case of the letters is taken into account, so "Airbus" and "airbus" are considered different.

Sort A Result Set By A Column That Is Not In The Select Column List

Suppose we want to retrieve a list of aircraft models and their seating capacity, sorted by the range in kilometers of the aircraft. However, we don't want to include the "range_in_km" column in the select list. We can achieve this by including the "range_in_km" column in the ORDER BY clause, even though it's not in the SELECT list. Here's the SQL statement to achieve this:

SELECT model, seating_capacity
FROM aircraft
ORDER BY range_in_km;

In this example, we're selecting only the "model" and "seating_capacity" columns from the "aircraft" table, and sorting the results in ascending order by the "range_in_km" column.

Here's the result of the above query:

Sorting with NULL Values:

SQL Server provides options for how to handle NULL values when sorting data. You can either specify whether to treat NULL values as the highest or lowest values in the sort order, or you can specify a custom sort order for NULL values.

Here's an example of an UPDATE statement to set the Age value to NULL for the employee with First Name 'David':

UPDATE employees
SET [Age] = NULL
WHERE [First Name] = 'David';

To sort NULL values, SQL Server has two sorting options: NULLS FIRST and NULLS LAST. By default, SQL Server sorts NULL values as NULLS FIRST, which means that NULL values are sorted before non-NULL values. Here's an example of a SELECT statement to show how SQL Server sorts NULL values:

SELECT [First Name], [Last Name], [Age], [State Name], [State Abbreviation]
FROM employees
ORDER BY [Age] ASC NULLS LAST;

This code retrieves all the rows from the employees table and sorts them by the Age column in ascending order, using the ORDER BY clause. The NULLS LAST option specifies that NULL values should be sorted after non-NULL values. Therefore, in this case, the rows with non-NULL ages will be sorted first in ascending order, and then the row with a NULL age will be sorted last.

These are some of the best features of sorting in SQL Server that make it a powerful tool for working with data. By using these features, you can easily sort data in various ways to suit your needs and gain insights from your data.

Additional Resources