Ordering query results

Introduction to Databases in Python

Jason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Order by clauses

Allows us to control the order in which records are returned in the query results
Available as a method on statements order_by()

Order by ascending

print(results[:10])

[('Illinois',), ...]

stmt = select([census.columns.state])

stmt = stmt.order_by(census.columns.state)

results = connection.execute(stmt).fetchall()

print(results[:10])

[('Alabama',), ...]

Order by descending

Wrap the column with desc() in the order_by() clause

Order by multiple

Just separate multiple columns with a comma
Orders completely by the first column
Then if there are duplicates in the first column, orders by the second column
Repeat until all columns are ordered

Order by multiple

print(results)

('Alabama', 'M')

stmt = select([census.columns.state, census.columns.sex])

stmt = stmt.order_by(census.columns.state, census.columns.sex)

results = connection.execute(stmt).first()
print(results)

('Alabama', 'F')
('Alabama', 'F')
...
('Alabama', 'M')

Let's practice!

Introduction to Databases in Python