Explains how to sort query results in ascending or descending order
- [Instructor] Earlier we ran this query, and it returned us all the Penelopes there. So it's giving us all the actors with the first name of Penelope but the last names seem to be in quite a random order. They're certainly not alphabetical. We've got G and then P, and then C and then M. In fact, what's happening is that they're given to us in the same order they appear in the table. Rows in a table are typically in the order they were entered. So data entered later is lower down in the table so Penelope Monroe was probably inserted into the actor table after all of these three before it.
So this is no good to us. We've just decided that we don't care when the name was added to the database. We want to sort our results alphabetically on last name. And to do this we use a new command which is order by. And we'll say order by last name, and run that. By default, when you use order by, the results are arranged in ascending order.
The database doesn't care, by the way, whether this is in a number or a date. It identifies what kind of data it's dealing with, and does the best it can with the data that it has. To avoid confusion, I generally stipulate that I want things to be ascending using ASC like so. And if you wanted them descending, it would be DESC. It's always temping to write DSC there, but it's DESC, like so.
Now let's just look at the order of the query. Notice that we've put the filtering clause where before the order by clause. SQL requires you to write your queries in a particular order. It's quite unfussy about extra spaces or random carriage returns, but it is picky that the query is written in the right order, so this is the standard setup for a select statement. Select fields from the table, where if there is any filter, and then order by followed by ASC or DESC.
Every select statement we now look at is going to be a variation on this theme.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries