SELECT columnlist, rn = ROW_NUMBER() OVER (PARTITION BY sales.salesID ORDER BY payment.paymentID) FROM sales JOIN payments ON sales.salesID=payments.salesID
Here is what the above code is Doing:
1. The first line is the SELECT statement.
2. The second line is the ROW_NUMBER() function.
3. The third line is the OVER clause.
4. The fourth line is the PARTITION BY clause.
5. The fifth line is the ORDER BY clause.
The ROW_NUMBER() function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the PARTITION BY clause.
The PARTITION BY clause is optional. If you omit the PARTITION BY clause, the ROW_NUMBER() function treats the entire result set as a single partition.
The ORDER BY clause is mandatory and must appear right after the OVER clause.
The ROW_NUMBER() function is useful for pagination in applications.