9

Getting Started With SQL Server: 2. Sort Your Query Results

In this lesson you are going to explore how to sort your query results by using SQL’s ORDER BY statement.  Using this phrase allows us to sort our result in ascending or descending order.  In addition you can limit your query to a specified number of results.

The lesson’s objectives are to:

  1. learn how to sort on one column in ascending or descending order
  2. sort on two or more columns
  3. sort on a calculated field
  4. limit our query to a specified number of results

Important! Please follow along and do the examples in your database.  If you haven’t already done so, sign up for my Guide to Getting Started with SQL Server.  You get instructions on how to install the free tools and sample database.

SORT using ORDER BY

In order to sort a query’s results use the ORDER BY clause.  This clause comes after the FROM clause and is where you specify columns or expression to use to order your data.

When using the ORDER BY clause the select statement takes the form

SELECT columns FROM table ORDER BY columns;

So if you wanted to sort people by last name you could state

SELECT   FirstName,
         LastName
FROM     Person.Person
ORDER BY LastName

In addition you can specify the direction to sort.  Unless specified, all sorts are in ascending order (smallest to largest) and can be explicitly specified using the ASC keyword

SELECT   FirstName,
         LastName
FROM     Person.Person
ORDER BY LastName ASC

To sort by LastName in descending order you would issue the statement

SELECT   FirstName,
         LastName
FROM     Person.Person
ORDER BY LastName DESC

DESC stand for Descending.

Sort  More than One Column

You can also order by more than one column.  Just separate the columns you wish to sort with a comma.  If you wanted to sort PurchaseOrderDetail by OrderQty and UnitPrice enter

SELECT   PurchaseOrderID,
         OrderQty,
         UnitPrice
FROM     Purchasing.PurchaseOrderDetail
ORDER BY OrderQty, UnitPrice

You can also specify the sort order, that is whether the columns are sorted in ascending or descending order.  In this sample the PurchaseOrderDetails are sorted in descending order by price within quantity.

SELECT   PurchaseOrderID,
         OrderQty,
         UnitPrice
FROM     Purchasing.PurchaseOrderDetail
ORDER BY OrderQty ASC, UnitPrice DESC

 Order  using A Calculated Value

So far you have learned to sort on one or more columns, but did you know you can sort on a expression?  For instance, consider PurchaseOrderDetail, perhaps you would like to know who has the largest orders.  Of course you can sort by Quantity or UnitPrice, but what about TotalPrice?  That column doesn’t exist in the table, be we learned how to create it as a an expression in our last lesson.  Can we sort on this?

Sure!

Check out the following, and look closely at the ORDER BY clause, there you’ll see where it’s ordered by TotalPrice (UnitPrice * Quantity)

SELECT   PurchaseOrderID,
         UnitPrice,
         OrderQty
FROM     Purchasing.PurchaseOrderDetail
ORDER BY UnitPrice * OrderQty

To make it more clear, lets also display the TotalPrice

SELECT   PurchaseOrderID,
         UnitPrice,
         OrderQty,
         UnitPrice * OrderQty AS TotalPrice
FROM     Purchasing.PurchaseOrderDetail
ORDER BY UnitPrice * OrderQty

And here is where aliasing, or renaming fields, can help.  Here UnitPrice * OrderQty is aliased as TotalPrice in the SELECT clause.  Now that’s done, we can simply refer to TotalPrice when specifying the sort order.

SELECT   PurchaseOrderID,
         UnitPrice,
         OrderQty,
         UnitPrice * OrderQty AS TotalPrice
FROM     Purchasing.PurchaseOrderDetail
ORDER BY TotalPrice

Get TOP Results

The last item I would like to go over with you is being able to limit the number of sorted results returned from a list.  This makes it really easy to return the “first ten” or “top ten” items in a search.  For instance if you want to know the top five PurchaseOrderDetail items, you could enter the following query into SSMS

SELECT   TOP 5 PurchaseOrderID,
               UnitPrice,
               OrderQty,
               UnitPrice * OrderQty AS TotalPrice
FROM     Purchasing.PurchaseOrderDetail
ORDER BY TotalPrice

The TOP keyword limits our search to the first five rows.  You can limit by other numbers of course, in fact you can also limit by any number resulting from an expression.

Tip!  To get the last five rows of a result, such as the smallest five orders, just order your result in descending order.

Exercises

