1
00:00:00,300 --> 00:00:02,420
Okay, so we're resuming where we left off.

2
00:00:02,430 --> 00:00:09,300
We want to try and find the entire row or maybe just the title of the book that has the longest page

3
00:00:09,300 --> 00:00:09,870
count.

4
00:00:09,960 --> 00:00:13,110
So we can find the page count at 634.

5
00:00:13,110 --> 00:00:17,940
But of course, let's say we don't know the number because if we know it's 634, I could just write

6
00:00:17,940 --> 00:00:25,530
a query that says, you know, select star from books where page pages equals six, three, four, but

7
00:00:25,530 --> 00:00:26,910
that defeats the purpose.

8
00:00:27,270 --> 00:00:28,560
We see the answer here.

9
00:00:28,560 --> 00:00:35,160
But I want to know if I don't know the number exactly, I have 10,000 plus books.

10
00:00:35,160 --> 00:00:39,570
How do I retrieve the entire row or just the title of that book?

11
00:00:39,780 --> 00:00:42,000
So there's a couple of options.

12
00:00:42,000 --> 00:00:48,690
And one of those options is to use order by which we already know we can order by pages and then limit.

13
00:00:48,690 --> 00:00:57,960
So let's do select, let's just do title and pages from books, order by and then pages.

14
00:00:59,020 --> 00:01:03,820
And then let's do Descending DSC OC.

15
00:01:03,820 --> 00:01:10,120
So we're going from highest pages down to lowest pages and then I would just limit it to one.

16
00:01:10,450 --> 00:01:15,670
And there we are, we found the whole row or in this case just the title and pages, but we have access

17
00:01:15,670 --> 00:01:20,290
to whatever we want of the book that has the highest number of pages.

18
00:01:20,350 --> 00:01:21,610
That is one option.

19
00:01:21,610 --> 00:01:23,440
So here's that in the slides.

20
00:01:23,440 --> 00:01:26,620
The second option I'll show you is quite a bit different.

21
00:01:26,710 --> 00:01:31,840
It involves using something called a sub query, which is really the main focus of this video.

22
00:01:31,840 --> 00:01:34,510
I want to show you that we can do sub queries.

23
00:01:34,600 --> 00:01:36,610
So a sub query is what it sounds like.

24
00:01:36,610 --> 00:01:39,250
It's a query within a larger query.

25
00:01:39,250 --> 00:01:48,280
We surround it with parentheses and this tells my SQL run this first that will be evaluated to some

26
00:01:48,280 --> 00:01:52,390
in this case some number, and then this query will run.

27
00:01:52,810 --> 00:01:53,980
So let's try it.

28
00:01:53,980 --> 00:01:55,360
Let's try writing it out.

29
00:01:55,360 --> 00:02:06,250
Let's do a select title, a comma, pages from books, and then I'm going to say where pages equals,

30
00:02:06,250 --> 00:02:08,410
but I don't know what it's going to be equal to.

31
00:02:08,410 --> 00:02:10,660
I mean, I know it's 634, but that's cheating.

32
00:02:11,200 --> 00:02:17,560
I'm going to say where pages equals the result of running select and I'm going to get an error immediately

33
00:02:17,560 --> 00:02:21,760
because in at least in this, what do you call it, SQL workbench.

34
00:02:21,760 --> 00:02:26,020
I get some nice little error checking as I'm typing some syntax checking.

35
00:02:26,020 --> 00:02:32,350
It says you can't have select right there, but if I surround it with parentheses, it'll shut up eventually.

36
00:02:32,350 --> 00:02:37,960
And it's complaining I don't have a semicolon, but it's it's totally happy now because I have those

37
00:02:38,200 --> 00:02:38,680
ends.

38
00:02:38,710 --> 00:02:40,510
It is a valid query.

39
00:02:40,510 --> 00:02:45,940
It's a sub query within the main query, but otherwise I can't just go writing select if I don't have

40
00:02:45,950 --> 00:02:46,690
prints.

41
00:02:47,110 --> 00:02:48,490
Those are very important.

42
00:02:49,510 --> 00:02:55,630
So select and then I want to find the max of pages from books.

43
00:02:56,440 --> 00:02:58,080
And this will run first.

44
00:02:58,090 --> 00:03:01,600
That's going to be replaced eventually with six, three, four.

45
00:03:01,600 --> 00:03:04,750
And then this query is very straightforward, pretty simple.

46
00:03:05,140 --> 00:03:06,520
Let's try running it.

47
00:03:06,550 --> 00:03:10,630
I'll just run it over here and we get the same exact answer.

48
00:03:10,720 --> 00:03:13,090
The Amazing Adventures of Kavalier and Clay.

49
00:03:13,720 --> 00:03:14,290
Okay.

