CODEWITHSUNDEEP
rankrow-number
paygboy00
paygboy00

Reputation: 19

I would like to add a column rownumber counter based on the InvoiceId only in SQL

Based on the InvoiceId, I would like to count 1 to X and add those line item values next to each order line item.

Desired result wanted. Adding LineItem column, then counting 1 to X until next InvoiceId

InvoiceId Item Description LineItem
ABC 03944343 SPEAKER 1
ABC 03435555 WAVEMP4 2
XYZ 73843433 GENERATOR 1
XYZ 24543454 PLUG AC 2

Tried using RowNumber() but it is applying it to all records and not restarting to 1 from every new InvoiceId

Upvotes: 0

Views: 31

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23837

You can use row_number and partition by. ie:

select InvoiceId, Item, Description,
 row_numbe() over (partition by InvoiceId Order by Description) as LineItem
from myTable;

Upvotes: 1

Related Questions