ROWNUM equivalent in Centura SQLBase (aka Gupta)

I have a Centura SQLBase (version 7, so it is over 10 years old; now apparently they changed the name back to Gupta) where I need to put a running number into a column while copying data into a temporary table.

The running number has to restart when a grouping code changes (so each group has its own counter), the data has to be ordered by a date column.

This is a conversion task. For testing (UAT) I only have to convert parts of the database (though it isn't really big, so I just want to convert all). For go-live, I will do a final conversion and the database will be disabled. So to be exact: it is a two-times task. Though not mission critical any outage is annoying for the users, as they have to switch to papers and then later key-in (instead of immediately key-in)

In essence I have this data:

employee_no,date,group_code

and I have to generate:

employee_no,date,group_code,group_running_number

The employee_no + date + group_code is the PK.

In Oracle, I would use an analytic function and get it done in one step, but in SQLBase I even cannot find something like "rownum".

I considered using the SYSDBSequence.nextval sequence, but it stayed the same for a single insert (which probably makes sense).

I considered using their ROWID (which contains a unique row number and I know how to pick that out), but I cannot use the ORDER BY in the INSERT-SELECT combo. (When creating views ORDER BY isn't allowed either)

One last resort, if I cannot solve this with SQL, would be using a spreadsheet. There are only about 400,000 rows in total, but it certainly would be rather slow and error prone. On the final conversion day I want to have as few steps as possible.

If I cannot find a rownum equivalent I might try writing a procedure in sqltalk doing RBAR processing.

One way to emulate row numbers in DBMS that do not have window function is using a triangular self join. It's usually not very efficient though.

Given that the primary key is (employee_no, date, group_code):

SELECT
    a.employee_no, a.date, a.group_code,
    COUNT(*) AS group_running_number
FROM
    tableX AS a
  JOIN
    tableX AS b
      ON  a.group_code = b.group_code
      AND ( a.date > b.date
         OR a.date = b.date AND a.employee_no >= b.employee_no
          )
GROUP BY
    a.group_code, a.date, a.employee_no ;

The order used for the assigning of row numbers above is: (date ASC, employee_no ASC)

An index on (group_code, date, employee_no) will improve efficiency I think.

StackExchange Database Administrators Q#49382, answer score: 3