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

Rhea Khanna
Rhea Khanna
2,838 Points

Paging with SQL, Limit/ Offset

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 last_name, first_name FROM phone_book LIMIT 20 OFFSET 59;

I am getting this question wrong, can someone explain what I am doing wrong please

3 Answers

Erasto Oraro
Erasto Oraro
5,216 Points

I think the formula for calculating the offset should be: $offset = ($page - 1) * $contacts_per_page;

In your case: offset = (3- 1) * 20 = 40 ;

Rhea Khanna
Rhea Khanna
2,838 Points

I'm still not quite sure what that means sorry

Erasto Oraro
Erasto Oraro
5,216 Points

SELECT last_name, first_name FROM phone_book LIMIT 20 OFFSET 40;

Rhea Khanna
Rhea Khanna
2,838 Points

I haven't been taught the formula to calculate the offset the way you did but I also tried putting the answer you gave me and it still is incorrect, I have to put in the keyword ORDER BY?

Erasto Oraro
Erasto Oraro
5,216 Points

Sorry... I think this is close to what you are looking for.

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