How to Number Rows Within Each Group in SQL?

SELECT ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date) AS row_num FROM orders;

This SQL query assigns a unique sequential integer to rows within a partition of a result set.

1. ROW_NUMBER() generates a unique number for each row in the result set.
2. OVER() defines the window for the row numbering operation.
3. PARTITION BY specifies the column(s) to group the data (e.g., customer_id).
4. ORDER BY determines the order of the rows within each partition (e.g., order_date).

• If there are ties in the ordering column, ROW_NUMBER() will still assign unique numbers.
• Using PARTITION BY with a single unique value results in a continuous sequence for all rows.
• If no ORDER BY is specified, the row numbering will be arbitrary.