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.)
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'