🧠 ROW_NUMBER(): Assign a Unique Number to Each Row

Xorthax can now pull values from specific positions. But what if he wants to label each row in order?

“Which row is first?” “Which row is second?”

That’s what ROW_NUMBER() does.

🚀 ROW_NUMBER() + OVER()

ROW_NUMBER() assigns a unique number to each row in a sequence.

ROW_NUMBER() OVER (
  PARTITION BY column
  ORDER BY column
)

This works because:

• PARTITION BY defines the group
• ORDER BY defines the sequence
• Each row gets a unique position

📊 Example: Number Orders per Customer

🧾 Sample Data

SELECT CustomerID, OrderDate, Amount,
       ROW_NUMBER() OVER (
         PARTITION BY CustomerID
         ORDER BY OrderDate
       ) AS RowNum
FROM Orders;

Result:

Each row now has a position within its customer group.

🧠 What’s Happening Here?

PARTITION BY CustomerID creates a group for each customer.

ORDER BY OrderDate defines the sequence within each group.

ROW_NUMBER() assigns a number starting at 1 and increases by 1 for each row.

The numbering resets for each group.

⚠️ Important: If two rows have the same value in the ORDER BY column, the order between them is not guaranteed.

That means ROW_NUMBER() will still assign unique numbers, but the order of those tied rows may change each time the query runs.

To make results consistent, add additional columns to the ORDER BY.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, ROW_NUMBER() OVER (ORDER BY orderdate) AS order_number FROM orders ORDER BY orderdate;

➡️ Next Step

ROW_NUMBER() gives each row a unique position.

Next, you’ll learn how to rank rows when there are ties.

Want to go deeper?