The ORDER BY clause is used to sort the result set of a query in either ascending (smallest to largest) or descending (largest to smallest) order. By default, SQL Server does not guarantee the order of results unless you explicitly use this clause.

1. Basic Syntax

The ORDER BY clause always appears at the very end of your SELECT statement.

  • ASC: Sorts in ascending order (Default).
  • DESC: Sorts in descending order.

Example:

SQL
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC; -- Most expensive products first

2. Sorting by Multiple Columns

You can sort by more than one column. SQL Server will sort by the first column listed, and then sort by the second column for any rows that have the same value in the first.

Example:

SQL
SELECT FirstName, LastName, Department
FROM Employees
ORDER BY Department ASC, LastName ASC; 
-- Grouped by Department, then alphabetized by last name within each dept.

3. Sorting by Column Position or Alias

You don’t always have to type the full column name. You can use its position in the SELECT list or its alias.

  • By Position: ORDER BY 2 (Sorts by the second column in the SELECT list). Note: This is generally discouraged as it makes code harder to read if columns change.
  • By Alias: Using a custom name defined in the SELECT.

Example:

SQL
SELECT FirstName + ' ' + LastName AS FullName
FROM Employees
ORDER BY FullName; -- Sorting by the alias

4. Handling NULL Values

In SQL Server, NULL values are treated as the lowest possible values.

  • In an ASC sort, NULL values appear at the top.
  • In a DESC sort, NULL values appear at the bottom.

Note: Unlike some other database engines (like PostgreSQL), SQL Server does not have a native NULLS LAST syntax. To force NULLs to the bottom of an ascending list, you must use a CASE expression.

Example (Force NULLs to bottom):

SQL
SELECT ProductName, Color
FROM Products
ORDER BY 
    CASE WHEN Color IS NULL THEN 1 ELSE 0 END, 
    Color ASC;

5. Sorting by Non-Selected Columns

You can sort a result set by a column that is not included in your SELECT list, provided it exists in the tables defined in the FROM clause.

Example:

SQL
-- You see names and prices, but they are sorted by the Hidden ID
SELECT ProductName, UnitPrice
FROM Products
ORDER BY ProductID;

6. Sorting with Expressions and Functions

You can use functions within the ORDER BY clause to create custom sorting logic.

Example:

SQL
-- Sort by the length of the product name
SELECT ProductName
FROM Products
ORDER BY LEN(ProductName);

Categorized in:

SQL Server,