Another thing you may come across with order by looks like this ordered by and then a number.
What on earth does that mean?
It's basically a shorthand syntax that says ordered by the second column that we're selecting.
So if we're selecting title author, F name and author L name order by that second thing, I guess it
saves you a little bit of typing so you don't have to type author f name.
Let me show an example of that.
So let's do select title is kind of long, but let's do book ID and then author F name, author L name
and let's do pages from books.
Okay, So I want to sort by pages.
One option, of course, is just to say order by pages.
I mean, that's not that much work.
And what did I mess up here?
Oh, I somehow got rid of the table name from books ordered by pages, and that works fine.
But the syntax I'm showing you here is we can replace pages with a number.
This would be the first column, second, third.
And this is the fourth column.
I'll sort by or order by four and we get the same result null 176 up to 634.
And then, of course, I could say descending and go the other direction.
If I want to sort by author first name, I could say order by two.
I'm honestly not really a fan of this.
It's just not as clear.
If you look at this, you then have to go reference over here.
So maybe it saves you a couple of characters of typing, but it also clutters your or it makes your
code less meaningful, less obvious if someone else is looking at it or just future you is looking at
it.
But as you can see here, when I do two, we're sorting by author F name.
So the first name in descending order.
So from Z to A, in our case Raymond to Dan and NULL, the next thing I'll show you with order by is
that we can actually order by multiple columns.
So when we do this, we'll sort by one column first and then secondly, we'll sort by the second column
or third or fourth and fifth.
So it's not saying sort them together, it's sorting by this and then this column.
So I'll show you an example where this might make sense.
Let's do a select author l name and then about released year and then finally title from books.
So here's our result.
Nothing is ordered by yet, but now let's say I want to order by author last name.
I want to see them sorted by the author's last name.
And so now we see all the Carver books together, all the Eggers books together, Foster Wallace, Gaiman.
But then within each group, they're right where the author last name is the same.
There could be an additional sort.
Maybe I want them also to be sorted by the release year, because right now if we look at Eggers, we
have 2012, 2013 and then 2001.
Or if you look at Gaiman, we have 2016, 2001, 2003.
So secondarily, I could add on after author l name in my order by right here, I'll say then order
by released year.
So this now will look very similar, at least on the left hand side where we see the last names.
But what has changed is the order of the rows within each one of these chunks.
So within the Eggers grouping we now have 2001 2012, 2013.
Again, compare that to what we had before.
We still had the three eggers rows together, but their order within that grouping was different.
It was 2012 or yeah, 12, 13 and one, right?
And now it is 112 and 13.
And then same thing with Neil Gaiman, 2001 2003 2016 and we could sort by a third value like by title.
It's just that we don't really have any con or any duplicate values for release here.
So there's no real way for me to show you that it works, but you could keep going.
And we can also change direction.
So I could say I want to actually sort released here.
Descending.
So DSC so we're still sorting ascending for author last name that happens first all the rows are going
to be ordered by author last name alphabetically A to Z.
And then after that we will sort by release gear and this is descending.
So 2013 most recent, then 12 and then 2001 for Dave Eggers or for Carver, we have 89 and then 81.
So that's how you can sort by multiple values, one column and then another column afterwards.
And then one last thing that I should show you about order by is that we can also order by columns that
aren't necessarily part of the table, but our results are values that we've asked SQL for.
For example, remember that we can do things like can cat select can cat?
And then let's take author, f name, comma, author, LL name, and then I guess I'll put a space in
there.
And let's call that as.
Full name.
Or just how about just author as author from books.
So we're we're not sorting anything, but we're just getting these full names using Kit-Kat.
I can then order by author if that's the name that I gave my column there that I retrieved.
Right.
This is not part of the database.
It's not a part of the table.
There's no column called Author, but I have Alias this information called it author using the as word,
the as word using as.
And then I can order by author, as you can see.
There we go, Dan Harris, all the way down to Raymond Carver.
And I think that's good enough.
I hope you get the picture there by using an alias.
I can then reference that name and order by later on.
So there's a lot to order by.
But at the end of the day, usually I use it just to order by alphabetically, but more commonly numerically.
And occasionally I want something to be descending instead of the default ascending.
And then somewhat rarer you can order by multiple columns one after another, as we saw here.