1
00:00:00,150 --> 00:00:02,910
Next up in our toolbox is limit.

2
00:00:03,240 --> 00:00:09,080
So limit allows us to specify a number for how many results that we want to select.

3
00:00:09,090 --> 00:00:16,170
So I could say things like, I want the first two bestselling books rather than all bestselling books

4
00:00:16,170 --> 00:00:19,650
or the top ten most recent books.

5
00:00:19,650 --> 00:00:25,080
So we often use it in conjunction with order, buy or sorting because that typically is going to give

6
00:00:25,080 --> 00:00:31,680
you something meaningful rather than just saying Select all books and give me the first two.

7
00:00:31,710 --> 00:00:33,540
Well, how is first defined?

8
00:00:33,540 --> 00:00:34,440
And we can't do that.

9
00:00:34,440 --> 00:00:35,310
I'll show you that.

10
00:00:35,400 --> 00:00:41,130
If we do a select, let's just do title from books.

11
00:00:41,850 --> 00:00:44,940
The order that we get them right now again is the order they were added.

12
00:00:45,090 --> 00:00:48,540
So we have the namesake, Norse mythology, American gods and so on.

13
00:00:48,540 --> 00:00:52,290
So I could limit this and the syntax looks like this.

14
00:00:52,290 --> 00:00:57,000
I just add limit at the end, just like we added order by limit.

15
00:00:57,000 --> 00:00:57,960
Let's say three.

16
00:01:00,350 --> 00:01:02,600
And now I just get those first three.

17
00:01:03,260 --> 00:01:06,050
So it's going to limit it to the first three.

18
00:01:06,080 --> 00:01:08,210
Whether they're sorted or not, it doesn't care.

19
00:01:08,210 --> 00:01:10,220
It just chops it off right there.

20
00:01:10,910 --> 00:01:15,140
So again, it's not that useful or that meaningful just to do it on its own like this.

21
00:01:15,500 --> 00:01:20,720
But we could do limit one, limit ten and so on.

22
00:01:21,650 --> 00:01:24,080
And that's basically all that there is to limit.

23
00:01:24,320 --> 00:01:32,480
We can do it with a select star if I only want let's say I want everything about the first book.

24
00:01:32,930 --> 00:01:33,620
There we go.

25
00:01:33,620 --> 00:01:35,900
I get The Namesake and there's everything about it.

26
00:01:36,080 --> 00:01:39,620
But more often than not, we're using it with order by.

27
00:01:39,950 --> 00:01:41,120
So here's an example.

28
00:01:41,120 --> 00:01:48,590
If I want the five most recently released books I'm going to, and I only want the title and the release

29
00:01:48,590 --> 00:01:49,550
here in this case.

30
00:01:49,550 --> 00:01:51,860
So select title and release here from books.

31
00:01:52,460 --> 00:01:59,060
Then I'm going to order them by the release year descending and then I'm going to limit it to the first

32
00:01:59,060 --> 00:01:59,660
five.

33
00:02:00,410 --> 00:02:01,430
So let's try it.

34
00:02:01,820 --> 00:02:11,270
I'll go ahead and make a new file again, LTD SQL and in there I'll write a select title comma released

35
00:02:11,270 --> 00:02:12,920
year from books.

36
00:02:12,920 --> 00:02:14,090
I won't add limit yet.

37
00:02:14,090 --> 00:02:22,460
Let's just make sure our source is working so we'll do a source refining.

38
00:02:22,460 --> 00:02:24,200
Why did I name this so long?

39
00:02:24,200 --> 00:02:27,950
Refining selections slash limit SQL.

40
00:02:28,100 --> 00:02:31,580
Fortunately when we have to type it once, so that's working.

41
00:02:32,060 --> 00:02:35,240
So now what we'll do is order them by that release year.

42
00:02:36,260 --> 00:02:43,910
So we'll do an order by and we could write release here and I will because it's more specific or it's

43
00:02:44,210 --> 00:02:48,020
easier to understand, it's more semantic to write it out even though it's longer.

44
00:02:48,020 --> 00:02:52,370
But of course we could replace it with two, as we just saw in the last video.

45
00:02:52,370 --> 00:02:54,380
So order by that release here.

46
00:02:54,410 --> 00:03:02,450
If I do that now, that gives us all the books ordered in ascending order based off of their year,

47
00:03:02,450 --> 00:03:03,620
which is not what we want.

48
00:03:03,950 --> 00:03:06,800
So we can switch that with DSC.

49
00:03:07,670 --> 00:03:08,570
There we go.

50
00:03:08,660 --> 00:03:10,790
And now I can limit it to five.

51
00:03:12,020 --> 00:03:14,600
And so that limit comes last as well.

