🎯 SQL FETCH – Retrieve a Specific Number of Rows in a Query (With Examples)

Looking for a more flexible and ANSI-standard alternative to LIMIT in SQL?
Meet FETCH FIRST, a powerful way to retrieve the top rows from your query results—especially useful when building paginated applications or generating ranked reports.

---

🧾 What is SQL FETCH FIRST?

The FETCH clause allows you to retrieve a specific number of rows from a sorted query result. It is often used in combination with OFFSET for pagination and is part of the SQL:2008 standard, making it portable across many modern databases.

---

📚 Syntax of SQL FETCH FIRST

SELECT column1, column2, ...
FROM table_name
ORDER BY column
OFFSET n ROWS
FETCH FIRST m ROWS ONLY;

• OFFSET n ROWS skips the first n rows
• FETCH FIRST m ROWS ONLY returns the next m rows
• Can also use: FETCH NEXT m ROWS ONLY (same result)

📌 Both OFFSET and FETCH must follow an ORDER BY clause.

---

✅ Example 1: Return Top 3 Highest Salaries

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

This will return the top 3 earners in the employees table.

---

🔁 Example 2: Pagination – Skip 5 and Get Next 5

SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

🟢 This skips the first 5 rows and fetches the next 5—great for page 2 of a paginated list.

---

⚙️ Database Compatibility

Database	FETCH Support	Notes
PostgreSQL	✅ Fully Supported	Use with ORDER BY and OFFSET
Oracle 12c+	✅ Fully Supported	Native FETCH FIRST support
SQL Server	✅ (2012+)	Requires ORDER BY
MySQL	❌ Use LIMIT instead	MySQL does not support FETCH
SQLite	❌ Use LIMIT instead	Not supported

---

✅ FETCH vs LIMIT: What’s the Difference?

Feature	LIMIT	FETCH (Standard SQL)
Compatibility	MySQL, PostgreSQL	PostgreSQL, Oracle, SQL Server
Standard SQL	❌ Non-standard	✅ Yes (SQL:2008)
Readability	✅ Shorter	✅ More expressive
Usage	LIMIT 5 OFFSET 10	OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

---

🧪 Practice Challenge

Question: Retrieve the second page (rows 6–10) of top products sorted by rating.

SELECT product_name, rating
FROM products
ORDER BY rating DESC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

---

📝 Final Thoughts

The SQL FETCH clause is a powerful, modern way to:

• Retrieve a limited number of rows
• Enable pagination
• Work with large sorted datasets efficiently

✅ Use it with ORDER BY and OFFSET
✅ Preferred when writing standards-compliant SQL