<No Subject Supplied>

Quoting Tang Chang Thai..... < hi ... has anyone ever implemented a scheme to generate serial numbers that < stays within a group? I know that Informix supports serial number that is < table wide. However I require something like the following: < Table A < ------- < Col1 Col2 < --- ---- < 1 1 < 1 2 < 1 3 < 1 4 < 2 1 < 2 2 < 3 1 < 3 2 < etc. < Col1 is the group while the numbers in Col2 is serial within its group. < Would be grateful for any contributions. Try this..... LET WCol1 = Col1 LET WCol2 = NULL SELECT MAX(Col2) INTO WCol2 FROM <table> WHERE Col1 = WCol1 IF WCol2 IS NULL THEN LET WCol2 = 0 END IF LET WCol2 = WCol2 + 1 Now the only thing you need to be careful of is exceeding the maximum value for Col2 (e.g. trying to place a value > 32,767 into a SMALLINT). Hope this helps. Mark * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Mark E. Heringslake Phone (215) 824-3000 extension 249 * * CompuData, Inc. FAX (215) 824-4423 * * Philadelphia, PA. * * * * "If at first you don't succeed, hit it with a hammer. If that doesn't work * * use a bigger hammer and hit it harder. If it breaks, it needed fixing * * anyway." - Murphy (paraphrased) * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *