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

Takyi Akwah
PRO
Takyi Akwah
Pro 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 Pukli
Balazs Pukli
46,040 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 Pukli
Balazs Pukli
46,040 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
Pro 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