Add a 'real' ROW_NUMBER() routine to the SQL syntax
In advantage there's are ROWNUM() function, but this does not ensure rownumbers are created in the required order (because the order by is processed AFTER the ROWNUM() routine has generated the actual numbers).
In MSSQL you can supply the order condition for generating the seqeunce numbers using the OVER statement.
Please add a OVER clause to the current ROWNUM() routine or create a ROW_NUMBER() routine which supports the OVER clause!
select 'SET1', ROWNUM() OVER(ORDER BY <AFIELD>), ID_2
where ID_1 = 1
select 'SET2', ROWNUM() OVER(ORDER BY <AFIELD>), ID_2
where ID_1 = 2
This is something which can perfectly be done using MSSQL, where each set will have it's own numbering sequence...
I think this has to be done:
- created the result set
- order the records
- then apply the ROWNUM() to the result set ID-field
The problem with this is, we need the numbers to be in order of the ODER BY clause en not by whatever way ADS adds the rows to the result set.
This is exactly what we do NOT want.
- the ID's must be in sequences order of the ORDER BY (like MSSQL)
Adminmhagman (Admin, advantage) commented
This may help?
Pulling from Chris' Blog "If there is an index that matches the ORDER BY then the results can be ordered prior to being added to the the resultset, if no index exists then the order will be applied after the results have been added to the resulset..."
More specfic to your actual example you would have to use temp tables to achieve this. Something similar to
select 'SET1', rownum(), id_2 into #temp from mytable where ID_1 = 1 order by <afield>
select 'SET2', rownum(), id_2 into #temp2 from mytable where ID_1 = 2 order by <afield>
select * from #temp
select * from #temp2