An example of the rownumber() function
I recently worked on a report where I needed to assign sequential numbers like 1,2,3,4,5 to SOP line items instead of using the Dynamics GP assigned line item sequence numbers that we all know and love (16384, 32768, 49152, etc.).
The rownumber() function in SQL Server is perfect for this. Below is the formula I used:
select SOPNUMBE, SOPTYPE,
row_number() over (partition by SOPNUMBE, SOPTYPE
order by LNITMSEQ) LineNumber,
LNITMSEQ, ITEMNMBR
from SOP30300
You can easily adapt this formula to anything similar that you need.