It’s important to practice! Use the sample database to answer these questions.

  1. Write a statement to select Employee NationalIDNumbrer, MaritalStatus, BirthDate and JobTitle, sorted by BirthDate.
  2. Write a statement to select Person first and last names ordered by the upper case equivalent of their last name.   Remember:  We discussed UPPER in the previous lesson.
  3. Select the first two names to appear in a sort of Person LastNames.
  4. Select the last three names to appear in a sort of Person LastNames.

Answers are Here!

Congratulations!  You just learned how to use the select command to query a database.  More tutorials are to follow!

Remember!  I want to remind you all that if you have other questions you want answered, then post a comment or tweet me.  I’m here to help you.

What other topics would you like to know more about?

 

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Vivek kumar says:

    SELECT PurchaseOrderID,
    OrderQty,
    UnitPrice
    FROM Purchasing.PurchaseOrderDetail
    ORDER BY OrderQty, UnitPrice

    Here which parameter(OrderQty, or UnitPrice) for sorting will have higher priority?

    QrderQty UnitPrice
    1 1
    2 2
    3 4
    4 3

    For the above example, which row, #3 or row #4 will come first? And why

    Thanks for your nice tutorial it drive me learning SQL

    • Hi,

      The precedence for sorting is from left to right. That means that all the values are first sorted by the left most columns, and then working right, sorted by those columns’ values.

      Example
      Given the following table named Example with columns A and B with the following data

      A B
      -----
      1 2
      2 3
      1 3
      2 5
      1 6
      3 4
      4 3
      3 6
      4 5

      The statement
      SELECT A, B
      FROM EXAMPLE
      ORDER BY A, B

      Results in the following sorted result
      A B
      -----
      1 2
      1 3
      1 6
      2 3
      2 5
      3 4
      3 6
      4 3
      4 5

    • To directly answer your question, the results are first sorted by OrderQty and then by UnitPrice.

  • Dewei says:

    Hi, Kriz
    Thanks for this nice article.
    I met an error during doing the exercise 4 “Select the last two names to appear in a sort of Person LastNames.”

    Using AdventureWorks2012, if I run
    SELECT TOP 2
    Person.FirstName + ‘ ‘ + Person.LastName AS FullName
    FROM
    Person.Person
    ORDER BY
    LastName DESC

    Then I got two identical People:
    Michael Zwilling
    Michael Zwilling

    To remove duplicated select result, I used the DISTINCT keyword in this form:
    SELECT DISTINCT TOP 2 Person.FirstName + ‘ ‘ + Person.LastName AS FullName FROM Person.Person ORDER BY LastName DESC

    But I got an error

    “ORDER BY items must appear in the select list if SELECT DISTINCT is specified.”

    That is confusing, could you kindly tell me the right command?
    Thanks in advance!

    Dewei

    • Hi Dewei,

      I’m glad you liked the article. The DISTINCT keyword isn’t necessary to arrive at the answer. Actually you had it! The last two people in the list have the same names. Perhaps, because of the way the database is setup, they are both an employee and customer. Anyways, it is a good introduction to the fact that data isn’t perfect.

      I do see that my example does have a confusing result, since people may think they are getting duplicate rows. So to help with this I changed the exercise to get the top three items.

      Now If you did want to get the last two distinct names in the list, this is how you do it…


      SELECT DISTINCT TOP 2
      LastName
      ,Person.FirstName + ' ' + Person.LastName AS FullName
      FROM Person.Person
      ORDER BY LastName DESC

      To fix your statement I just added the LastName to the Select list since the error message said I had to do so… :)

      The error message was “ORDER BY items must appear in the select list if SELECT DISTINCT is specified.” which basically translates in English to, “Yo, if you’re going to use the DISTINCT keyword and you want to sort your list, you need include what you’re sorting by in the distinct’s select list.”

      Make Sense?

  • Demond says:

    I did not receive the results expected in Exercise #2 with your posted answer. The last name data was not in upper case. I got it to work by using the code below. Did I do something wrong?

    Select [FirstName],upper([LastName])
    From [Person].[Person]
    Order by lastname

    • Hi,

      The answer you gave is good if you want to report the LastName in upper case; however, the question asked to order the results by the uppercase equivalent of the last name. To do that you would ORDER BY upper(LastName).

  • Nams says:

    In Get TOP Results example:
    SELECT TOP 5 PurchaseOrderID,
    UnitPrice,
    OrderQty,
    UnitPrice * OrderQty AS TotalPrice
    FROM Purchasing.PurchaseOrderDetail
    ORDER BY TotalPrice

    It should be ORDER BY TotalPrice DESC?
    I think DESC is missing. When it is just ORDER BY TotalPrice I am getting the last 5 or the smallest 5 order details.

  • >