52
00:03:14,900 --> 00:03:19,640
So it's going to do everything and then chop it wherever you specify.

53
00:03:19,670 --> 00:03:21,260
So that's important to to know.

54
00:03:21,590 --> 00:03:22,670
So let's try it now.

55
00:03:22,670 --> 00:03:23,750
Make sure I save.

56
00:03:25,310 --> 00:03:27,770
And there we go, the five most recent books.

57
00:03:28,340 --> 00:03:33,080
And of course, we can change this to whatever number you want, if you want the one most recent book

58
00:03:33,200 --> 00:03:39,110
or if we wanted, let's say 14, we can do that.

59
00:03:39,560 --> 00:03:41,660
So there are a few other ways we can use it.

60
00:03:42,020 --> 00:03:44,420
For example, here's the same query.

61
00:03:45,170 --> 00:03:47,000
However, I've written it slightly different.

62
00:03:47,270 --> 00:03:51,650
So everything up until this point right here is the same limit.

63
00:03:52,460 --> 00:03:55,490
But this time I'm saying limit is zero, comma five.

64
00:03:55,670 --> 00:04:02,990
So if we just copy this, I won't make you watch me type that whole thing out and I comment this out

65
00:04:03,890 --> 00:04:04,850
and I try running this.

66
00:04:04,850 --> 00:04:06,950
Now just take a guess.

67
00:04:06,950 --> 00:04:08,060
What do you think will happen?

68
00:04:10,280 --> 00:04:11,570
And here we go.

69
00:04:12,780 --> 00:04:14,400
It is the exact same thing.

70
00:04:14,610 --> 00:04:21,300
So this is a syntax that allows us to specify a starting point and then how many we want to go from

71
00:04:21,300 --> 00:04:21,720
there.

72
00:04:21,839 --> 00:04:26,700
So basically it's saying and this is kind of confusing because it starts at zero.

73
00:04:26,700 --> 00:04:29,640
So the first row in a table is the zeroth row.

74
00:04:30,000 --> 00:04:36,450
But then when we're dealing with strings, like when we had Hello World H is actually the first character

75
00:04:36,450 --> 00:04:38,400
in my SQL at least.

76
00:04:38,550 --> 00:04:43,920
So anyways, it's saying I want the first row and I want to go for five rows.

77
00:04:44,280 --> 00:04:45,660
So we could change that.

78
00:04:46,020 --> 00:04:47,820
I only want three rows this time.

79
00:04:47,850 --> 00:04:50,970
If we rerun it, it gives us the first three rows.

80
00:04:51,090 --> 00:04:52,830
But then I could shift it.

81
00:04:53,310 --> 00:04:54,880
Let's say I want to go.

82
00:04:54,900 --> 00:04:58,310
I don't know why, but the second row onwards.

83
00:04:58,320 --> 00:05:01,200
So it gives us North mythology 10% happier.

84
00:05:01,200 --> 00:05:02,040
And the circle.

85
00:05:03,080 --> 00:05:05,470
If you didn't catch that before when it was zero.

86
00:05:05,500 --> 00:05:08,410
We got Lincoln in the Bardo Norse mythology in 10%.

87
00:05:08,410 --> 00:05:11,770
Happier when I change this to one, it shifts the starting point.

88
00:05:11,830 --> 00:05:14,260
So it's not that really it's not comment at all.

89
00:05:14,260 --> 00:05:18,910
But you could do things like ten comma one.

90
00:05:19,270 --> 00:05:22,570
And what this will do is go to the 11th book.

91
00:05:23,490 --> 00:05:25,090
Well, I need to save first.

92
00:05:25,750 --> 00:05:32,560
Now, if I run it, it will give us the 11th book and just to make sure that we're right there, if

93
00:05:32,560 --> 00:05:38,080
we rerun it now, we can count down one, two, three, four, five, six, seven, eight, nine, ten,

94
00:05:38,080 --> 00:05:39,760
11 and we get fake book.

95
00:05:40,030 --> 00:05:47,680
So if you need to access very particular rows based off of order, that is a way that you can do it.

96
00:05:47,710 --> 00:05:49,570
It's just honestly not that common.

97
00:05:49,570 --> 00:05:55,240
Pretty much every time that I use limit, it's in a context like this limit one or the first three or

98
00:05:55,240 --> 00:05:57,610
the first five or something like that.

99
00:05:57,610 --> 00:06:01,210
But I'll give you an actual a real reason you might use it.

100
00:06:01,210 --> 00:06:06,970
Let's say that you have a blog site that you've created and your blog has thousands of articles in it

101
00:06:06,970 --> 00:06:07,570
already.

