LIMIT, FETCH & TOP (Pagination) 📑¶

Prerequisites: ORDER BY Clause

Mentor's Note: Imagine searching for something on Google. You don't see 1 billion results at once; you see 10 at a time. This is "Pagination". In SQL, we use different commands depending on the database to achieve this. 💡

🌟 The Scenario: The Hall of Fame 🏆¶

Imagine you have 1,000 players in a game. - The Goal: You only want to show the "Top 3" players on the leaderboard. - The Process: First, sort them by score (ORDER BY), then tell the computer to "Stop after 3 rows". ✅

💻 1. Multi-Dialect Syntax¶

A. MySQL & PostgreSQL (LIMIT)¶

The most common and easiest syntax.

SELECT * FROM players
ORDER BY score DESC
LIMIT 3;

B. Oracle (FETCH FIRST)¶

The modern ISO-standard way.

SELECT * FROM players
ORDER BY score DESC
FETCH FIRST 3 ROWS ONLY;

C. SQL Server (TOP)¶

SELECT TOP 3 * FROM players
ORDER BY score DESC;

💻 2. Offset (Page 2, Page 3...) 🪜¶

To see the next set of results (e.g., rows 4 to 6), we use an OFFSET.

MySQLOracle

-- Skip 3 rows, take the next 3
SELECT * FROM players LIMIT 3 OFFSET 3;

-- Skip 3, take next 3
SELECT * FROM players
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

🎨 Visual Logic: The Sliding Window¶

Full Table: [1, 2, 3, 4, 5, 6, 7, 8, 9]
LIMIT 3:    [1, 2, 3]
OFFSET 3:   [_, _, _, 4, 5, 6]

⚠️ Important Rule¶

Always use ORDER BY with LIMIT! Without a sort order, the database might give you a different set of "Top 3" every time you run the query.