Hi, I a newbie to the forum & informix.

I am using informix V10.x

I need to generate a sequence # based set of records and I am drawing a blank, a sample set of the records are:

Col1 Col2
2006 5
2006 5
2006 5
2006 6
2006 6

I need to generate with a select statement:

Col1 Col2 Seq
2006 5 1
2006 5 2
2006 5 3
2006 6 1
2006 6 2

Any ideas would be greatly appreciated and Thanks in advance for any help.

Try something like this. I find it more reliable to create your own ROWIDs, rather than using the tables own ROWIDs.

This line creates an exact duplicate of your original table, except this table will contain a row_id column.

CREATE TABLE orig_tmp
(row_id CHAR(10), Col1 CHAR(4), Col2 CHAR(1));

This line creates the orig_tmp_1 table, which will include the minrow, col1 and col2 columns.

CREATE TEMP TABLE orig_tmp_1
(minrow CHAR(10), Col1 CHAR(4), Col2 CHAR(1)) WITH NO LOG;

This line selects the table's own rowid, col1 and col2 from your original table into orig_tmp.

INSERT INTO orig_tmp
SELECT rowid, col1, col2 FROM original_table;

This line is creating your sequence that increments by 1 and starts with 1.

CREATE SEQUENCE seq_2
INCREMENT BY 1 START WITH 1;

This line will update the row_id of the orig_tmp table.

UPDATE orig_tmp
SET row_id = seq_2.NEXTVAL;

This line will select the minimum rowid, col1 and col2 from the orig_tmp table and place that data into the orig_tmp_1 table and group the data by
col1 and col2.

INSERT INTO orig_tmp_1
SELECT MIN(row_id) minrow, Col1, Col2
FROM orig_tmp
GROUP BY col1, col2;

You'll need to join the tables from this point. I didn't include the script for the join. Anyway, you'll need to use something like this

(a.row_id-b.minrow))) as seq_num to obtain your unique values.

Hi,

That worked great thanks. Just what I needed.