WEBVTT 00:00.300 --> 00:03.420 Next up in our toolbox is limit. 00:03.480 --> 00:09.080 So limit allows us to specify a number for how many results that we want to select. 00:09.330 --> 00:16.020 So I could say things like I want the first two best selling books rather than all best selling books 00:16.440 --> 00:19.760 or the top 10 most recent books. 00:19.770 --> 00:25.410 So we often use it in conjunction with order by sorting because that typically is going to give you 00:26.370 --> 00:31.770 something meaningful rather than just saying select all books and give me the first two. 00:31.890 --> 00:34.520 Well how was first defined and we can do that. 00:34.530 --> 00:43.230 I'll show you that if we do a select let's just do title from books the order that we get them right 00:43.230 --> 00:45.040 now and get us the order they were added. 00:45.330 --> 00:48.800 So we have the namesake Norse mythology American Gods and so on. 00:48.840 --> 00:52.380 So I could limit this and the syntax looks like this. 00:52.380 --> 00:57.100 It just adds limit at the end just like we added order by limit. 00:57.130 --> 01:00.470 I'd say three. 01:00.550 --> 01:03.270 Now I just get those first three. 01:03.430 --> 01:06.380 So it's going to limit it to the first three. 01:06.400 --> 01:08.440 Whether they're sorted or not it doesn't care. 01:08.470 --> 01:11.100 It just chops it off right there. 01:11.110 --> 01:15.670 So again it's not that useful or meaningful just to do it on its own like this. 01:15.670 --> 01:21.100 But we could do you know Limit 1 limit 10 and so on. 01:21.790 --> 01:24.140 And that's basically all that there is to limit. 01:24.220 --> 01:28.980 You know we can do it with a select star. 01:29.650 --> 01:34.990 If I only want let's say everything about the first book there we go and you get the namesake and there's 01:34.990 --> 01:36.060 everything about it. 01:36.280 --> 01:40.170 But more often than not we're using it with order by. 01:40.180 --> 01:41.150 So here's an example. 01:41.350 --> 01:48.670 If I want the five most recently released books I'm going to and I only want the title and the release 01:48.670 --> 01:49.640 year in this case. 01:49.780 --> 01:56.710 So select title and release here from books then I'm going to order them by the released year descending 01:57.460 --> 02:00.030 and then I'm going to limit it to the first five. 02:00.550 --> 02:01.640 So let's try it. 02:01.960 --> 02:04.220 I'll go ahead and make a new file again. 02:04.450 --> 02:06.490 Limit that as well. 02:06.810 --> 02:12.990 And in there I'll write a select title comma released year from books. 02:13.150 --> 02:14.280 I won't add it yet. 02:14.320 --> 02:22.360 Just make sure our source is working so we'll do a source refining. 02:22.460 --> 02:26.100 Why the name so long finding selections. 02:26.110 --> 02:28.050 Slash limit as kua. 02:28.330 --> 02:30.480 Fortunately you only have to type it once. 02:30.490 --> 02:31.880 OK so that's working. 02:32.230 --> 02:41.050 So now what we'll do is order them by that released year so we'll do an order by and we could write 02:41.050 --> 02:46.150 really sure and I will because it's more specific or is it's easier to understand more semantic to write 02:46.150 --> 02:48.120 it out even though it's longer. 02:48.310 --> 02:52.510 But of course we could replace it with two As we just saw in the last video. 02:52.630 --> 02:54.550 So ordered by that release here. 02:54.600 --> 03:02.680 If I do that now that gives us all the books ordered in ascending order based off of their year which 03:02.680 --> 03:07.560 is not what we want so we can switch that with DGSE. 03:07.790 --> 03:08.820 Here we go. 03:08.970 --> 03:11.270 And now I can limit it to five. 03:12.130 --> 03:15.020 And so that limit comes last as well. 03:15.160 --> 03:19.750 So it's going to do everything and then chop it wherever you specify. 03:19.960 --> 03:21.790 So that's important to know. 03:21.790 --> 03:22.750 So let's try it now. 03:22.780 --> 03:26.120 Make sure I save and there we go. 03:26.200 --> 03:31.240 The five most recent books and of course we can change this to whatever number you want if you want 03:31.250 --> 03:37.570 the one most recent book or if we wanted let's say 14. 03:38.290 --> 03:39.720 We can do that. 03:39.760 --> 03:42.140 So there are a few other ways we can use it. 03:42.250 --> 03:45.240 For example here's the same query. 03:45.340 --> 03:50.560 However I've written it slightly different so everything up until this point right here is the same 03:51.040 --> 03:55.820 limit but this time I'm saying limit 0 comma 5. 03:55.900 --> 04:00.790 So if we just copy this I won't make you watch me type that whole thing out. 04:01.210 --> 04:04.930 And I comment this out and I tried running this. 04:04.930 --> 04:07.060 Now just take a guess. 04:07.060 --> 04:08.400 What do you think will happen. 04:10.430 --> 04:11.670 And here we go. 04:12.930 --> 04:14.540 It does the exact same thing. 04:14.820 --> 04:21.390 So this is a syntax that allows us to specify a starting point and then how many we want to go from 04:21.390 --> 04:21.830 there. 04:22.110 --> 04:26.780 So basically it's saying and this is kind of confusing because it starts at zero. 04:26.790 --> 04:29.860 So the first row in a table is zero with row. 04:30.210 --> 04:34.510 But then when we're dealing with strings like when we had hello world. 04:34.650 --> 04:38.760 Age is actually the first character in my ask well least. 04:38.790 --> 04:46.140 So anyways it's saying I want the first row and I want to go for five rows so we could change that. 04:46.180 --> 04:47.940 I only want three rows this time. 04:48.090 --> 04:51.170 If we rerun it it gives us the first three rows. 04:51.390 --> 04:53.020 But then I could shift it. 04:53.430 --> 04:54.990 Let's say I want to go. 04:55.080 --> 04:58.530 I don't know why but the second row onwards. 04:58.530 --> 05:05.600 So it gives us north mythology 10 percent happier and the circle didn't get 10 before when it was zero. 05:05.780 --> 05:09.320 We got Lincoln in the bardo Norse mythology and 10 percent happier. 05:09.320 --> 05:12.050 When I changed this to 1 it shifts the starting point. 05:12.110 --> 05:19.100 So it's not that really it's not common at all but you could do things like 10 comma 1. 05:19.490 --> 05:23.490 And what this will do is go to the eleventh book. 05:23.990 --> 05:25.860 I need to save first. 05:25.970 --> 05:28.650 Now if I run it it will give us the eleventh book. 05:28.820 --> 05:37.520 And just to make sure that we're right there if we rerun it now we could count down 1 2 3 4 5 6 7 8 05:37.580 --> 05:39.930 9 10 11 and we get fake book. 05:40.280 --> 05:48.320 So if you need to access very particular rows based off of order that is a way that you can do it just 05:48.440 --> 05:49.640 honestly not that common. 05:49.760 --> 05:55.310 Pretty much every time that I use a limit it's in a context like this limit one or the first three of 05:55.310 --> 05:57.690 the first five or something like that. 05:57.860 --> 06:01.400 But I'll give you an actual real reason you might use it. 06:01.400 --> 06:07.010 Let's say that you have a blog site that you've created and your blog has thousands of articles in it 06:07.040 --> 06:07.720 already. 06:07.820 --> 06:13.310 When a user visits the blog page you don't show them all thousand or let's say 10000 results all at 06:13.310 --> 06:15.570 once for every blog post that you've written. 06:15.650 --> 06:16.850 That would be overwhelming. 06:17.120 --> 06:20.400 And that's just there's too much information to display. 06:20.480 --> 06:26.900 Most likely what you'll do is have the first 10 posts and then I can click NEXT button and it will load 06:27.050 --> 06:29.270 from post 11 to 20. 06:29.560 --> 06:31.430 And you know I can keep clicking next. 06:31.430 --> 06:37.190 And so when you've probably seen that before it's called pagination so you can do that by rather than 06:37.190 --> 06:39.530 selecting everything from your database. 06:39.530 --> 06:45.630 You could select from zero and I only want the first 10 and then the next time through. 06:45.710 --> 06:48.900 You could select from 10 and I want 10 more. 06:49.400 --> 06:50.110 And so on. 06:50.330 --> 06:54.350 So that is a situation where you could use that limit and provide a range like that. 06:54.530 --> 06:57.530 But again most of the time I just use this limit. 06:57.530 --> 07:02.840 And then one number the very last thing I'll show you is that this is actually straight from the docs 07:02.840 --> 07:03.660 from my ask you. 07:03.800 --> 07:10.070 I think it's really weird that they even specify this if you wanted to select from one row all the way 07:10.070 --> 07:11.610 to the end of the table. 07:12.020 --> 07:15.170 So I went from Row 5 all the way to the end. 07:15.170 --> 07:17.410 We might have however many thousand rows. 07:17.600 --> 07:20.090 There isn't a nice elegant way of doing it. 07:20.180 --> 07:21.880 This is what you have to do. 07:22.010 --> 07:28.160 So Star for whatever you're selecting from whatever table limit and then where you want to start Khama 07:28.340 --> 07:29.710 some gigantic number. 07:29.820 --> 07:33.520 And I'm not kidding you actually have it in the docs here. 07:33.790 --> 07:40.820 Ray is right here to retrieve all rows from a certain offset up to the end of the results that you can 07:40.820 --> 07:43.060 use some large number for the second perimeter. 07:43.950 --> 07:47.140 So that's their official suggestion on how to do it. 07:47.150 --> 07:52.680 So let's start from table limit 95 comma some gigantic number that you would know you'd never have that 07:52.680 --> 07:54.530 many rows in your table. 07:54.660 --> 08:02.350 So we could actually do that here if we just do a select title from books. 08:02.520 --> 08:05.670 Order by and let's just say I want to go. 08:06.000 --> 08:07.310 We didn't even need the order by honest. 08:07.320 --> 08:08.480 That's just a limit. 08:08.720 --> 08:14.230 And if we just you limit 5 that's going to give us the first five. 08:14.460 --> 08:19.580 But if I want to go from 5 to the end I can do limit 5. 08:20.070 --> 08:21.980 And then some gigantic number. 08:22.220 --> 08:25.640 And in our case it doesn't even have to be that again because we don't have that many books. 08:26.100 --> 08:33.430 But if I do that it gives us everything after five or taking 3 6 because it starts at zero. 08:33.570 --> 08:35.230 So I could reduce this down. 08:35.310 --> 08:40.250 I could just do like 50 and give us the same thing because we don't have 50 books. 08:40.260 --> 08:41.910 So just goes until the end. 08:42.420 --> 08:48.450 So that's it to limit with all those weird things they showcased the odd situations in ways you can 08:48.450 --> 08:49.020 use it. 08:49.050 --> 08:55.660 The most common and the way that almost always said I use it is this just limit and then a single number. 08:55.730 --> 08:56.200 All right. 08:56.250 --> 08:57.570 So that's it. 08:57.690 --> 08:58.940 Moving on to one more. 08:59.070 --> 09:02.070 And then we're done for the section at least. 09:02.070 --> 09:03.560 We're not done with the whole course.