Ordering query results

1. Ordering query results

Often when we are building queries,

2. Order by clauses

we will want to order the return data alphabetically, numerically, or by dates. It is easy to do so in SQLAlchemy by using the order_by() method on any statement, which by default orders from lowest to highest. In the case of strings, this means in alphabetical order.

3. Order by ascending

Initially when I selected the state field from all the records in a prior query, I got Illinois back as the first result. I really want the states in alphabetical order. To do that built a select statement for the state column of the census table. Then I append an order_by clause on my select statement that targets the state column. After executing that statement, I can see that the results now start with Alabama.

4. Order by descending

If we want to sort from highest to lowest, we can do so by wrapping a column in the order_by() clause with the desc() function, which is short for 'descending' and will reverse the natural sort order and make it highest to lowest. You'll get to practice using the desc() function in the interactive exercises that follow this video.

5. Order by multiple

Often we might want to order by one column such as district and then within each district order by age. We can accomplish this by passing multiple columns to the order_by() method and it will fully sort the first column, then within the rows that have matching values for the first column it sorts by the second column and so on until all sort columns are satisfied.

6. Order by multiple

In this example, I want to get all the states in alphabetical order. Then within each state, I want to get the genders in alphabetical order as well. To demonstrate this, I've already performed a select statement to get the state and sex from the census table where it's in the wrong order. Next, we build a select statement for the state and sex columns. Then, we append an order by clause that targets the state and sex columns. Now I can see that after executing the query, we get Alabama and Female as the first state and sex combo in our result set.

7. Let's practice!

Let's practice this with our census data.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.