WEBVTT 00:00.540 --> 00:06.210 So what we're discussing in this video in case you didn't see the last video that leads into this is 00:06.410 --> 00:13.170 that there is a problem with trying to do something like this select max pages common title from books 00:13.170 --> 00:18.120 if I want to find the title and they'll find the title of the longest book or any other information 00:18.180 --> 00:21.650 along with the maxim pages. 00:21.780 --> 00:25.300 It doesn't work and what it's doing right now is actually returning 00:27.920 --> 00:35.490 here we go is first finding the longest pages maximum pages which is indeed 634. 00:35.490 --> 00:41.790 But then when I ask for title it's actually just giving me the first title The Namesake. 00:42.090 --> 00:45.440 And the reason that it does it it's kind of complicated and weird. 00:45.450 --> 00:53.250 But basically you can think of it as sequel or my as well as executing this and it finds Max pages first 00:53.610 --> 00:57.960 which is independent from this so finds this 634. 00:58.200 --> 01:02.030 And there's only one row that it's preparing to print out. 01:02.070 --> 01:06.930 So then we get Khama title and there's only one spot to fill. 01:06.930 --> 01:08.820 So just takes the first title. 01:08.850 --> 01:11.010 So these are independent of one another. 01:11.010 --> 01:15.160 They're not connected unfortunately but there are ways around it. 01:15.420 --> 01:18.450 So the first thing I'll show you is something a little weird. 01:18.780 --> 01:19.660 It's weird. 01:19.830 --> 01:23.770 Overall it's just weird to you at this point because we haven't seen this before. 01:23.820 --> 01:28.610 It involves something called the sub query which we'll return to later on in the course. 01:28.740 --> 01:30.860 But I wanted to just preview now. 01:31.380 --> 01:37.290 And basically what it lets us do is run one query instead of another. 01:37.710 --> 01:45.180 So it looks like this select star and this case had its star although we could use the title but select 01:45.270 --> 01:50.420 star from books where pages equals and then this is the weird part. 01:50.640 --> 01:55.070 Parentheses and inside the print we have another query select. 01:55.140 --> 01:59.020 In this case I did mean pages from books. 01:59.070 --> 02:05.880 So what's going to happen is that this will actually execute first select whatever we have here. 02:05.890 --> 02:13.150 So let's say how many pages I believe is 1:29 or in our case Max pages would be 6:34. 02:13.450 --> 02:17.090 So it's going to find that in return 634 here. 02:17.440 --> 02:24.790 And then this will run select star from books where pages equals 634. 02:24.820 --> 02:29.220 So let's try it out now and I'll show you basically manually what's happening. 02:29.440 --> 02:37.130 So think of it as doing a select max pages from books just like this. 02:39.820 --> 02:41.670 Which is 634. 02:41.950 --> 02:55.450 And then copy that and do a select and let's do a will do star like star from books where pages equals 02:55.630 --> 02:57.090 6:34. 02:57.190 --> 03:03.610 So it's a two step process and you can see with our formatting a little screwed up we get the Amazing 03:03.610 --> 03:10.770 Adventures of Kavalier and Clay which is indeed the longest book 634 pages so we can do that all at 03:10.770 --> 03:15.400 once or at least in one line of code with it. 03:15.450 --> 03:21.940 If we just recall this select max pages from books that will be our Sobe queery. 03:23.040 --> 03:24.980 And so that's kind of going to be a fill in the blank. 03:24.990 --> 03:35.370 So we'll have a select and let's just do title and pages this time from books where pages equals the 03:35.370 --> 03:38.770 result of finding the max pages. 03:39.270 --> 03:41.220 So it's doing what we just did. 03:41.360 --> 03:47.760 But in one line of code and now you can see if we get the Amazing Adventures of Kavalier and Clay 634 03:47.790 --> 03:48.550 pages. 03:48.990 --> 03:52.410 So we can also do that with men and if we just replace this 03:56.420 --> 04:01.640 you'll see that Raymond Carver's What We Talk About When We Talk About Love short story collection great 04:01.640 --> 04:07.450 short story collection if you like short stories is the shortest book 176 pages. 04:07.600 --> 04:09.530 So that's all we'll talk about with subcreative. 04:09.550 --> 04:10.600 That's all I'll show you. 04:10.750 --> 04:14.400 But as you probably imagine there are other uses for them. 04:14.860 --> 04:16.990 But there's a small problem. 04:16.990 --> 04:21.990 It's not really a problem but there's something to consider which is this is a little bit slow. 04:22.600 --> 04:28.270 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 04:28.270 --> 04:34.030 running two queries so each one of these is run independently. 04:34.030 --> 04:37.210 So when we hit ENTER when this executes. 04:37.210 --> 04:42.350 Like I said two things are happening to separate queries that are going to take time. 04:42.370 --> 04:45.240 Each one of them every time you do is select. 04:45.460 --> 04:51.070 It takes time and there's a faster way if you are worried about speed. 04:52.040 --> 04:54.210 And it involves something we've already seen. 04:55.010 --> 05:01.260 So there's nothing new no fancy ness no top secret no advanced features. 05:01.280 --> 05:05.960 So queries or anything like that that you haven't been introduced to yet there's a way of doing it using 05:05.960 --> 05:08.150 what you've already seen. 05:08.150 --> 05:13.760 So that way is using order by. 05:13.790 --> 05:20.630 So what we could do is just order by pages in ascending order so do a select star from books ordered 05:20.750 --> 05:26.340 by pages ascending and then just limit once or Chapa everything else off and just take the first one. 05:27.290 --> 05:29.520 And that will give us the exact same result. 05:29.810 --> 05:31.620 But it's not going to take much time. 05:31.700 --> 05:37.790 So I can be as difficult for my as queue up to run because it's only selecting one thing and it's only 05:37.790 --> 05:39.010 running one query. 05:39.350 --> 05:41.400 So let's try that. 05:41.570 --> 05:50.020 We'll do a select and let's just through Title and pages from books instead of doing our web pages equals 05:50.020 --> 05:50.910 PABX blah blah. 05:51.040 --> 06:00.620 Well just to our order by and then we want to order five pages ascending limit one and we get the exact 06:00.620 --> 06:08.630 same result that we got before with another longer query that involves a subquery and we can do the 06:08.630 --> 06:10.500 same thing with men. 06:10.640 --> 06:14.880 And to do that we just change our order yes. 06:14.890 --> 06:16.130 It still bothers me. 06:16.720 --> 06:21.990 Four letters DGSE sending is only three letters so inconsistent. 06:22.390 --> 06:28.930 But if you remember that DGSE are descending you'll see that the longest book is yet again the Amazing 06:28.930 --> 06:30.910 Adventures of Kavalier and Clay. 06:30.910 --> 06:31.260 OK. 06:31.300 --> 06:36.310 So that was a bit of a tangent but it's important to address because I think a lot of people myself 06:36.310 --> 06:37.080 included. 06:37.180 --> 06:42.790 I remember when I was learning this stuff setting out and thinking that you could do something like 06:43.210 --> 06:53.320 select men let's say year a title comma let's say Star quantity and that it would go find the minimum 06:53.320 --> 06:57.550 year and then the corresponding title and start quantity. 06:57.550 --> 06:58.480 And that's not the case. 06:58.480 --> 07:00.320 That's just not how it works as we've seen. 07:00.490 --> 07:05.680 So I just wanted to point that out and highlight a couple of solutions just so that you're aware going 07:05.680 --> 07:06.260 forward. 07:06.340 --> 07:08.970 Things aren't always as intuitive as you'd hope. 07:09.330 --> 07:09.920 OK. 07:10.030 --> 07:14.440 Next up we're going to continue with min and max and use them in conjunction with group by.