Bird
Raised Fist0
PostgreSQLquery~30 mins

FETCH FIRST for SQL standard pagination in PostgreSQL - Mini Project: Build & Apply

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Using FETCH FIRST for SQL Standard Pagination
📖 Scenario: You are managing a small bookstore database. You want to show the list of books to customers page by page, so they can easily browse without being overwhelmed by too many books at once.
🎯 Goal: Build a SQL query that uses the FETCH FIRST clause to limit the number of rows returned, simulating pagination.
📋 What You'll Learn
Create a table called books with columns id (integer), title (text), and author (text).
Insert exactly 5 books with specified titles and authors.
Write a query to select all columns from books ordered by id ascending.
Add a configuration variable page_size to control how many rows to fetch.
Use FETCH FIRST with page_size to limit the number of rows returned.
💡 Why This Matters
🌍 Real World
Pagination is used in websites and apps to show data in small chunks, improving user experience and performance.
💼 Career
Knowing how to paginate data with SQL is essential for backend developers, data analysts, and anyone working with databases to build efficient and user-friendly applications.
Progress0 / 4 steps
1
Create the books table and insert data
Create a table called books with columns id (integer), title (text), and author (text). Then insert these 5 books exactly: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), (3, 'Pride and Prejudice', 'Jane Austen'), (4, 'To Kill a Mockingbird', 'Harper Lee'), and (5, 'The Great Gatsby', 'F. Scott Fitzgerald').
PostgreSQL
Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the books.

2
Set the page size variable
Create a variable called page_size and set it to 3. This will control how many books to show per page.
PostgreSQL
Hint

In PostgreSQL psql shell, use \set page_size 3 to define a variable. If using another tool, just note the variable name and value.

3
Write the query to select books ordered by id
Write a SQL query to select all columns from books ordered by id ascending.
PostgreSQL
Hint

Use SELECT with ORDER BY id ASC to get the books sorted by their id.

4
Add FETCH FIRST clause to limit rows
Add FETCH FIRST with page_size rows only to the query to limit the number of books returned per page.
PostgreSQL
Hint

Use FETCH FIRST :page_size ROWS ONLY to limit the rows returned to the value of page_size.

Practice

(1/5)
1. What does the SQL clause FETCH FIRST 5 ROWS ONLY do in a query?
easy
A. Limits the query result to only the first 5 rows.
B. Skips the first 5 rows and returns the rest.
C. Returns all rows except the first 5.
D. Sorts the rows by the first 5 columns.

Solution

  1. Step 1: Understand FETCH FIRST clause

    The clause FETCH FIRST 5 ROWS ONLY limits the number of rows returned by the query to 5.

  2. Step 2: Effect on query result

    This means only the first 5 rows from the result set will be returned, no more.

  3. Final Answer:

    Limits the query result to only the first 5 rows. -> Option A

  4. Quick Check:

    FETCH FIRST limits rows = A [OK]
Hint: FETCH FIRST means limit rows returned, not skip [OK]
Common Mistakes:
  • Confusing FETCH FIRST with OFFSET
  • Thinking FETCH FIRST sorts rows automatically
  • Assuming FETCH FIRST removes rows instead of limiting
2. Which of the following is the correct syntax to get the first 10 rows from a table named employees in PostgreSQL?
easy
A. SELECT * FROM employees FETCH 10 ROWS;
B. SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;
C. SELECT * FROM employees LIMIT FIRST 10;
D. SELECT * FROM employees TOP 10;

Solution

  1. Step 1: Recall correct FETCH FIRST syntax

    The standard SQL syntax to limit rows is FETCH FIRST n ROWS ONLY. PostgreSQL supports this.

  2. Step 2: Check each option

    SELECT * FROM employees FETCH FIRST 10 ROWS ONLY; uses correct syntax. Options A, B, and D are invalid in PostgreSQL.

  3. Final Answer:

    SELECT * FROM employees FETCH FIRST 10 ROWS ONLY; -> Option B

  4. Quick Check:

    Correct FETCH FIRST syntax = C [OK]
