Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

General Discussion

Limiting the result

Here is a question: Get the 101st to 200th actor from the actors table. (No need to use any ordering).

Here is my answer: SELECT * FROM actors LIMIT 199 OFFSET 100; or SELECT * FROM actors LIMIT 100, 199;

9 Answers

Andrew Chalkley
STAFF
Andrew Chalkley
Treehouse Guest Teacher

The question is asking to get 100 results from 101 to 200.

Your querying is limiting by 199 which will get the next 199 results after 100. So that's the 101st to the 299th.

Hope that helps.

Curious, though, as to why it's LIMIT 100 OFFSET 100.

We want to get the 101st actor first, right? So since it starts at zero, we set it to 100...

But the second bit...wouldn't setting it to 100 retrieve the 201st actor? Or does it "Drop-off" one because it starts indexing at zero?

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

Hey William J. Terrell,

LIMIT is just a number, a number of records, it's not an index of an "array" or set of records.

OFFSET is the starting "index" of where you want you results from so it starts at the 101st not the 100th And 101 to 200 is 100 results. It's not 99 because it includes the numbers 101 and 200 and everything in-between.

...aha thx...LIMIT 100,100;

Andrew Goddard
Andrew Goddard
40,806 Points

Hi Andrew,

There seems to be a bit of a bug in the Ordering the Result Set code challenge. When I type in the correct answer it says there is a problem connecting to the Code Challenge engine.

I've had this problem before when my internet connection was playing up but I don't think it's that this time - if I type the wrong answer in it tells me, so I figure it might be a problem with displaying the results of the search?

Thanks,

Andrew

Andrew Chalkley
STAFF
Andrew Chalkley
Treehouse Guest Teacher

If you report the issue to help@teamtreehouse.com and the app team can take a look. Give any additional info that you think would help like Browser/OS.

Ord Gernov
Ord Gernov
2,510 Points

SELECT * FROM MOVIES LIMIT 19 OFFSET 11;

I do not understand? It should start from 11th movie and than count 19 (to 30)?

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

For LIMIT 1 of your statement it would be the 12th movie.

LIMIT 1 = 12th
LIMIT 2 = 12th, 13th
LIMIT 3 = 12th, 13th, 14th
... LIMIT 19 = 12th, 13th, 14th, 15th, 16th, 17th, 18th, 19th, 20th, 21st, 22nd, 23rd, 24th, 25th, 26th, 27th, 28th, 29th, 30th

If you did OFFSET 1 you'd start at the 2nd movie so if you want 11 through 30 you need to OFFSET 10 and LIMIT 20.

Hope that helps.

SELECT * FROM actors LIMIT 100 OFFSET 100;

thank you

I think I've got it now... So, if we wanted to put it into plain English:

SELECT * FROM movies LIMIT 100,100;

Just means:

"Select 100 rows from the 'movies' table, starting after the first 100 rows."

Correct?

(This is also what I have in my notes, using an example from the SQL documentation:

SELECT * FROM movies LIMIT 5,10; "Select rows 6 through 15 from the 'movies' table." (So, "Select 10 rows starting after the first 5, correct?")

)

Thanks!

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,

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,