In this lesson, you'll learn how to sort your query results using an ORDER BY clause, and an additional technique for filtering the results of your SQL queries using the ORDER BY clause combined with the LIMIT clause.

You will be able to:

• Order the results of your queries by using ORDER BY (ASC & DESC)
• Limit the number of records returned by a query using LIMIT
• Write SQL queries to filter and order results

Once again we'll be using the Northwind database, containing records related to employees, orders, products, etc.

Below we import the relevant packages and connect to the database:

import pandas as pd
import sqlite3
conn = sqlite3.connect("data.sqlite")

In this lesson we'll focus on the products table:

pd.read_sql("""
SELECT *
  FROM products;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

By default, SQL query results will be returned in the order that they are in the database. This order may or may not have any relevance to your business case.

If, instead, you want to specify the sort order of the returned rows based on the values in the data, you use the ORDER BY clause.

A standard query with ORDER BY looks like this:

SELECT column(s)
  FROM table_name
 ORDER BY column_name (sort_order);

Let's say we wanted to order the products table by the product name:

pd.read_sql("""
SELECT *
  FROM products
 ORDER BY productName;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

When using ORDER BY, the default is to order in ascending order. This aligns with "alphabetical order" for text data like productName.

You can specify ASC (ascending) at the end of the ORDER BY clause and retrieve the same result:

pd.read_sql("""
SELECT *
  FROM products
 ORDER BY productName ASC;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Alternatively, if you wanted to use a descending order (opposite of alphabetical order for text data), you can use DESC:

pd.read_sql("""
SELECT *
  FROM products
 ORDER BY productName DESC;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

You are not limited to sorting by alphabetical order. For example, if you wanted to sort based on the length of the productDescription, that would look like this:

pd.read_sql("""
SELECT productName, length(productDescription) AS description_length
  FROM products
 ORDER BY description_length;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

You can also sort by something without selecting it. Here is a query that has the same order as the query above, but doesn't actually select the description length:

pd.read_sql("""
SELECT productName
  FROM products
 ORDER BY length(productDescription);
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

You are also not limited to sorting by one column at a time. Typically this is most useful if some rows have repeated values in a given and you want a "tiebreaker" value from another column.

For example, this query sorts by productVendor, then productName:

pd.read_sql("""
SELECT productVendor, productName, MSRP
  FROM products
 ORDER BY productVendor, productName;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

The ordering of the columns in the ORDER BY clause is important. The earlier ones should be the ones requiring a "tiebreaker", not the later ones. If we switch around the order of the previous query, for example, you'll notice that it doesn't really look sorted by productVendor at all:

pd.read_sql("""
SELECT productVendor, productName, MSRP
  FROM products
 ORDER BY productName, productVendor;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Another way of thinking about which column should come first after ORDER BY, is that the fewer unique values the column has, the earlier it should appear.

To find out how many unique values there are in a column, we can use the DISTINCT keyword in addition to COUNT. Below are some examples:

pd.read_sql("""
SELECT COUNT(DISTINCT productVendor) AS num_product_vendors,
       COUNT(DISTINCT productName) AS num_product_names
  FROM products;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

This result aligns with what we observed earlier. There are many more unique product names than product vendors, so it makes sense to sort by vendors first, then names.

Sometimes when you use ORDER BY, you'll get an odd result like this. We are trying to sort by quantityInStock to get a list of products from the least to most number in stock right now:

pd.read_sql("""
SELECT productName, quantityInStock
  FROM products
 ORDER BY quantityInStock;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

What is 136 doing there? What happened?

Let's take a closer look at the first 10 results (using pandas):

pd.read_sql("""
SELECT productName, quantityInStock
  FROM products
 ORDER BY quantityInStock;
""", conn).head(10)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

The problem is that we assumed that these values were stored as numbers, but apparently they are actually strings. So they are being sorted in alphabetical order, so "1249", "136", and "1452" are correctly ordered based on comparing the first two characters ("12" comes before "13", which comes before "14").

We want to treat these as numbers, not strings, so we can use CAST as part of the ORDER BY clause.

Specifically since these are whole numbers, we'll cast them as the INTEGER data type in SQLite:

pd.read_sql("""
SELECT productName, quantityInStock
  FROM products
 ORDER BY CAST(quantityInStock AS INTEGER);
""", conn).head(10)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

That looks right for the first 10 rows! Let's check the full dataset:

pd.read_sql("""
SELECT productName, quantityInStock
  FROM products
 ORDER BY CAST(quantityInStock AS INTEGER);
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Looks good!

LIMIT is used to determine the number of records you want to return from a dataset.

A standard query with LIMIT would be:

SELECT column(s)
  FROM table_name
 LIMIT number;

You can use LIMIT to specify that you only want a certain number of results, in whatever order they appear in the database. This is kind of like using .head() to look at the first part of a dataframe:

pd.read_sql("""
SELECT *
  FROM orders
 LIMIT 5;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

This approach would also be relevant if you find yourself in a situation where the database is too large, so you can only select a certain number of rows at a time.

LIMIT becomes much more powerful and useful when combined with ORDER BY.

Sometimes you are less interested in results that are filtered based on an absolute value like "greater than or equal to 5" or "starting with 'M'", and more interested in results that are filtered based on a relative value like "oldest", "youngest", "longest", "shortest", etc.

To do this kind of filtering, you first need to use ORDER BY to sort the values, then LIMIT to select only the top value(s) based on your sorting.

For example, if we wanted to select the 10 orders with the longest comments to start a customer service audit:

pd.read_sql("""
SELECT *
  FROM orders
 ORDER BY length(comments) DESC
 LIMIT 10;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

You can also combine LIMIT with WHERE if you want to apply two different kinds of filters (absolute and relative) at the same time:

pd.read_sql("""
SELECT *
  FROM orders
 WHERE status = "Cancelled"
 ORDER BY length(comments) DESC
 LIMIT 10;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

As you can see in the above query result, note that the number after LIMIT does not guarantee that you will get that number of results. That is only the upper limit of the results you might get. It appears that there are only 6 canceled orders, so even though we limited the results to 10, we only got 6.

If there were 0 canceled orders, we would get 0 results, regardless of what LIMIT says.

If we loosen the query restriction on the status column (producing more results), we can see the LIMIT take effect again:

pd.read_sql("""
SELECT *
  FROM orders
 WHERE status IN ("Cancelled", "Resolved")
 ORDER BY length(comments) DESC
 LIMIT 10;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

LIMIT can be especially useful for finding the oldest and newest records in a dataset with date/time data.

For example, who were the first five unique customers to place an order using this system?

pd.read_sql("""
SELECT DISTINCT customerNumber, orderDate
  FROM orders
 ORDER BY orderDate
 LIMIT 5;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

Of the orders that have not been shipped and have not been canceled, what are the 10 newest based on order date?

pd.read_sql("""
SELECT *
  FROM orders
 WHERE shippedDate = ""
   AND status != "Cancelled"
 ORDER BY orderDate DESC
 LIMIT 10;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

What is the order that took the longest to fulfill, and how long did it take?

pd.read_sql("""
SELECT *,
       julianday(shippedDate) - julianday(orderDate) AS days_to_fulfill
  FROM orders
 WHERE shippedDate != ""
 ORDER BY days_to_fulfill DESC
 LIMIT 1;
""", conn)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

conn.close()

In this lesson, you expanded your SQL knowledge by learning how to sort and limit the results of your query using ORDER BY and LIMIT. You also saw how to customize the sorting behavior as well as incorporate DISTINCT and WHERE keywords to create more-sophisticated queries.