Re: Incrementing a count within a file by using SQL

select Record_type, part_number, rank() over(partition by Part_number
order by Part_number) as line_number
from file.

Hope this helps.



--

Michael Schutte
Admin Professional



Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is your
own backyard with Bob Evans Brats and Italian Sausage! For tasty recipes
using Bob Evans grilling sausage, visit http://www.BobEvans.com/Recipes



midrange-l-bounces@xxxxxxxxxxxx wrote on 07/16/2009 01:51:39 PM:

Afternoon all
I have a quandary that I'm hoping someone knows an answer to

I have a file
The file consists of 3 fields
Record type
Part number
Line number

For example, the data in this file is
Record type Part number Line number
H 0000000001 00000
S 0000000001 00000
D 0000000001 00000
D 0000000001 00000
A 0000000001 00000
C 0000000001 00000
M 0000000001 00000
H 0000000002 00000
S 0000000002 00000
D 0000000002 00000
H 0000000003 00000
S 0000000003 00000
D 0000000003 00000
D 0000000003 00000
D 0000000003 00000
D 0000000003 00000

Using SQL, how can I increment the line number in the file so that the
first record of each part number is 00001, but the subsequent records

(for

the same part number) is incremented by 1 for each record
so that the end result is
Record type Part number Line number
H 0000000001 00001
S 0000000001 00002
D 0000000001 00003
D 0000000001 00004
A 0000000001 00005
C 0000000001 00006
M 0000000001 00007
H 0000000002 00001
S 0000000002 00002
D 0000000002 00003
H 0000000003 00001
S 0000000003 00002
D 0000000003 00003
D 0000000003 00004
D 0000000003 00005
D 0000000003 00006
etc



Thanks in anticipation


Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.