Next up in our toolbox is limit.
So limit allows us to specify a number for how many results that we want to select.
So I could say things like I want the first two best selling books rather than all best selling books
or the top 10 most recent books.
So we often use it in conjunction with order by sorting because that typically is going to give you
something meaningful rather than just saying select all books and give me the first two.
Well how was first defined and we can do that.
I'll show you that if we do a select let's just do title from books the order that we get them right
now and get us the order they were added.
So we have the namesake Norse mythology American Gods and so on.
So I could limit this and the syntax looks like this.
It just adds limit at the end just like we added order by limit.
I'd say three.
Now I just get those first three.
So it's going to limit it to the first three.
Whether they're sorted or not it doesn't care.
It just chops it off right there.
So again it's not that useful or meaningful just to do it on its own like this.
But we could do you know Limit 1 limit 10 and so on.
And that's basically all that there is to limit.
You know we can do it with a select star.
If I only want let's say everything about the first book there we go and you get the namesake and there's
everything about it.
But more often than not we're using it with order by.
So here's an example.
If I want the five most recently released books I'm going to and I only want the title and the release
year in this case.
So select title and release here from books then I'm going to order them by the released year descending
and then I'm going to limit it to the first five.
So let's try it.
I'll go ahead and make a new file again.
Limit that as well.
And in there I'll write a select title comma released year from books.
I won't add it yet.
Just make sure our source is working so we'll do a source refining.
Why the name so long finding selections.
Slash limit as kua.
Fortunately you only have to type it once.
OK so that's working.
So now what we'll do is order them by that released year so we'll do an order by and we could write
really sure and I will because it's more specific or is it's easier to understand more semantic to write
it out even though it's longer.
But of course we could replace it with two As we just saw in the last video.
So ordered by that release here.
If I do that now that gives us all the books ordered in ascending order based off of their year which
is not what we want so we can switch that with DGSE.
Here we go.
And now I can limit it to five.
And so that limit comes last as well.
So it's going to do everything and then chop it wherever you specify.
So that's important to know.
So let's try it now.
Make sure I save and there we go.
The five most recent books and of course we can change this to whatever number you want if you want
the one most recent book or if we wanted let's say 14.
We can do that.
So there are a few other ways we can use it.
For example here's the same query.
However I've written it slightly different so everything up until this point right here is the same
limit but this time I'm saying limit 0 comma 5.
So if we just copy this I won't make you watch me type that whole thing out.
And I comment this out and I tried running this.
Now just take a guess.
What do you think will happen.
And here we go.
It does the exact same thing.
So this is a syntax that allows us to specify a starting point and then how many we want to go from
there.
So basically it's saying and this is kind of confusing because it starts at zero.
So the first row in a table is zero with row.
But then when we're dealing with strings like when we had hello world.
Age is actually the first character in my ask well least.
So anyways it's saying I want the first row and I want to go for five rows so we could change that.
I only want three rows this time.
If we rerun it it gives us the first three rows.
But then I could shift it.
Let's say I want to go.
I don't know why but the second row onwards.
So it gives us north mythology 10 percent happier and the circle didn't get 10 before when it was zero.
We got Lincoln in the bardo Norse mythology and 10 percent happier.
When I changed this to 1 it shifts the starting point.
So it's not that really it's not common at all but you could do things like 10 comma 1.
And what this will do is go to the eleventh book.
I need to save first.
Now if I run it it will give us the eleventh book.
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
9 10 11 and we get fake book.
So if you need to access very particular rows based off of order that is a way that you can do it just
honestly not that common.
Pretty much every time that I use a limit it's in a context like this limit one or the first three of
the first five or something like that.
But I'll give you an actual real reason you might use it.
Let's say that you have a blog site that you've created and your blog has thousands of articles in it
already.
When a user visits the blog page you don't show them all thousand or let's say 10000 results all at
once for every blog post that you've written.
That would be overwhelming.
And that's just there's too much information to display.
Most likely what you'll do is have the first 10 posts and then I can click NEXT button and it will load
from post 11 to 20.
And you know I can keep clicking next.
And so when you've probably seen that before it's called pagination so you can do that by rather than
selecting everything from your database.
You could select from zero and I only want the first 10 and then the next time through.
You could select from 10 and I want 10 more.
And so on.
So that is a situation where you could use that limit and provide a range like that.
But again most of the time I just use this limit.
And then one number the very last thing I'll show you is that this is actually straight from the docs
from my ask you.
I think it's really weird that they even specify this if you wanted to select from one row all the way
to the end of the table.
So I went from Row 5 all the way to the end.
We might have however many thousand rows.
There isn't a nice elegant way of doing it.
This is what you have to do.
So Star for whatever you're selecting from whatever table limit and then where you want to start Khama
some gigantic number.
And I'm not kidding you actually have it in the docs here.
Ray is right here to retrieve all rows from a certain offset up to the end of the results that you can
use some large number for the second perimeter.
So that's their official suggestion on how to do it.
So let's start from table limit 95 comma some gigantic number that you would know you'd never have that
many rows in your table.
So we could actually do that here if we just do a select title from books.
Order by and let's just say I want to go.
We didn't even need the order by honest.
That's just a limit.
And if we just you limit 5 that's going to give us the first five.
But if I want to go from 5 to the end I can do limit 5.
And then some gigantic number.
And in our case it doesn't even have to be that again because we don't have that many books.
But if I do that it gives us everything after five or taking 3 6 because it starts at zero.
So I could reduce this down.
I could just do like 50 and give us the same thing because we don't have 50 books.
So just goes until the end.
So that's it to limit with all those weird things they showcased the odd situations in ways you can
use it.
The most common and the way that almost always said I use it is this just limit and then a single number.
All right.
So that's it.
Moving on to one more.
And then we're done for the section at least.
We're not done with the whole course.