winner

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:SOP Transaction example

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:

SQL Results - original

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:
SQL Final Results

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

Trackbacks/Pingbacks

  1. Assign sequential numbers in SQL Server | Victoria Yudin | DynamicAccounting.net - May 8, 2013

    [...] Victoria explains how to Assign sequential numbers in SQL Server [...]

    Like

  2. Assign sequential numbers in SQL Server – 5/7, Victoria Yudin | Partner Compete - May 7, 2013

    [...] Continue reading on Source Blog [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,566 other followers

%d bloggers like this: