Moving on along to our next topic.
I guess that's okay.
Moving on along.
I'll keep using that.
Our next topic here is Order By.
So as a subtitle tells you, it's how we can sort our results, which is something that we'll want to
do.
Hopefully you see why that's useful when you're trying to understand things.
It's good to be able to sort your data.
So with our books, for instance, right now we're getting them all in the order that they were inserted
in.
And maybe we want to do things like find the best selling books or the worst selling books or find the
most active users on our website or the least active users or something similar.
Find the most upvoted posts or the post that has the most comments on our blog or whatever we're working
with.
It's important to be able to sort things.
And again, the way we do that is by using order by.
So let's take a look at an example.
Here's a query that's going to select all the author last names from books.
So up until this point, nothing is new.
Select author last name from book or books.
But after that we add on this little thing order space by.
That's something that trips me up, by the way, that there is a space.
Sometimes it feels like it should be one word or there should be an underscore.
But there's a space or space by.
And then what do we want to order it by?
By the last name.
So let me show you.
If we go here, we just do a select author l name from books.
This should be review what we get.
We get them in the order they were inserted.
So it's not alphabetical at all.
So if we want to do alphabetical, select author l name from books order by and then we have to say
author l name just like that.
And as you can see now, we have Carver up top all the way down to Steinbeck, and you should notice
it is sorted by default in ascending order.
So from C or A technically down to Z and it's actually alphanumeric as well.
So if we do titles, for instance, let's just do select title from books.
We've got a title that has an A at the beginning, but we also have 10% happier.
So if we do this select title from Books Order by title, you'll see we get the 10% happier first before
the A's and all the way down to W.
So as I mentioned, it is ascending by default.
So if you don't specify an order, it just assumes you want ascending.
But of course, we can change that, as my slide says, by just providing one thing at the end.
So everything up until this point is the same select author l name from books order by author l name
space DSC.
And in my opinion, this is also something that's kind of confusing because it's DSC.
But then the abbreviation for Ascending is AC, which is only three letters versus four.
But that's just my opinion.
So let's try it.
Now, if we recall what we had select author l name from books order by author L name.
There we go.
This is the original in default ascending order.
But if we add DSC, you can see it's reversed.
Now it goes from Steinbeck down to Carver.
Or if we do the same thing with our title and we just add on DSC, it now goes from white noise down
to 10% happier.
And of course order by will also work with numbers.
So it's pretty common that we want to sort things numerically, whether it's the number of orders or
the sort, the highest priced items or like I said earlier, sort the blog posts by whichever have the
most comments, most to least or something like that.
Those are numeric values.
So to do that, it's the exact same syntax.
All that we change is what we're ordering by.
So in this case released here, which is.
So in this case released here, which is an integer select release here from books order by release
here.
So let's try it out.
Let's do a select release here from books without the order by and there in all sorts of crazy order.
I think our lowest one is 1945 all the way up to 2017, which is that Lincoln in the Bottle Book definitely
recommend it still.
So if we recall that line and just add on order by release dear.
There we go.
And by default, it's also ascending 1945 up to 2017.
We could reverse it with DSC.
Just like that.
And just to prove to you you can do this, you can be explicit with ASC ascending and get the same order
if you left it off.
And not to harp on this too much, but I just.
I don't like that this is four letters versus three letters.
It's just it should be consistent.
So if we leave it off, though, we get the same results.
So that's the basics of order by.
Another thing I'd like to highlight here is that we've been doing things like select release year order
by release here where it's matched, what we're selecting is matching how we're ordering it.
You don't have to do that.
So let's say we wanted to get let's say we want title and year released here, that is and pages or
not.
And I want all three of those from books order by and we'll do release here so I can do that and it's
going to order them by the release here but still gives me all the data.
So it's not like it has to match or it could even leave released.
You're out of this entirely.
I don't have to select it, but I can still order by so I could select title and pages in order by the
release here.
And you can see the first one we get is Cannery Row.
And the first one we got here is Cannery Row from 1945 and the last one was Lincoln in the Prado 2017.
Last one is Lincoln in the Bardo 2017, even though we don't have the year actually printing out.
So that's all I want to show there, that you don't have to be ordering by something that you're selecting
or that it doesn't have to be the only thing that you're selecting.
So here's another little quirk of order by if you take a look at this query, select title, author
f name, author l name.
And I put this on two line tier, not because it's correct to do that, but just so that it's easier
to read for you.
So select title author f name and author l name from Books Order by two.
What do you think the two refers to?
Well, in this case, it's actually just a shortcut.
So we don't have to rewrite author f name.
So if we're selecting title author F name and author L name, this will be one.
This is two and this is three.
And it's a shortcut for me to say ordered by two author f name.
So let's copy it over.
Try running it and you can see the results we get back.
They're not sorted alphabetically here, even though sort of looks like it because we have 10% happier
and then a hologram for the King ten and then a but it's not the rest of the way is not.
But you can see over here we've got Dan first and then David, Dave, Data, Don, Frieda, blah, blah,
blah, down to Raymond.
And if we wanted to sort instead by last name, we just changed it to three again because author l name
is the third thing that we're selecting.
And now we've go from Carver down to Steinbeck and likewise you can change it to one to sort by title,
and we could also switch it over to descending.
And that works.
And if we did switch the order here just to show the order does matter.
So if I do author l name comma title if I want to sort by title, I would need to do two now and now
they're sorted by title.
So there's one last thing and I made a slide to remind myself about it, which is this here?
So what we can actually do is sort by or order by two different columns, which seems like something
that you wouldn't really do that often.
And truth be told, it's not.
But I just want you to know that you can.
So to show you what I'm talking about, let me first do a select and I'll make a new file for this one
as well.
Just because we're going to have a little bit of code, let's do new file.
I'll just call it order underscore by SQL.
And if we do a select author f name, comma, author, l name from books just like that, save it and
we need to do our source order by SQL.
Just like that.
And I forgot the directory refining selections slash.
So when I do that, we get first and last names are not sorted.
So now if I do an order by author last name.
And I redo it.
Everything looks good.
We have our authors sorted alphabetically.
Carver, Gaiman, HARRIS.
But then that's where we run into our problem, which is.
HARRIS And it's not really a problem.
But if we wanted our authors sorted alphabetically, technically, when there's two authors that have
the same or the same last name and we want the entire author name to be sorted alphabetically, well
then we need to go to the first name.
And in this case, Frieda Harris is in front of Dan Harris, even though DD comes before F.
So we want Dan to come before Frieda.
And that's where this comes in handy.
We can sort after we sort by last name, then sort by first name.
So it's not going to redo everything.
It's going to keep basically the initial sort and then sort first name if there's any conflicts.
So we can just add that in as a comma, author, f name and save.
Now if you just focus here, so we have Frieda Harris and then Dan Harris.
And if I source that and run it, you can see it just switched.
So everything else is the same.
Carver first, Steinbeck last, but now Dan Harris comes before Frieda Harris.
All right.
So that's actually it to order by saw a lot there is a little bit dense.
I apologize but order by is useful like I said you'll be sorting lots of things and it helps you understand
your data, especially when we're talking about sales data or site specific data.
There's a lot of insights that you can get just by putting it in an order.