Sorting results with ORDER BY - SQL Server Tutorial

- [Narrator] In this lesson we're going to take a look at some samples of using the ORDER BY clause in some queries for SQL Server. We're going to use our Sorting_with_ORDER_BY SQL script, and we're going to execute a few different types of queries so we can gain a better understanding of how we're actually working with the ORDER BY clause. The first query that we're going to execute here is just a straightforward one. There's no ORDER BY clause used at all. I just want to run this one to give us a reminder of how the records actually are returned from a result set based on whatever the database engine has decided it's going to do for returning them. Let's go ahead and run this first query. And we see that we have selected the BusinessEntityID, FirstName, MiddleName, and LastName from the Person table. And you'll note that the records are returned and there is no specific order. We get 19,972 rows not even close to being ordered by BusinessEntityID or alphabetically by FirstName, MiddleName, maybe LastName possibly, but again, there's no guarantee on the results that you return. Let's look at another query. In this one here, we're actually going to use an ORDER BY clause. So you can see the ORDER BY keywords right here, and after the ORDER BY keyword, we tell SQL Server which column, or which attribute from the database, we want to generate the ORDER BY. And SQL Server will then use that after it returns all of the records and it will order the result set based on that specific field. Now you'll also notice that I'm not telling it whether I want it in ascending order or descending order, so in other words from small to large or from large to small. And the reason being is to show you that the default ORDER BY is ascending. So let's go ahead and run this query. And we see that we return the BusinessEntityID, FirstName, MiddleName, LastNames, the same as we did in the previous query. But this time, we've told it to ORDER BY the BusinessEntityID, and by default, it will do that in ascending order. So, note the difference in what we get for a result set. Now, if we were to scroll through, we would see that, indeed, we are ordered in an ascending value by the BusinessEntityID. Let's look at another one, here. In this case what we're going to do is we're going to change the order of the records. We're going to use the LastName in this case as the ORDER BY column. But this time, I'm going to explicitly tell it I want it in descending order. So take note of that, because if you want your result sets returned in a descending order, for example, you're returning, maybe, salaries of employees, and you want to see who the highest paid employees are, you might want to use descending, because it puts the highest values at the top. Let's run this query. Again, the same three columns, or the same four columns, BusinessEntityID, FirstName, MiddleName, Last, from the Person table. We're going to order it by the LastName in descending order. So if we take a look at the values now, we see that certainly not sorted by the business entity ID. Nor are we by the FirstName or MiddleName. But it looks like we indeed have sorted in a descending order by LastName, because we are starting with the letter Z. Let's take a look at changing the order of the records. And we're going to do it alphabetically based on the FirstName, but then we're going to throw a little curve ball in here, because we're also going to sort on the LastName. So this is designed to show you how ORDER BY can use multiple columns, but the logic behind the ordering of the records being returned gives priority to the first column name that we list, in this case FirstName. But it will still provide some kind of sorting functionality using the LastName as well. Let's run this and see what the results look like. So we can see that we are sorted by the FirstName, because we have an ascending order as well, because we have the As first. You'll see that MiddleName is listed, but it's not really in any sorted order. But then you'll also see that within the sorted results, we do have a sorting of the LastName. So let's see what that looks like. So we have two FirstNames of "A.", and then we have LastNames sorted, and you'll notice that these are in alphabetical order. So within the results sets for these FirstNames, so we have Aaron here on a number of cases, those are all together, and then the sorting took place on the LastName afterwards. So Adams comes before Alexander, which comes before Campbell, et cetera. So you can get an idea of how that ORDER BY using two different attributes, or two different field names changes the way the result set comes in. Priority is given to sorting on the FirstName, and then within the results of those FirstNames, we will sort the remaining values by the LastName. To give you an idea of the difference, let's go ahead and just execute a query that does the same thing as before, but, let's just focus this time on the ORDER BY FirstName. So if we execute the results, we can see that we have indeed ordered by FirstName, but you'll notice that the LastNames within those FirstName results are not sorted alphabetically. So that's the difference between the two. Here, we're just focused on sorting by FirstName. SQL returns the remaining columns in whatever order makes sense to it, and we have no way of understanding how that works. But there is no order to these LastNames. Much as there was, in this one, because we explicitly told SQL, "I want to ORDER BY FirstName and LastName." So we can see the order of the LastName within the FirstName result set. Okay, let's look at another. In this case what we're going to do is demonstrate how SQL Server also has the concept of ordinal positions of the fields within your SELECT clause. So looking at this query, we have SELECT FirstName, MiddleName, and LastName, and again, it's coming from the Person table. In our ORDER BY clause, this time, rather than indicating the column names, such as we did above, which was FirstName and LastName, we're using the ordinal positions. So we're saying ORDER BY 1, 3. And what that basically means is sort by FirstName first, and then LastName, same as we had in that previous query. Let's run this and see what the results look like. Once again, we have definitely sorted by FirstName. And because we've indicated LastName using the ordinal position 3, we've also sorted by that LastName within the result set of the FirstName. So again, you can use the column names, or field names, I've been using that interchangeably throughout, or you can use the ordinal positions. Let's look at another example here. This time what we're going to do is we're going to bring back the FirstName, MiddleName, and LastName, but we're not going to include the BusinessEntityID in the visible output. So, in other words, we're not including that column in the SELECT statement. But we're going to order by the BusinessEntityID. Let's run this and see if we get the results that we're looking for. So we can see that we have Ken and Terri and Roberto. These are ordered by the BusinessEntityID, but, sometimes you might look at this and go, "This is a little disturbing to me "because I don't know that it's necessarily "ordered by the BusinessEntityID. "I have no way of knowing that for certain, "because that column is not included in the output. "So, did it actually return them "in the order that I was looking for?" And to understand that or to maybe answer that question, let's take a brief look again here at this order that we see, where we have Ken J. and Terri Lee and Roberto and Rob. Let's go back up to the top, where we did indeed record or reorder by BusinessEntityID, and let's see if we get the same results. Indeed, if we come down here and look, Ken J., Terri Lee, Roberto, Rob. So we can see that we don't necessarily have to include a column in our ORDER BY clause in order for that to actually impact the output of the results. Okay, for our final example, we're going to run a query that is going to attempt to order the records alphabetically. So we're going to use the ORDER BY clause on the MiddleName. And because we haven't specified descending, it will attempt to do so in an ascending fashion. But we're going to run into a small glitch, and the small glitch comes in the form of null. And if you remember from earlier in the course, we talked about how SQL Server handles nulls. It's an undefined type, therefore, there is no way to compare it to something, which means that as we take a look at trying to order these results, or as SQL tries to order the results, it needs to put them in some kind of a sorting order. So it has to compare, each of the MiddleNames and return them in the proper order. But, again, because we can't compare an undefined, we don't know what the value of that null is, we can't compare it against the letter A or the letter L. So what's going to happen in this case? Well, let's run it and see. Run the query. And we note that what SQL Server does is, it says, "Okay, I cannot compare null values, "so I cannot sort null values "in a logical sense when trying to compare to other values, "so what I'm going to do "is I'm going to put all of your null values first." So that's what happens. All of the null values show up ahead of the actual MiddleName values. So if we scroll far enough down then we can see that we start to actually sort by the middle name or the middle initial. But because null is that little problem child, SQL Server says, "I will put that ahead of all your other sort results." So that's a run down on different types of uses of the ORDER BY clause within SQL Server, and how we can use it to impact or change the way the results are displayed after SQL Server runs the query for us.