MSDN Documentation

Home > Tools > SQL Server Management Studio > Writing Queries > Sorting Data

Sorting Data in SQL Server Management Studio (SSMS)

This section guides you through using SQL Server Management Studio (SSMS) to sort data retrieved from your SQL Server databases. Proper data sorting is crucial for readability, analysis, and presenting information in a meaningful order.

Understanding the ORDER BY Clause

The primary mechanism for sorting data in SQL is the ORDER BY clause. You can use it to sort rows based on one or more columns in ascending (ASC) or descending (DESC) order.

Syntax:

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

Example 1: Basic Ascending Sort

To retrieve all customers and sort them by their last name in ascending order:

SELECT CustomerID, FirstName, LastName, City
FROM Customers
ORDER BY LastName ASC;

If you omit ASC, it defaults to ascending order.

Example 2: Descending Sort

To retrieve products and sort them by their price in descending order (most expensive first):

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY Price DESC;

Example 3: Sorting by Multiple Columns

To retrieve employees and sort them first by department in ascending order, and then by salary in descending order within each department:

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

The order of columns in the ORDER BY clause determines the sorting hierarchy.

Using SSMS to Apply ORDER BY

While you can directly write the ORDER BY clause in your T-SQL query, SSMS provides visual aids and shortcuts for common sorting tasks.

1. Manually Writing the Query:

This is the most common and flexible method. Open a new Query Editor window in SSMS (Ctrl+N), type your SELECT statement, and append the ORDER BY clause as shown in the examples above. Then, execute the query.

2. Using the Query Designer (Limited Sorting Options):

For simple queries, you can use the visual Query Designer. However, its sorting capabilities are less granular than direct T-SQL.

  1. Open a new Query Editor.
  2. Click the "Design Query in Editor" button on the toolbar (looks like a table with a pencil).
  3. Add tables and select columns.
  4. In the Query Designer pane, find the "Sort Order" column for the desired column and select Ascending or Descending from the dropdown.
  5. Note that the Query Designer will automatically generate the ORDER BY clause in the SQL pane.

The Query Designer is useful for quick visual table joins and basic selections but is not recommended for complex queries or fine-tuned sorting logic.

Sorting with Aggregations

The ORDER BY clause can also be used in conjunction with aggregate functions (like COUNT, SUM, AVG) to sort aggregated results.

Example 4: Sorting Aggregated Results

To find the number of customers in each city and display the results sorted by the count of customers in descending order:

SELECT City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY City
ORDER BY NumberOfCustomers DESC;

Sorting Based on Column Position (Not Recommended)

You can sort by the ordinal position of a column in the SELECT list. For example, ORDER BY 2 would sort by the second column selected.

SELECT FirstName, LastName
FROM Customers
ORDER BY 2 ASC; -- Sorts by LastName

While this syntax is supported, it is generally considered bad practice. If the order or number of columns in your SELECT list changes, your sorting logic will break. Always prefer sorting by column name for clarity and maintainability.

Key Takeaways

Mastering the ORDER BY clause is fundamental to effectively querying and presenting data using SQL Server Management Studio.