Part 25: Restricting Row Counts: TOP vs FETCH FIRST

Different database engines historically implemented row limiting differently before ANSI standardization.

1. ANSI Standard: OFFSET ... FETCH NEXT

Standardized in SQL:2008 and supported by Oracle, PostgreSQL, and SQL Server:

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

2. Microsoft SQL Server TOP

SELECT TOP (10) product_name, price 
FROM products 
ORDER BY price DESC;

Key Takeaways

• FETCH NEXT n ROWS ONLY is the ANSI standard.
• LIMIT / OFFSET is the most popular idiom in PostgreSQL, MySQL, and SQLite.

In Part 26, we will compare Offset-Based Pagination vs Keyset / Cursor Pagination!