Assigning Sequence Numbers

[Guest]

I need to assign sequence numbers in multiples of 10 to a BOM file that I am
creating. I have the following information in a current table:

ITEM COMPONENT QTY SEQ
1 XXXXX 1
1 YYYYY 4
1 ZZZZZ 2
2 YYYYY 3
2 XXXXX 1

I need to create an expression that allows me to populate the sequence
number to look like the following:

ITEM COMPONENT QTY SEQ
1 XXXXX 1 10
1 YYYYY 4 20
1 ZZZZZ 2 30
2 YYYYY 3 10
2 XXXXX 1 20

For some reason I can not seem to do this. Any suggestions????

Thanks!

 

[Tom Ellison]

Dear Jent:

Your sample data does not seem to be in any set sequence. At first, I
thought it was ordered by ITEM and COMPONENT, but the last two lines blew
that away.

If the rows are in an ordered sequence, then such a thing can really be
done.

Assuming you mistyped the sample data, and the last two lines are
mis-ordered and mis-numbered:

SELECT ITEM, COMPONENT, QTY
((SELECT COUNT(*) FROM YourTable T1
WHERE T1.ITEM = T.ITEM
AND T1.COMPONENT < T.COMPONENT)
+ 1) * 10 AS SEQ
FROM YourTable T
ORDER BY ITEM, COMPONENT

If you have two rows with the same values of ITEM and COMPONENT, they will
be in a tie, and the sequence will be the same for those two rows.

Tom Ellison