So what we're discussing in this video in case you didn't see the last video that leads into this is
that there is a problem with trying to do something like this select max pages common title from books
if I want to find the title and they'll find the title of the longest book or any other information
along with the maxim pages.
It doesn't work and what it's doing right now is actually returning
here we go is first finding the longest pages maximum pages which is indeed 634.
But then when I ask for title it's actually just giving me the first title The Namesake.
And the reason that it does it it's kind of complicated and weird.
But basically you can think of it as sequel or my as well as executing this and it finds Max pages first
which is independent from this so finds this 634.
And there's only one row that it's preparing to print out.
So then we get Khama title and there's only one spot to fill.
So just takes the first title.
So these are independent of one another.
They're not connected unfortunately but there are ways around it.
So the first thing I'll show you is something a little weird.
It's weird.
Overall it's just weird to you at this point because we haven't seen this before.
It involves something called the sub query which we'll return to later on in the course.
But I wanted to just preview now.
And basically what it lets us do is run one query instead of another.
So it looks like this select star and this case had its star although we could use the title but select
star from books where pages equals and then this is the weird part.
Parentheses and inside the print we have another query select.
In this case I did mean pages from books.
So what's going to happen is that this will actually execute first select whatever we have here.
So let's say how many pages I believe is 1:29 or in our case Max pages would be 6:34.
So it's going to find that in return 634 here.
And then this will run select star from books where pages equals 634.
So let's try it out now and I'll show you basically manually what's happening.
So think of it as doing a select max pages from books just like this.
Which is 634.
And then copy that and do a select and let's do a will do star like star from books where pages equals
6:34.
So it's a two step process and you can see with our formatting a little screwed up we get the Amazing
Adventures of Kavalier and Clay which is indeed the longest book 634 pages so we can do that all at
once or at least in one line of code with it.
If we just recall this select max pages from books that will be our Sobe queery.
And so that's kind of going to be a fill in the blank.
So we'll have a select and let's just do title and pages this time from books where pages equals the
result of finding the max pages.
So it's doing what we just did.
But in one line of code and now you can see if we get the Amazing Adventures of Kavalier and Clay 634
pages.
So we can also do that with men and if we just replace this
you'll see that Raymond Carver's What We Talk About When We Talk About Love short story collection great
short story collection if you like short stories is the shortest book 176 pages.
So that's all we'll talk about with subcreative.
That's all I'll show you.
But as you probably imagine there are other uses for them.
But there's a small problem.
It's not really a problem but there's something to consider which is this is a little bit slow.
In our case it's not that slow so I'm slow at all because we have very small data set but it's effectively
running two queries so each one of these is run independently.
So when we hit ENTER when this executes.
Like I said two things are happening to separate queries that are going to take time.
Each one of them every time you do is select.
It takes time and there's a faster way if you are worried about speed.
And it involves something we've already seen.
So there's nothing new no fancy ness no top secret no advanced features.
So queries or anything like that that you haven't been introduced to yet there's a way of doing it using
what you've already seen.
So that way is using order by.
So what we could do is just order by pages in ascending order so do a select star from books ordered
by pages ascending and then just limit once or Chapa everything else off and just take the first one.
And that will give us the exact same result.
But it's not going to take much time.
So I can be as difficult for my as queue up to run because it's only selecting one thing and it's only
running one query.
So let's try that.
We'll do a select and let's just through Title and pages from books instead of doing our web pages equals
PABX blah blah.
Well just to our order by and then we want to order five pages ascending limit one and we get the exact
same result that we got before with another longer query that involves a subquery and we can do the
same thing with men.
And to do that we just change our order yes.
It still bothers me.
Four letters DGSE sending is only three letters so inconsistent.
But if you remember that DGSE are descending you'll see that the longest book is yet again the Amazing
Adventures of Kavalier and Clay.
OK.
So that was a bit of a tangent but it's important to address because I think a lot of people myself
included.
I remember when I was learning this stuff setting out and thinking that you could do something like
select men let's say year a title comma let's say Star quantity and that it would go find the minimum
year and then the corresponding title and start quantity.
And that's not the case.
That's just not how it works as we've seen.
So I just wanted to point that out and highlight a couple of solutions just so that you're aware going
forward.
Things aren't always as intuitive as you'd hope.
OK.
Next up we're going to continue with min and max and use them in conjunction with group by.