Re: Incrementing a count within a file by using SQL

[Michael_Schutte@xxxxxxxxxxxx]

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.