Hint: Use FETCH FIRST n ROWS ONLY for standard limit [OK]
Common Mistakes:
  • Using LIMIT FIRST instead of FETCH FIRST
  • Using TOP keyword which is SQL Server syntax
  • Omitting ROWS ONLY after FETCH FIRST
3. Given the table products with columns id and price, what will this query return?
SELECT id FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
medium
A. All products sorted by price descending.
B. The 3 products with the lowest prices.
C. The 3 products with the highest prices.
D. Syntax error due to FETCH FIRST usage.

Solution

  1. Step 1: Analyze ORDER BY clause

    The query orders products by price in descending order, so highest prices come first.

  2. Step 2: Apply FETCH FIRST 3 ROWS ONLY

    This limits the output to only the first 3 rows after sorting, i.e., top 3 highest priced products.

  3. Final Answer:

    The 3 products with the highest prices. -> Option C

  4. Quick Check:

    ORDER BY DESC + FETCH FIRST = top rows [OK]
Hint: ORDER BY DESC + FETCH FIRST = top N highest [OK]
Common Mistakes:
  • Ignoring ORDER BY and thinking it returns random rows
  • Confusing DESC with ASC order
  • Assuming FETCH FIRST causes syntax error
4. Identify the error in this query:
SELECT name FROM customers FETCH FIRST 5 ROWS;
medium
A. Missing ORDER BY clause before FETCH FIRST.
B. No error; query is valid.
C. FETCH FIRST cannot be used without OFFSET.
D. Incorrect FETCH FIRST syntax; missing 'ONLY'.

Solution

  1. Step 1: Check FETCH FIRST syntax

    The correct syntax requires ROWS ONLY after the number, so it should be FETCH FIRST 5 ROWS ONLY.

  2. Step 2: Verify other parts

    ORDER BY is recommended but not mandatory. OFFSET is not required. So the error is missing 'ONLY'.

  3. Final Answer:

    Incorrect FETCH FIRST syntax; missing 'ONLY'. -> Option D

  4. Quick Check:

    FETCH FIRST needs ROWS ONLY [OK]
Hint: FETCH FIRST must end with ROWS ONLY [OK]
Common Mistakes:
  • Omitting 'ONLY' after FETCH FIRST clause
  • Thinking ORDER BY is mandatory with FETCH FIRST
  • Confusing FETCH FIRST with OFFSET usage
5. You want to show page 3 of a product list with 10 items per page, sorted by created_at ascending. Which query correctly uses FETCH FIRST and OFFSET for this pagination?
hard
A. SELECT * FROM products ORDER BY created_at ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;
B. SELECT * FROM products ORDER BY created_at ASC FETCH FIRST 10 ROWS OFFSET 20 ROWS ONLY;
C. SELECT * FROM products ORDER BY created_at ASC FETCH FIRST 30 ROWS ONLY;
D. SELECT * FROM products ORDER BY created_at ASC OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;

Solution

  1. Step 1: Calculate OFFSET for page 3

    Each page has 10 items, so page 3 starts at row 21. OFFSET is zero-based, so OFFSET = (3-1)*10 = 20.

  2. Step 2: Use FETCH FIRST to limit rows per page

    FETCH FIRST 10 ROWS ONLY limits the result to 10 rows for the page.

  3. Step 3: Check query syntax order

    OFFSET must come before FETCH FIRST. SELECT * FROM products ORDER BY created_at ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY; correctly orders OFFSET then FETCH FIRST.

  4. Final Answer:

    SELECT * FROM products ORDER BY created_at ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY; -> Option A

  5. Quick Check:

    OFFSET before FETCH FIRST for pagination [OK]
Hint: OFFSET = (page-1)*size; FETCH FIRST = page size [OK]
Common Mistakes:
  • Swapping OFFSET and FETCH FIRST order
  • Using FETCH FIRST without OFFSET for pages >1
  • Incorrect OFFSET calculation for page number
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard