Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Poland, ME

    Unhappy Unanswered: Row Seqeuence # Question

    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.

  2. #2
    Join Date
    May 2008
    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.


    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.
    Last edited by skisalomon77; 08-10-09 at 17:50.

  3. #3
    Join Date
    Aug 2009
    Poland, ME

    That worked great thanks. Just what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts