How to use ORDER BY to sort an SQL result set

When you create an SQL SELECT statement, the data probably won’t be returned or sorted in the order you want, so the ORDER BY clause is used to control the sort order of the results returned by the query.

The ORDER BY clause is really easy to use and can allow you to sort an SQL resultset in ascending or descending order, using one or more columns. Here’s a quick guide to using it.

I’m connecting to a MySQL Docker container into which I’ve imported a MySQL SQL database dump based on the Online Retail Dataset. We’ll be using SQLAlchemy and PyMYSQL to connect to the database using Pandas.

Install and load the packages

Since we’re querying MySQL from within a Jupyter notebook, as many data scientists do in their daily work, you’ll first need to open Jupyter and install the required pymysql and sqlalchemy packages using the Pip Python package manager. Then, you’ll need to import pandas and the create_engine module from sqlalchemy.

!pip3 install pymysql
!pip3 install sqlalchemy

import pandas as pd
from sqlalchemy import create_engine

Create a database connection

Next we’ll construct a database connection string to pass to create_engine(). This tells SQLAlchemy we’re connecting to a MySQL database using the PyMYSQL driver, and are using the username root and the password ***** and are connecting to the MySQL server on port 3306 of IP address 172.17.0.3. The final argument tells create_engine() to connect to the ecommerce database.

engine = create_engine('mysql+pymysql://root:*****@172.17.0.3:3306/ecommerce')

SELECT FROM an SQL table

First we’ll create a simple unordered query using a regular SQL SELECT statement. This orders the data according to whatever index is present on the database, which might not match our preferred formatting.

df = pd.read_sql_query("SELECT * FROM order_items", con=engine)
df.head()

Use ORDER BY to sort by a single column in descending order

To sort the SQL resultset we can append the ORDER BY clause to the end of the SELECT statement and define the column by which we want to sort and the sort order, which can either by DESC for descending order, or ASC for ascending order.

Our query SELECT * FROM order_items ORDER BY qty DESC will sort the resultset by the qty column in descending order, so the highest quantities are shown first and the values decrease down the list.

df = pd.read_sql_query("SELECT * FROM order_items ORDER BY qty DESC", con=engine)
df.head()

Use ORDER BY to sort by a single column in ascending order

To sort items in ascending order, you simply replace the DESC argument with ASC. Our query below will sort items from the lowest unit_price to the highest.

df = pd.read_sql_query("SELECT * FROM order_items ORDER BY unit_price ASC", con=engine)
df.head()

Using ORDER BY to sort a column in alphabetical order

The ORDER BY column_name ASC statement also works for non-numeric or categorical data, such as strings, allowing you to put them in alphabetical order from A to Z, or vice versa if you use DESC.

df = pd.read_sql_query("SELECT * FROM order_items ORDER BY description ASC", con=engine)
df.head()

Using ORDER BY to sort by multiple columns

Finally, you can also sort by multiple columns by passing them as a list of comma separated values. The below query will sort the resultset first in ascending order of the description name (i.e. in alphabetical order) and then in ascending order of the order_date, so the earliest purchases are first.

query = """
SELECT 
    * FROM order_items 
ORDER BY 
    description ASC,
    order_date ASC
"""

df = pd.read_sql_query(query, con=engine)
df.head()

Matt Clarke, Thursday, April 14, 2022