SQL Limiting the Result Set

I was stumped for awhile because I was trying to use the short version:

SELECT * FROM movies LIMIT 20, 10;

Instead of this:

SELECT *FROM movies LIMIT 20 OFFSET 10;

I would think Treehouse would let it pass either way. Oh well.

OFFSET Essentially means: Skip this many rows. So if I wanted to 10 rows, but to skip the first 5 I would do:

SELECT * FROM actors LIMIT 10 OFFSET 5

So if you wanted to show 100 results per page for example, your query for page 1 would be:

SELECT * FROM actors LIMIT 100;

Page 2:

SELECT * FROM actors LIMIT 100 OFFSET 100;

You offset 100 because you already showed the first 100 rows on page 1, on page 3:

SELECT * FROM actors LIMIT 100 OFFSET 200;

You skip the first 200 rows because they were displayed on Pages one and two.

Understand how it works? It literally means, skip this many results. If you wanted to read it out in english it would sound like:

Get me 100 rows from actors, but skip the first 200 rows because I dont need them.

E.g. get me rows 201 to 301

Your statement will get the 2nd to the 102nd rows. You want:

SELECT * FROM actors LIMIT 100 OFFSET 100

if you dont understand why let me know.

I think im misunderstanding OFFSET.

I was understanding LIMIT to be the number of items that would show on 1 page.

so if i did: SELECT * FROM actors LIMIT 100 OFFSET 0;

This would show the first 100 actors.

If i did: SELECT * FROM actors LIMIT 100 OFFSET 1;

This would show the 2nd 100 actors(101-200).

What am I misunderstanding?

Database Foundations

Challenge Task 1 of 2

Get the 101st to 200th actor from the actors table. Be sure to return every column. Also, there's no need to use any ordering.

SELECT * FROM actors LIMIT 100,100;

Challenge Task 2 of 2

Get the 11th to 30th movie from the movies table. Be sure to return every column. Also, there's no need to order the results.

Type in your command below, then press Enter.

SELECT * FROM movies LIMIT 20 OFFSET 10;

GREAT ARY,

Thanks! That explanation was perfect. I have a clear understanding of it now.

Still not really getting this myself.. what about for:

Get the 11th to 30th movie from the movies table. (Again no need to order the results).

??

I was confused with this as well. For some reason I thought the OFFSET was the "page" created by the LIMIT. I didn't realize it was the number or rows to OFFSET. Thanks for the explanation.

Bummer! There's something wrong with your SQL statement. Please review your code and try again. Try again

SELECT * FROM movies OFFSET 10 LIMIT 20; Checking your answer...

DOESN'T WORK FOR ME !!!!

IT RETURN SOMETHING BUT Bummer! The wrong movies were returned. Please check the numbers and try again. Try again

SELECT * FROM movies LIMIT 10 OFFSET 20; Checking your answer...

Shouldn't SELECT * FROM movies LIMIT 20, 10 work as well?

Database Foundations

Challenge Task 1 of 2

Get the 101st to 200th actor from the actors table. Be sure to return every column. Also, there's no need to use any ordering.

SELECT * FROM actors LIMIT 100,100;

Challenge Task 2 of 2

Get the 11th to 30th movie from the movies table. Be sure to return every column. Also, there's no need to order the results.

Type in your command below, then press Enter.

SELECT * FROM movies LIMIT 20 OFFSET 10;

GREAT ARY,