Beginner

687. JOIN with FETCH FIRST

Learn how FETCH FIRST can limit rows before a JOIN.

📘 Lesson 687 of 728 ⏱ 14 min 🎯 Advanced

FETCH FIRST restricts a query to a specified number of rows and is supported by several modern SQL systems.

It provides a standard-style approach to row limiting in supported database systems.

Select the ten latest transactions before adding customer information.

SQL 
SELECT
    c.CustomerName,
    x.TransactionID,
    x.TransactionDate
FROM Customers c
INNER JOIN (
    SELECT
        TransactionID,
        CustomerID,
        TransactionDate
    FROM Transactions
    ORDER BY TransactionDate DESC
    FETCH FIRST 10 ROWS ONLY
) x
    ON c.CustomerID = x.CustomerID;

Used in recent-activity reports, controlled data extraction, and analytical queries.

  • FETCH FIRST can limit returned rows.
  • ORDER BY determines which rows are selected.
  • Support varies by database system.
  • It can be used in derived tables where supported.
  • Global limits differ from per-group limits.
What is FETCH FIRST used for?

It limits the number of rows returned by a query.

Why is ORDER BY important with FETCH FIRST?

Without a defined ordering, the selected rows may not represent the intended records.

Have you finished this lesson?

Introduction to SQL – All Lessons