When writing a SELECT statement, the records are returned in an unsorted order. To ensure that the records being returned are sorted in a specific order, we would use the ORDER BY statement. (Note: There are some performance issue concerns when using the ORDER BY statement that will be discussed in a future blog post.)

SELECT ProductID, ListPrice, Color
FROM Production.Product
WHERE COLOR IS NOT NULL
ORDER BY Color ASC, ListPrice DESC

In the code above, we have selected the ProductID, ListPrice, and Color columns from the Production.Product table in the AdventureWorks database. We have included a WHERE clause to only return the records that have a value in the Color column. Finally, we are introducing the ORDER BY statement to first sort the Color column in ascending order and then the ListPrice in descending order. When this code is executed, we receive the following results.

Notice that the records have been sorted by the Color column first in ascending order and then for each color the records are then sorted by ListPrice in descending order.

Next, let’s reverse the order and sort by ListPrice first in descending order and then by Color in ascending order. (Note: If a sort order is not provided it will default to ascending so the ASC clause is not required. )

SELECT ProductID, ListPrice, Color
FROM Production.Product
WHERE COLOR IS NOT NULL
ORDER BY ListPrice DESC, Color

This time we will notice that the results are sorted first by ListPrice in descending order and then by the Color column.