Beginner

616. JOIN with ROW_NUMBER

Use ROW_NUMBER with JOINed data to assign a unique sequential number to each row.

📘 Lesson 616 of 728 ⏱ 14 min 🎯 Advanced

ROW_NUMBER assigns a unique sequential number to rows based on the specified ordering, even when values are tied.

It is useful for selecting the first record, latest record, top record, or a specific row from each group.

Number each customer's orders from newest to oldest.

SQL 
SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    ROW_NUMBER() OVER (
        PARTITION BY o.CustomerID
        ORDER BY o.OrderDate DESC
    ) AS OrderNumber
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID;

Used in deduplication, latest-record selection, pagination, and customer transaction analysis.

  • ROW_NUMBER creates unique sequential numbers.
  • PARTITION BY creates numbering groups.
  • ORDER BY determines sequence.
  • Tied values still receive different row numbers.
  • Useful for latest-record queries.
Why use ROW_NUMBER with JOIN?

It can number related records within each group and help identify specific records such as the latest order.

Does ROW_NUMBER assign the same number to ties?

No. Each row receives a unique number within its partition.

Have you finished this lesson?

Introduction to SQL – All Lessons