SQL Pagination with LIMIT and OFFSET

When working with large datasets in a database, it's common to retrieve data in smaller, manageable chunks rather than loading everything at once. This technique, known as pagination, significantly improves application performance and user experience. This challenge will focus on implementing pagination using the widely supported LIMIT and OFFSET clauses in SQL.

Problem Description

Your task is to write a SQL query that retrieves a specific "page" of data from a table. You will be given the table name, the desired page number, and the number of items to display per page. The query should order the results consistently to ensure that pages are displayed in the correct sequence.

Key Requirements:

• Retrieve a subset of records from a given table.
• The subset should correspond to a specific page number.
• The number of records per page should be configurable.
• Results must be ordered deterministically to ensure consistent pagination.

Expected Behavior:

The query should return only the records belonging to the requested page. For instance, if items_per_page is 10 and you request page_number 2, the query should return records 11 through 20 (assuming 1-based indexing for records).

Edge Cases to Consider:

• Requesting a page number that is beyond the total number of available records (should return an empty result set).
• Requesting page 1.
• What happens if items_per_page is 0 or a negative number? (While not explicitly handled by LIMIT/OFFSET in most SQL dialects, consider how your logic would adapt if you were responsible for input validation before constructing the query).
• What happens if the table is empty?

Examples

Let's assume we have a table named products with the following structure and data:

Example 1:

Input:

• table_name: products
• page_number: 1
• items_per_page: 5
• order_by_column: product_id (ascending)

Output:

[
  {"product_id": 1, "product_name": "Laptop", "price": 1200},
  {"product_id": 2, "product_name": "Keyboard", "price": 75},
  {"product_id": 3, "product_name": "Mouse", "price": 25},
  {"product_id": 4, "product_name": "Monitor", "price": 300},
  {"product_id": 5, "product_name": "Webcam", "price": 50}
]

Explanation: We want the first page of results, with 5 items per page, ordered by product_id. This means we select the first 5 records.

Example 2:

Input:

• table_name: products
• page_number: 3
• items_per_page: 3
• order_by_column: product_name (ascending)

Output:

[
  {"product_id": 6, "product_name": "Desk Lamp", "price": 40},
  {"product_id": 9, "product_name": "Microphone", "price": 100},
  {"product_id": 2, "product_name": "Keyboard", "price": 75}
]

Explanation: We want the third page of results, with 3 items per page, ordered by product_name.

• Page 1: Desk Lamp, External HDD, Keyboard
• Page 2: Laptop, Microphone, Monitor
• Page 3: Mouse, Printer, Speakers. (Self-correction during explanation: The example output above is incorrect based on the alphabetic ordering. The correct output for page 3 with 3 items per page ordered by product_name would be:

[
  {"product_id": 3, "product_name": "Mouse", "price": 25},
  {"product_id": 7, "product_name": "Printer", "price": 200},
  {"product_id": 8, "product_name": "Speakers", "price": 150}
]

Explanation: We want the third page of results, with 3 items per page, ordered by product_name. The records, when sorted alphabetically by product_name, are: Desk Lamp, External HDD, Keyboard, Laptop, Microphone, Monitor, Mouse, Printer, Speakers, Webcam.

• Page 1 (records 1-3): Desk Lamp, External HDD, Keyboard
• Page 2 (records 4-6): Laptop, Microphone, Monitor
• Page 3 (records 7-9): Mouse, Printer, Speakers.

Example 3 (Edge Case):

Input:

• table_name: products
• page_number: 5
• items_per_page: 3
• order_by_column: product_id (ascending)

Output:

[]

Explanation: There are 10 products in total. With 3 items per page, there are 4 full pages and one remaining item. Requesting page 5 should return an empty set as there are no records on that page.

Constraints

• The page_number will be a positive integer (>= 1).
• The items_per_page will be a positive integer (>= 1).
• The table_name will be a valid, existing table name.
• The order_by_column will be a valid, existing column name in the specified table.
• The database system supports LIMIT and OFFSET clauses.
• The order_by_column must be unique or a tie-breaker column must be provided to ensure deterministic ordering. For this challenge, assume product_id is a primary key and can be used as a tie-breaker if needed for consistent ordering.

Notes

• Remember that LIMIT and OFFSET often work with 0-based indexing for the offset. You will need to convert your 1-based page_number and items_per_page into the correct LIMIT and OFFSET values for your SQL dialect.
• Consider the impact of ordering. Without a deterministic ORDER BY clause, the results of pagination can be inconsistent.
• This challenge is language-agnostic in terms of the SQL dialect, but the core concepts of LIMIT and OFFSET are what you need to apply. You can write your solution as a general SQL query.