Hey everyone, welcome back. In this video, part 15 of topic three in our database class, I'm going to show you how you can sort the results of your SQL queries by using the order by statement. So we've seen plenty of examples where we get results, and one of the things that is often very useful for us to do is to sort the results that we get from our SQL select statements. And the SQL provides us with the capability to do that by means of the order by statement. We can use order by to sort our results. And of course, when we sort things, we follow sorting rules. And those sorting rules are language specific. So in the English language, for example, we have a medical order in which letters appear in the alphabet. And that will vary because different languages use different symbols. Some languages are not letter based, but are more word or sound based. And in that case, they use a different set of rules for sorting things. But to every human written language, there is a way of sorting things. And because of that, we can capitalize on the order by capabilities that SQL provides to sort things. Now when we sort things, it can be an ascending sort. That is the lower values are on the top of the list and the larger values are on the bottom. So for example, one, two, three, four, five is the numbers one through five sorted in ascending order. Increasing values as we proceed. And we can contrast that with a descending sort, in which case the larger values appear at the top of the list. And we proceed to smaller and smaller values as we work down the list. So the same five numbers in descending order would be sorted as 5, 4, 3, 2, 1. And of course, the same rules apply to whatever sort of alphabet we're using. Let's see some examples. In this example here, we are taking a look at a request where we're telling the database to give us everything. This is all columns out of the employee table, but we want the results to be sorted by employee name. Now if we just specify something like this order by employee name by default, that will be an ascending sort. So the default sort behavior is ascending. That is smallest to largest, but we can invert that and create a descending sort if we like by using the letters D E S C for a descending. So this is the same results, except that the employee names here would be sorted in descending order. If you'd like to remove any and all ambiguity, you can always specifically tell the database that you want things sorted in an ascending order by using the letters A S C as we see here. So instead of saying employee name descending, I can use employee name ascending. I could put it up here, for example, and it would achieve the same result. So just to eliminate any and all possibility, any ambiguity, you can do that. The other thing that you can do with order by is that you can do layers of sorting as necessary. So if you, for example, want to sort first by an employee's last name and then by first name, you can do that easily. Let's see some examples. So let's, oh, we know we have some employees in here. Here are all of our employees. And you can see we have, I don't know, like here we have a couple of employees that have the last name, same last name. I catenous and primrose both have the last name ever dean. So this gives an opportunity to illustrate these things. First, let's do a sort, I'll just sort it by the last name. Now this will do an ascending sort by last name. You can see Abernathy and then Crane and then the two ever deans, Flickerman, Hawthorne, Heavens B, etc. So this is alphabetical order according to the English rules of sorting. And if we had different data in here, so example of this were written in one of the Japanese methods or Chinese methods or Korean methods or it's another language like Arabic or far CE or Hebrew or something like that where we use different sets of symbols. I would follow those languages sorting orders. Okay. But we can see here that we have our results sorted by last name. Now if we take a look at the two ever deans, these two here, you'll see that primrose currently appears first followed by catenous. Now if we wanted to sort by last name and then by first name, we would expect those records to be reversed. So if I wanted to do that, all I have to do is just add the additional column that I want to use as the secondary sort. So this was last name, we can put in first name. And in this case, if we run it, we'll see that because these two employees have the same last name, we then decide that we want to further sort them according to their first name. In this case, we can see now catenous appears before primrose, but we have complete control over this. If I want to sort first, let me just first illustrate that if I append a SC to these, we will get exactly the same results because by default, if I do not specify a sort order, the database will assume that we want an ascending sort. So if I do specify ascending, we should get exactly the same results as we see here. And I run this again, and we can see that indeed we do get exactly the same results. So now let's play around with this a little bit. So let's sort by last name ascending and then we'll do first name descending. And so here's a descending sort and if I execute this, we'll see that now primrose is back on top of catenous. Okay, because we're now sorting first by our last names in ascending order and then by first names in descending order. And of course I can play around with these things so I could do I don't know sort by last name in descending order. And then first name. And then we could see our alphabetically largest last names appear at the top of the list and we work our way down to the alphabetically smallest ones. So Abernathy which starts with the letter a is at the bottom of the list since this is now a descending sort. And we can sort on anything. It doesn't matter if it's a text based attribute like a first name or last name or we can sort by numbers. So maybe I want to order them by the employee ID. And in that case you'll see we could are 12345678.910 or we couldn't reverse that. Right, get it in reverse order something like that. Another thing that we can do is we could sort by dates. Right, so here's an interesting question for you. If I sort by higher date, what do we expect to peer at the top of the list the oldest records or the newest ones interesting question. Well, when we sort by dates the oldest records appear at the top of the list. You can see here I can find that this person. And Katniss was my first employee issue is hired on the first of January 2018. And we can see this goes up to my most recently hired employee which was on the 15th of November 2021. And of course we can reverse sort on dates as well. So in this case our most recently hired employee appears at the top. And the employee that we hired first that is the farthest into the past appears at the bottom of the list. Just a little trick here that I'll show you and then we'll proceed. If I wanted to say just get my most recently hired employee. One of the interesting tricks that we can do is we can if we run this we'll see that we get our list of employees. And we know that our most recently are our first employee the employee that we hired first in our company appears at the top of the list. Because we are sorting by higher date in ascending order. But if I want to return just that employees name. I can do something like this top one. And then when I run it I'm just going to get that very top record in the results set. So that's an interesting goal trick. Or if I wanted to get the record for our most recently hired employee. Let's switch that to a descending sort. And we can see we get Plutarch Hevensby who was the employee that we hired most recently. So any sort of sorting options you can do you can sort as many layers deep as you need. I illustrated two with last name and first name but you can sort three layers deep or four layers deep. And you can mix and match on ascending or descending sort. So you can imagine this is very good for I don't know like reporting purposes generating reports and stuff like that.