50
00:03:14,470 --> 00:03:17,440
So does that mean that these are identical solutions?

51
00:03:17,470 --> 00:03:18,520
Not really.

52
00:03:18,640 --> 00:03:19,830
They both work.

53
00:03:19,840 --> 00:03:27,640
If we know for sure that there is only one maximum, meaning that there's only one row that has 634

54
00:03:27,640 --> 00:03:28,330
pages.

55
00:03:28,510 --> 00:03:31,030
But let's say that I have a second one in there.

56
00:03:31,780 --> 00:03:33,400
Insert into books.

57
00:03:34,780 --> 00:03:35,870
Let's see.

58
00:03:35,890 --> 00:03:38,560
Let's go with author.

59
00:03:38,830 --> 00:03:40,270
Actually, let's just keep it simple.

60
00:03:40,270 --> 00:03:44,470
Title and pages and title.

61
00:03:44,470 --> 00:03:50,140
Here is going to be my life in Words.

62
00:03:50,710 --> 00:03:55,180
And the number of pages is going to be 634.

63
00:03:55,300 --> 00:03:59,880
So now we have a duplicate of at least the number of pages.

64
00:03:59,890 --> 00:04:07,030
There's two books with that number of pages, and if I try my sub query solution, this one here, we'll

65
00:04:07,030 --> 00:04:09,370
get both of them, which is great.

66
00:04:09,430 --> 00:04:10,560
Maybe that's what we want.

67
00:04:10,570 --> 00:04:19,990
We get both of them because all that we said is find all of the books where pages is equal to 634 and

68
00:04:19,990 --> 00:04:25,450
if we ran that ourselves, we would get multiple rows, just like if I said find all the books that

69
00:04:25,450 --> 00:04:26,910
were released in 2001.

70
00:04:26,920 --> 00:04:29,030
If there's multiple, we get multiple.

71
00:04:29,050 --> 00:04:30,400
We're not limiting it.

72
00:04:31,090 --> 00:04:37,990
But with the other solution involving order by and limit, of course we only get one because we set

73
00:04:37,990 --> 00:04:38,950
the limit to one.

74
00:04:39,310 --> 00:04:46,150
And yes, I could set the limit to two, but that requires me knowing that there's more than one, more

75
00:04:46,150 --> 00:04:48,610
than one matching row that has that number of pages.

76
00:04:48,640 --> 00:04:54,100
Otherwise, if I had run this a couple of minutes ago, I would see 634 and then some other smaller

77
00:04:54,100 --> 00:04:57,790
number of pages for whatever the runner up book is.

78
00:04:58,000 --> 00:04:59,290
So there is a difference.

79
00:04:59,290 --> 00:05:01,780
But if we're just looking for one, we know there's one.

80
00:05:01,780 --> 00:05:04,510
Then you don't need to bother with a subquery necessarily.

81
00:05:04,510 --> 00:05:09,310
But it's good to know that there is a subtle difference between how they will work.

82
00:05:10,420 --> 00:05:13,910
So just to recap sub queries, let's try one more example.

83
00:05:13,930 --> 00:05:18,760
Why don't we find the title of the book that was released earliest?

84
00:05:18,940 --> 00:05:25,450
So that would involve we could do this select and then we can find the minimum released here.

85
00:05:25,450 --> 00:05:26,470
We already saw this.

86
00:05:26,470 --> 00:05:29,350
I think it's 1945 from books.

87
00:05:30,130 --> 00:05:31,160
Put that in there.

88
00:05:31,180 --> 00:05:31,990
There it is.

89
00:05:32,110 --> 00:05:38,050
And then what I can do is make a subquery that says select the title.

90
00:05:38,170 --> 00:05:46,390
And let's also do release here so we can see it from books and then where released year.

91
00:05:47,390 --> 00:05:56,300
Is equal to this subquery which will run first and evaluate to 1945 with our current data set or some

92
00:05:56,300 --> 00:05:56,940
other year.

93
00:05:56,960 --> 00:06:01,970
If you have different years in yours, but this will be replaced and then this query runs with that

94
00:06:01,970 --> 00:06:02,750
new value.

95
00:06:02,900 --> 00:06:08,300
Let's check it out and we see Canterbury Row 1945.

96
00:06:08,750 --> 00:06:09,260
All right.

97
00:06:09,260 --> 00:06:11,370
So that's another example of a subquery.

98
00:06:11,390 --> 00:06:16,790
In this case, we used Min instead of Max, but the concept there is just a query will run first, the

99
00:06:16,790 --> 00:06:20,900
subquery runs first, and then that evaluates to something in the rest of the query runs.

100
00:06:20,960 --> 00:06:24,890
Remember, you have to use parentheses around any sort of sub query.

101
00:06:25,430 --> 00:06:25,910
All right.