Okay, so we're resuming where we left off.
We want to try and find the entire row or maybe just the title of the book that has the longest page
count.
So we can find the page count at 634.
But of course, let's say we don't know the number because if we know it's 634, I could just write
a query that says, you know, select star from books where page pages equals six, three, four, but
that defeats the purpose.
We see the answer here.
But I want to know if I don't know the number exactly, I have 10,000 plus books.
How do I retrieve the entire row or just the title of that book?
So there's a couple of options.
And one of those options is to use order by which we already know we can order by pages and then limit.
So let's do select, let's just do title and pages from books, order by and then pages.
And then let's do Descending DSC OC.
So we're going from highest pages down to lowest pages and then I would just limit it to one.
And there we are, we found the whole row or in this case just the title and pages, but we have access
to whatever we want of the book that has the highest number of pages.
That is one option.
So here's that in the slides.
The second option I'll show you is quite a bit different.
It involves using something called a sub query, which is really the main focus of this video.
I want to show you that we can do sub queries.
So a sub query is what it sounds like.
It's a query within a larger query.
We surround it with parentheses and this tells my SQL run this first that will be evaluated to some
in this case some number, and then this query will run.
So let's try it.
Let's try writing it out.
Let's do a select title, a comma, pages from books, and then I'm going to say where pages equals,
but I don't know what it's going to be equal to.
I mean, I know it's 634, but that's cheating.
I'm going to say where pages equals the result of running select and I'm going to get an error immediately
because in at least in this, what do you call it, SQL workbench.
I get some nice little error checking as I'm typing some syntax checking.
It says you can't have select right there, but if I surround it with parentheses, it'll shut up eventually.
And it's complaining I don't have a semicolon, but it's it's totally happy now because I have those
ends.
It is a valid query.
It's a sub query within the main query, but otherwise I can't just go writing select if I don't have
prints.
Those are very important.
So select and then I want to find the max of pages from books.
And this will run first.
That's going to be replaced eventually with six, three, four.
And then this query is very straightforward, pretty simple.
Let's try running it.
I'll just run it over here and we get the same exact answer.
The Amazing Adventures of Kavalier and Clay.
Okay.
So does that mean that these are identical solutions?
Not really.
They both work.
If we know for sure that there is only one maximum, meaning that there's only one row that has 634
pages.
But let's say that I have a second one in there.
Insert into books.
Let's see.
Let's go with author.
Actually, let's just keep it simple.
Title and pages and title.
Here is going to be my life in Words.
And the number of pages is going to be 634.
So now we have a duplicate of at least the number of pages.
There's two books with that number of pages, and if I try my sub query solution, this one here, we'll
get both of them, which is great.
Maybe that's what we want.
We get both of them because all that we said is find all of the books where pages is equal to 634 and
if we ran that ourselves, we would get multiple rows, just like if I said find all the books that
were released in 2001.
If there's multiple, we get multiple.
We're not limiting it.
But with the other solution involving order by and limit, of course we only get one because we set
the limit to one.
And yes, I could set the limit to two, but that requires me knowing that there's more than one, more
than one matching row that has that number of pages.
Otherwise, if I had run this a couple of minutes ago, I would see 634 and then some other smaller
number of pages for whatever the runner up book is.
So there is a difference.
But if we're just looking for one, we know there's one.
Then you don't need to bother with a subquery necessarily.
But it's good to know that there is a subtle difference between how they will work.
So just to recap sub queries, let's try one more example.
Why don't we find the title of the book that was released earliest?
So that would involve we could do this select and then we can find the minimum released here.
We already saw this.
I think it's 1945 from books.
Put that in there.
There it is.
And then what I can do is make a subquery that says select the title.
And let's also do release here so we can see it from books and then where released year.
Is equal to this subquery which will run first and evaluate to 1945 with our current data set or some
other year.
If you have different years in yours, but this will be replaced and then this query runs with that
new value.
Let's check it out and we see Canterbury Row 1945.
All right.
So that's another example of a subquery.
In this case, we used Min instead of Max, but the concept there is just a query will run first, the
subquery runs first, and then that evaluates to something in the rest of the query runs.
Remember, you have to use parentheses around any sort of sub query.
All right.