Clarification: This is NOT MS SQL server.
[As there is no suitable tag for centura-database and I am not allowed creating tags I had to use this generic one - sorry about that]
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)
In essence I have this data: employee_no,date,group_code
and I have to generate: employee_no,date,group_code,group_running_number
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.