The ORDER BY clause is used to sort the result set of a query by one or more columns.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
  • ASC → Sort ascending (smallest to largest, default).
  • DESC → Sort descending (largest to smallest).

Step 1: Sorting by a single column

Suppose we have the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000

Sort by Salary ascending:

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary ASC;

Result:

FirstName LastName Salary
Jane Smith 4500
John Doe 5000
Mike Brown 6000

Sort by Salary descending:

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

Result:

FirstName LastName Salary
Mike Brown 6000
John Doe 5000
Jane Smith 4500

Step 2: Sorting by multiple columns

You can sort by more than one column. SQL Server will sort by the first column, and if there are duplicates, it uses the next column.

Example: Sort by Department ascending, then Salary descending

SELECT FirstName, LastName, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;

Result:

FirstName LastName Department Salary
Mike Brown IT 6000
John Doe IT 5000
Jane Smith HR 4500

Here, employees are sorted by Department alphabetically, and within each department, by Salary descending.

Step 3: Using column positions

Instead of column names, you can use the column’s position in the SELECT list

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY 3 DESC; -- 3 means the third column in SELECT (Salary)

Result: Same as sorting by Salary DESC.

This method is less readable, so it’s recommended mainly for quick queries.

Step 4: Sorting with expressions

You can also sort by calculated columns or expressions:

SELECT FirstName, LastName, Salary, Salary * 12 AS AnnualSalary
FROM Employees
ORDER BY AnnualSalary DESC;

Result:

FirstName LastName Salary AnnualSalary
Mike Brown 6000 72000
John Doe 5000 60000
Jane Smith 4500 54000

Step 5: Using ORDER BY with TOP

ORDER BY is often used with TOP to get the highest or lowest values:

SELECT TOP 2 FirstName, Salary
FROM Employees
ORDER BY Salary DESC;

Result:

FirstName Salary
Mike 6000
John 5000

Returns the top 2 highest salaries.

Categorized in:

SQL Server,