LIMIT, FETCH & TOP (Pagination) 📑

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.

MySQL

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

Oracle

-- 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.

---

📈 Learning Path

• ← Back: DISTINCT Clause
• Next: Aliases (AS) →