1 00:00:00,090 --> 00:00:02,130 Next up we're talking about limit. 2 00:00:02,130 --> 00:00:05,730 Limit allows us to control the number of results we get back. 3 00:00:05,850 --> 00:00:10,980 Now, a lot of the time, this may not be that useful unless we're also sorting, but I'll show both 4 00:00:10,980 --> 00:00:11,820 of them together. 5 00:00:12,150 --> 00:00:17,700 On its own limit, followed by a number, will limit the number of results we get back. 6 00:00:17,700 --> 00:00:25,320 So as I said, I mean, we could just select the first five, let's do author or book ID, I think is 7 00:00:25,320 --> 00:00:34,440 what we have and title and released year from books and we get what is it, 20 rows. 8 00:00:35,490 --> 00:00:42,150 But I could limit that by just saying limit and then a number like five and that will give me the first 9 00:00:42,150 --> 00:00:42,870 five. 10 00:00:43,230 --> 00:00:44,370 But what does that really mean? 11 00:00:44,370 --> 00:00:45,150 The first five? 12 00:00:45,150 --> 00:00:50,370 It's just the first five based off of this order, which is the order that are stored in the table in 13 00:00:50,370 --> 00:00:56,940 maybe not that useful, but what if I wanted to get the first five books that were released, the earliest 14 00:00:56,940 --> 00:00:58,320 five released books? 15 00:00:58,650 --> 00:01:06,900 Well, I could sort or order by released here first, and that will give me all of the books sorted 16 00:01:06,900 --> 00:01:07,860 by release year. 17 00:01:07,890 --> 00:01:11,280 Although we have that darn null in there that I really should get rid of. 18 00:01:12,120 --> 00:01:16,560 And then after that I can limit it to five. 19 00:01:17,740 --> 00:01:19,430 Now we get the first five. 20 00:01:19,450 --> 00:01:24,190 So Cannery Row is 1945, up to where I'm calling from, 1989. 21 00:01:24,460 --> 00:01:27,010 Not a lot of super early literature in this table. 22 00:01:27,990 --> 00:01:30,700 Okay, so that's the basic usage of limits. 23 00:01:30,730 --> 00:01:36,550 You put some number in there, we can limit it to ten, and that will give me the first ten. 24 00:01:37,570 --> 00:01:43,060 Of course, if I change the order, if I order by release tier descending now, I'm going to get the 25 00:01:43,060 --> 00:01:47,080 ten most recent books 2017 down to 2003. 26 00:01:47,740 --> 00:01:51,250 So most of the time I use limit alongside order by. 27 00:01:51,310 --> 00:01:55,930 But maybe if I'm just actually honestly one of the times I use limit the most is when I'm teaching and 28 00:01:55,930 --> 00:01:59,290 I have a table with a lot of results, a lot of values in it. 29 00:01:59,290 --> 00:02:01,390 And I just want to show students an example. 30 00:02:01,390 --> 00:02:06,690 I might limit it to the first five, but in the real world, when you're not teaching, I usually use 31 00:02:06,710 --> 00:02:07,840 limit with order by. 32 00:02:08,380 --> 00:02:11,070 But we don't have to only get the first five. 33 00:02:11,080 --> 00:02:15,050 We can do fancy things like this right here where I actually have two numbers. 34 00:02:15,070 --> 00:02:18,250 Now this is actually equivalent to this one right here. 35 00:02:18,250 --> 00:02:21,960 So we are getting the first five going from 0 to 5. 36 00:02:21,990 --> 00:02:23,080 Let me just show that. 37 00:02:24,370 --> 00:02:27,520 Let me do this on different lines so that it's easier to look at. 38 00:02:27,520 --> 00:02:31,150 So order by released. 39 00:02:31,630 --> 00:02:32,310 Yea. 40 00:02:33,010 --> 00:02:33,760 All right. 41 00:02:33,760 --> 00:02:37,540 And then after that I'll say limit the first five. 42 00:02:37,570 --> 00:02:38,380 Just like that. 43 00:02:39,340 --> 00:02:44,080 But instead of saying five, I'm going to do zero comma five. 44 00:02:44,830 --> 00:02:50,470 And what you'll see is when I do that, what happens is we get the exact same results. 45 00:02:50,470 --> 00:02:53,160 But I could instead say from 1 to 5. 46 00:02:53,170 --> 00:02:58,900 And the way that it works is that I've said, start at one and go for five. 47 00:02:58,900 --> 00:03:05,830 So I still want five total, but I want to begin with that first row, which it means not the zero with 48 00:03:05,830 --> 00:03:06,340 row. 49 00:03:06,340 --> 00:03:09,700 So the zero with row would have been this one right here. 50 00:03:10,090 --> 00:03:11,100 It's a little confusing. 51 00:03:11,110 --> 00:03:12,370 Let's try one more example. 52 00:03:12,370 --> 00:03:17,530 So once again, I'm just going to limit it to ten this time so you can see what it looks like. 53 00:03:17,680 --> 00:03:24,190 But instead of ten, I'm going to say, let's go from row three and let's only go for two rows. 54 00:03:24,550 --> 00:03:25,960 And this is what happens. 55 00:03:25,960 --> 00:03:28,750 We started at row three in this result. 56 00:03:28,750 --> 00:03:32,170 So zero one, two, three is white noise. 57 00:03:32,170 --> 00:03:33,670 And then we carried on for two rows. 58 00:03:33,670 --> 00:03:36,610 So white noise and where I'm calling from, that's what we got. 59 00:03:36,610 --> 00:03:43,450 So this does allow us to select a subset in the middle of a grouping, not just the first three rows 60 00:03:43,450 --> 00:03:50,830 or the first five rows, but I could do from row one to row ten or from row 25 to 27 if I needed to 61 00:03:50,830 --> 00:03:51,100 do that. 62 00:03:51,100 --> 00:03:56,560 For some reason I will say usually I'm just working with the first rows and I don't use two numbers 63 00:03:56,560 --> 00:03:57,160 for limit. 64 00:03:57,160 --> 00:04:04,420 But you can just remember this first number is the starting row and the second number is the count how 65 00:04:04,420 --> 00:04:06,220 many you want to retrieve. 66 00:04:06,220 --> 00:04:11,290 So this one would start at row five and it would include seven results. 67 00:04:11,710 --> 00:04:13,000 Let's try that one here. 68 00:04:13,000 --> 00:04:16,860 So from five comma seven, we started it. 69 00:04:16,870 --> 00:04:20,200 When I say row five, it Let me just show this here. 70 00:04:20,980 --> 00:04:22,780 If I did ten. 71 00:04:24,040 --> 00:04:27,450 Row five would be zero one, two, three, four, five. 72 00:04:27,460 --> 00:04:30,250 And that is indeed where we started Interpreter of Maladies. 73 00:04:30,250 --> 00:04:34,180 And then we included seven results one, two, three, four, five. 74 00:04:34,180 --> 00:04:37,720 And it keeps going past what I have in this verse ten. 75 00:04:37,930 --> 00:04:46,480 Another thing we can do if I do a select, I don't have a just title from books and I'll just well, 76 00:04:46,480 --> 00:04:47,410 we'll do it alphabetically. 77 00:04:47,410 --> 00:04:47,950 Sure. 78 00:04:48,160 --> 00:04:50,650 Order by title. 79 00:04:51,010 --> 00:04:57,160 We have 20 rows, but let's say I don't know that and I use a number that's out of bounds that doesn't 80 00:04:57,160 --> 00:04:58,630 exist in this table. 81 00:04:58,630 --> 00:05:00,220 Like, I don't know, 40. 82 00:05:00,520 --> 00:05:02,170 What happens if I do a limit? 83 00:05:02,170 --> 00:05:02,860 40? 84 00:05:03,470 --> 00:05:05,850 It's just going to give me as many as it can. 85 00:05:05,870 --> 00:05:07,070 I don't get an error. 86 00:05:07,190 --> 00:05:12,950 Same thing if I said start at row 18 and go for this many rows. 87 00:05:13,580 --> 00:05:14,450 I don't get an error. 88 00:05:14,450 --> 00:05:17,540 I just get the last two because there's 20 rows. 89 00:05:17,540 --> 00:05:20,480 We start at 18 and we just go to the end. 90 00:05:20,480 --> 00:05:22,220 So that is one option. 91 00:05:22,220 --> 00:05:25,310 I guess if you want to get rows from the end and that's it for limits. 92 00:05:25,310 --> 00:05:30,020 So often I use it with a short first order, buy something and then limit our results. 93 00:05:30,020 --> 00:05:32,780 You can use one number which is probably more common. 94 00:05:32,780 --> 00:05:35,780 Just limit the first five results and that's all you get back. 95 00:05:35,780 --> 00:05:39,800 Or you can say, I want a certain number of results starting at some point.