Richard Cotton shows how to sort SQL query results with ORDER BY, ASC, and DESC. Learn to sort by single or multiple columns, handle NULL values, and use different sort directions for each field. ORDER BY runs after WHERE and SELECT but before LIMIT in the query pipeline.
Chapters
Congratulations on making it this far. We'll now learn how to sort and group results to gain further insight. Sorting results means we want to put our data in a specific order. It's another way to make our data easier to understand by quickly seeing it in a sequence. Let's say we wanted to extract our three longest coats. If I close it where messy, it would take a long time to find. However, if we sorted our closet by garment type and length, we could quickly grab them. In SQL, the order by keyword is used to sort results of one or more fields. When used on its own, it is written after the from statement as shown here. Orderby will sort in ascending order. By default. This can mean from smallest to biggest or from A to Z. In this case, we have one query sorting the budget from smallest to biggest and a second query sorting the titles alphabetically. Our database contains film titles that start with symbols and numbers. These come before the letter A. We could also add the ASC keyword to our query to clarify that we are sorting in ascending order. The results are the same and our code is more readable. We can use the DESC keyword to sort on the results in descending order. This query gives us the film titles sorted by budget from biggest to smallest. However, our data contains a lot of null values. We can add a where clause beforeorder by to filter the budget field for only non null values and improve our results. Notice that we don't have to select the field we are sorting on. For example, here is a query where we sort by release year and only look at the title. However, it is a good idea to include the field we are sorting on in the select statement for clarity. Order by can also be used to sort on multiple fields. It will sort by the first field specified, then sort by the next, etc. To specify multiple fields, we separate the field names with a comma. The second field we sort by can be thought of as a tie breaker when the first field is not decisive in telling the order. Here is an example. Let's say we wanted to find the best movie in the first query. We are only sorting the films by the number of Oscar wins and getting a tie. We can break that tie by adding a second sorting field by seeing which film has the most wins and the highest IMDb score. We can also select a different order for each field we are sorting. For example, here we are sorting birthdate in ascending order and name in descending order. Order by falls towards the end of the order of execution. We already know coming in just before limit the from statement will execute first, then where, followed by select, order by, and finally limit SA.