102
00:06:07,570 --> 00:06:13,240
When a user visits the blog page, you don't show them all thousand, let's say 10,000 results all at

103
00:06:13,240 --> 00:06:13,450
once.

104
00:06:13,450 --> 00:06:16,810
For every blog post that you've written, that would be overwhelming.

105
00:06:16,810 --> 00:06:19,810
And that's just there's too much information to display.

106
00:06:20,230 --> 00:06:26,410
Most likely what you'll do is have the first ten posts and then I can click a next button and it will

107
00:06:26,410 --> 00:06:31,750
load from post 11 to 20 and I can keep clicking next and so on.

108
00:06:31,750 --> 00:06:32,800
You've probably seen that before.

109
00:06:32,800 --> 00:06:39,280
It's called pagination, so you can do that by rather than selecting everything from your database,

110
00:06:39,280 --> 00:06:41,320
you could select from zero.

111
00:06:41,320 --> 00:06:48,250
And I only want the first ten and then the next time through you could select from ten and I want ten

112
00:06:48,250 --> 00:06:50,020
more and so on.

113
00:06:50,260 --> 00:06:54,250
So that is a situation where you could use that limit and provide a range like that.

114
00:06:54,250 --> 00:06:57,460
But again, most of the time I just use this limit.

115
00:06:57,460 --> 00:07:02,740
And then one number, the very last thing I'll show you is that this is actually straight from the docs

116
00:07:02,740 --> 00:07:03,550
from my SQL.

117
00:07:03,670 --> 00:07:07,120
I think it's really clunky and weird that they even specify this.

118
00:07:07,120 --> 00:07:14,230
If you wanted to select from one row all the way to the end of the table, so I want from row five all

119
00:07:14,230 --> 00:07:15,100
the way to the end.

120
00:07:15,100 --> 00:07:17,320
We might have however many thousand rows.

121
00:07:17,320 --> 00:07:19,660
There isn't a nice, elegant way of doing it.

122
00:07:19,900 --> 00:07:21,370
This is what you have to do.

123
00:07:21,820 --> 00:07:27,550
Select Star for whatever you're selecting from whatever table limit and then where you want to start

124
00:07:27,550 --> 00:07:29,620
comma some gigantic number.

125
00:07:29,620 --> 00:07:30,550
And I'm not kidding you.

126
00:07:30,550 --> 00:07:32,200
I actually have it in the docs here.

127
00:07:33,550 --> 00:07:34,060
Right?

128
00:07:34,060 --> 00:07:34,960
Where is that.

129
00:07:35,080 --> 00:07:40,330
Right here to retrieve all rows from a certain offset up to the end of the result set.

130
00:07:40,330 --> 00:07:43,000
You can use some large number for the second parameter.

131
00:07:43,820 --> 00:07:46,610
So that's their official suggestion on how to do it.

132
00:07:46,940 --> 00:07:52,610
Select start from table limit 95 comma some gigantic number that you would know you'd never have that

133
00:07:52,610 --> 00:07:54,410
many rows in your table.

134
00:07:54,410 --> 00:08:04,400
So we could actually do that here if we just do a select title from Books Order By and let's just say

135
00:08:04,400 --> 00:08:06,950
I want to go, we don't even need the order by.

136
00:08:06,950 --> 00:08:08,420
Honestly, let's just do a limit.

137
00:08:08,420 --> 00:08:14,180
And if we just do limit five, that's going to give us the first five.

138
00:08:14,180 --> 00:08:21,800
But if I want to go from five to the end, I can do limit five and then some gigantic number.

139
00:08:21,920 --> 00:08:24,010
And in our case, it doesn't even have to be that gigantic.

140
00:08:24,020 --> 00:08:25,370
So we don't have that many books.

141
00:08:25,970 --> 00:08:32,150
But if I do that, it gives us everything after five or technically six because it starts at zero.

142
00:08:33,350 --> 00:08:36,980
So I could reduce this down, I could just do like 50.

143
00:08:37,610 --> 00:08:40,130
And it gives us the same thing because we don't have 50 books.

144
00:08:40,130 --> 00:08:41,570
So it just goes until the end.

145
00:08:42,340 --> 00:08:48,220
So that's it to limit with all those weird things that I showcased and the odd situations and ways you

146
00:08:48,220 --> 00:08:48,850
can use it.

147
00:08:48,850 --> 00:08:55,620
The most common, the way that almost always that I use it is this just limit and then a single number.

148
00:08:55,630 --> 00:08:56,110
All righty.

149
00:08:56,110 --> 00:08:57,190
So that's it.

150
00:08:57,490 --> 00:09:01,540
Moving on to one more and then we're done for this section.

151
00:09:01,540 --> 00:09:03,370
At least we're not done with the whole course.