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 bestselling books rather than all bestselling books
or the top ten most recent books.
So we often use it in conjunction with order, buy or 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 is first defined?
And we can't 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 again is 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.
I just add limit at the end, just like we added order by limit.
Let's say three.
And 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 that meaningful just to do it on its own like this.
But we could do limit one, limit ten and so on.
And that's basically all that there is to limit.
We can do it with a select star if I only want let's say I want everything about the first book.
There we go.
I 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
here in this case.
So select title and release here from books.
Then I'm going to order them by the release 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, LTD SQL and in there I'll write a select title comma released
year from books.
I won't add limit yet.
Let's just make sure our source is working so we'll do a source refining.
Why did I name this so long?
Refining selections slash limit SQL.
Fortunately when we have to type it once, so that's working.
So now what we'll do is order them by that release year.
So we'll do an order by and we could write release here and I will because it's more specific or it's
easier to understand, it's 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 order 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 DSC.
There 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 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 is zero, comma five.
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 try running this.
Now just take a guess.
What do you think will happen?
And here we go.
It is 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 the zeroth row.
But then when we're dealing with strings, like when we had Hello World H is actually the first character
in my SQL at 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% happier.
And the circle.
If you didn't catch that before when it was zero.
We got Lincoln in the Bardo Norse mythology in 10%.
Happier when I change this to one, it shifts the starting point.
So it's not that really it's not comment at all.
But you could do things like ten comma one.
And what this will do is go to the 11th book.
Well, I need to save first.
Now, if I run it, it will give us the 11th book and just to make sure that we're right there, if
we rerun it now, we can count down one, two, three, four, five, six, seven, eight, nine, ten,
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.
It's just honestly not that common.
Pretty much every time that I use limit, it's in a context like this limit one or the first three or
the first five or something like that.
But I'll give you an actual a 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, let's say 10,000 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 ten posts and then I can click a next button and it will
load from post 11 to 20 and I can keep clicking next and so on.
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 ten and then the next time through you could select from ten and I want ten
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 SQL.
I think it's really clunky and 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 want from row five 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.
Select Star for whatever you're selecting from whatever table limit and then where you want to start
comma some gigantic number.
And I'm not kidding you.
I actually have it in the docs here.
Right?
Where is that.
Right here to retrieve all rows from a certain offset up to the end of the result set.
You can use some large number for the second parameter.
So that's their official suggestion on how to do it.
Select 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 don't even need the order by.
Honestly, let's just do a limit.
And if we just do limit five, that's going to give us the first five.
But if I want to go from five to the end, I can do limit five and then some gigantic number.
And in our case, it doesn't even have to be that gigantic.
So we don't have that many books.
But if I do that, it gives us everything after five or technically six because it starts at zero.
So I could reduce this down, I could just do like 50.
And it gives us the same thing because we don't have 50 books.
So it just goes until the end.
So that's it to limit with all those weird things that I showcased and the odd situations and ways you
can use it.
The most common, the way that almost always that I use it is this just limit and then a single number.
All righty.
So that's it.
Moving on to one more and then we're done for this section.
At least we're not done with the whole course.