Assign sequential numbers in SQL Server

Here is a cool little SQL Server tip from the April GP Reports Viewer newsletter. Say you have a list of rows and you want to assign sequential numbers to them, so that you always know which one is first, second, etc. Or so that you can display line numbers. This is often a need for Dynamics GP Sales Order Processing (SOP) transactions because they use line item sequence numbers that are very spread out to allow for inserting rows in-between them. (More detail on this from Dynamics GP MVP Mariano Gomez.)

Here is an example of an SOP transaction in GP:

Using the following SQL code you can see this data in SQL:

select SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR
from SOP10200 where SOPNUMBE = 'ORDST2227'

The results look like this:

The LNITMSEQ column is what determines the order of the lines. If you wanted to know which line is 1st, 2nd, 3rd, that would be a little difficult. The code below will add a sequential line number to this data using the line item sequence to determine the order of the lines:

select
row_number() over (partition by SOPNUMBE, SOPTYPE
 order by LNITMSEQ) LineNumber,
SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR
from SOP10200
where SOPNUMBE = 'ORDST2227'

Now your results will include a line number column:

For more tips like this sign up for the GP Reports Viewer newsletters. You can see past newsletters on the GP Reports Viewer website.