Sql Commands for incremental values

[Rashid]

Hi all
Need your help to do this; I got a table with these records:

Supplier RegNo Status PoNumber
ABC sbh1309m 1
DCD sbt99x 1
FGJ sbg3939m 1
FGJ sbg3939m 1
OEE ey3939d 1

Need to have a sql command to transform to :

Supplier RegNo Status PoNumber
ABC sbh1309m 1 50001
DCD sbt99x 1 50002
FGJ sbg3939m 1 50003
FGJ sbg3939m 1 50003
OEE ey3939d 1 50004

Any ideas?
Thanks in advance.
Rashid.

[David Portas]

It would appear that your required output is based on a transitive
dependency: (supplier,regno) -> ponumber. Therefore that information should
really be normalised into a separate table:

CREATE TABLE PO (supplier CHAR(3), regno CHAR(8), ponumber INTEGER NOT NULL
UNIQUE, PRIMARY KEY (supplier,regno))

INSERT INTO PO (supplier, regno, ponumber)
SELECT S1.supplier, S1.regno,
50000+COUNT(DISTINCT S2.regno) AS ponumber
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.supplier > S2.supplier
OR (S1.supplier = S2.supplier
AND S1.regno >= S2.regno)
GROUP BY S1.supplier, S1.regno

--
David Portas
------------
Please reply only to the newsgroup