3

How to sort results using SQLite

Order

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

Once you have gone through the lesson, please be sure to do the exercises.  These and the examples that follow are based on the sample database.  Be sure to get a copy so you can follow along.

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 employees by last name you could state

SELECT FirstName, LastName
FROM Employees
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 Employees
ORDER BY LastName ASC;

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

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName DESC;

DESC stand for Descending.

ORDER BY 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 OrderDetails by Quantity and UnitPrice enter

SELECT ProductID, Quantity, UnitPrice
FROM OrderDetails
ORDER BY Quantity, UnitPrice;

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

SELECT ProductID, Quantity, UnitPrice
FROM OrderDetails
ORDER BY Quantity ASC, UnitPrice DESC;

ORDER BY 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 OrderDetails, perhaps you would like to know who has 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 ProductID, Quantity, UnitPrice
FROM OrderDetails
ORDER BY UnitPrice * Quantity;

To make it more clear, lets also display the TotalPrice

SELECT ProductID, Quantity, UnitPrice, UnitPrice * Quantity AS TotalPrice
FROM OrderDetails
ORDER BY UnitPrice * Quantity;

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

SELECT ProductID, Quantity, UnitPrice, UnitPrice * Quantity AS TotalPrice
FROM OrderDetails
ORDER BY TotalPrice;

LIMIT 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 orderdetail items, you could enter the following query into SQLite

SELECT ProductID, Quantity, UnitPrice, UnitPrice * Quantity AS TotalPrice
FROM OrderDetails
ORDER BY TotalPrice
LIMIT 5;

The LIMIT 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 customer names, cities, and stated, sorted by customer’s cities.
  2. Write a statement to select employee 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 employee last names.
  4. Select the last two names to appear in a sort of employee last names.

Answers to the Exercises

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 an MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He is loves helping others learn SQL.

Click Here to Leave a Comment Below 3 comments