Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Reporting with SQL Ordering, Limiting and Paging Results Paging Through Results

Takyi Akwah
PLUS
Takyi Akwah
Courses Plus Student 1,049 Points
Posted by Takyi Akwah
Takyi Akwah
Courses Plus Student 1,049 Points

SQL Challenge task issue

Hi All,

I am having an issue with a challenge task.

Here is the question:

The phone has a technical limitation to show 20 contacts on a screen at a time. Write the SQL query to retrieve the 3rd page of results from the phone_book table. Contacts are ordered by last name and then first name.

This is the code I have used but the system doesn't accept it

SELECT * FROM phone_book ORDER BY last_name, first_name LIMIT 60 OFFSET 40;

Where have I gone wrong? The system says that 60 results were returned instead of 20.

3 Answers

Balazs Peak
Balazs Peak
46,160 Points
Balazs Peak
Balazs Peak
46,160 Points

They are very similar exercises, but with different numbers.

LIMIT specifies how many records you take from the result set.

OFFSET specifies how many records you set aside from the result set before you start taking as many as the LIMIT parameter, or if there is no limit parameter, all there is left.

another words: LIMIT 5 OFFSET 10 means that from the result set, I do not want the first 10, and then I want the next 5. (And I do not want any others that are left after the first 15.)

Balazs Peak
Balazs Peak
46,160 Points
Balazs Peak
Balazs Peak
46,160 Points

By using the LIMIT keyword, you specify how many records should be returned. Since - according to the specifications - you can only display 20 records at once, you should use LIMIT 20 to limit the number of records.

SELECT * FROM phone_book ORDER BY last_name, first_name LIMIT 20 OFFSET 40;
Takyi Akwah
Takyi Akwah
Courses Plus Student 1,049 Points
Takyi Akwah
Takyi Akwah
Courses Plus Student 1,049 Points

Thank you very much. That worked. However, I am left a bit confused.

I have another query challenge here which I got correct. But I dont see the difference between the two questions:

In a library database there's a books table. There's a title, author, genre and first_published column.

The library database is connected to a website displaying 10 books at a time, sorted by the title alphabetically.

Write a query to bring back the second page of results. Please retrieve all columns of information.

This was the code I used to get the correct answer:

SELECT * FROM books ORDER BY title LIMIT 20 OFFSET 10;

How are the two different?

After typing all this I now understand why this is the case with the two challenges but would still like some explanation If you can to further understand.

Kind regards,

Takyi