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
AJ Nunez
Courses Plus Student 1,269 PointsReporting With SQL Challenge 2 use the OFFSET keyword in conjunction with the LIMIT keyword, issue with my query?
Can you help me to understand what's going on with my query? please
Ex: Imagine you're developing a Contacts application on a phone. You have a database with a phone_book table. It has the columns, first_name, last_name and phone. 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.
My answer: select * from phone_book order by last_name asc, first_name desc limit 20 offset 20;
7 Answers
Matthew Krzyzynski
7,713 PointsHey AJ!
When I completed this quiz, I removed ASC and DESC in my query. Also, since it specifies to retrieve the 3rd page in the results, you'll want to OFFSET 40 instead of 20.
SELECT * FROM phone_book ORDER BY last_name, first_name LIMIT 20 OFFSET 40;
See if that works for you!
Matthew Krzyzynski
7,713 PointsHey Francis,
Since each page contains only 20 contacts, you'll want to offset the first 40 because you're trying to retrieve the 3rd page of results only.
Hope that helps!
AJ Nunez
Courses Plus Student 1,269 PointsThank you!
Francis Ansu
4,326 Pointsit worked but Why do I have to OFFSET 40 instead of 20 ?
Calvin . T Rupango
6,083 PointsThe reason why you would have to offset 40 instead of 20 @ Francis Ansu is because of the logic of the question is asking you to to retrieve the 3rd page of results from the phone_book table hence from the equation of the offset of pages according to the lecture on pagination. She explained clearly on this : (offset -1) *number of items hence this is my working to make it easy for you to understand the logic:: (1-1)*20=0 thus the first page its going to skip no items. (2-1)*20=20 thus we are going to skip first 20 items on page 2. (3-1)*20=40 thus we are going to skip first 40 items on page 3. That's the reason why we had to offset 40 instead of 20. Hope it will help you.
Yung Kazi
6,997 PointsHey hopefully, this clears the issue of the problem being asked about why we have 40 OFFSET instead of 20. At first, I did not understand why the OFFSET was 40, but then looking at it again, I noticed that:
- Skipping under 20 rows means you're starting on the 1st page.
- Skipping over 20 rows means you're starting on the 2nd page.
- Skipping over 40 rows means you're starting on the 3rd page.
- Skipping over 60 rows means you're starting on the 4th page.
Since the objective is to retrieve the 3rd page of results from the phone_book table, we want to skip over 40 rows because it will get us to the third page using the logic above. (There is a limitation to show 20 contacts per page btw)
The syntax for the answer will need to look like this:
SELECT <column> FROM <table> ORDER BY <column> LIMIT <# of rows> OFFSET <# skipped rows>
SELECT * FROM phone_book ORDER BY last_name, first_name LIMIT 20 OFFSET 40;
Hope that helps!
HIDAYATULLAH ARGHANDABI
21,058 PointsSELECT * FROM phone_book ORDER BY last_name, first_name LIMIT 20 OFFSET 40;