Feedback:

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!

Ex.

select 'SET1', ROWNUM() OVER(ORDER BY <AFIELD>), ID_2
from mytable
where ID_1 = 1
union all
select 'SET2', ROWNUM() OVER(ORDER BY <AFIELD>), ID_2
from mytable
where ID_1 = 2

This is something which can perfectly be done using MSSQL, where each set will have it's own numbering sequence...

13 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Ronald Hoek shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    3 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

        I think this has to be done:
        - created the result set
        - order the records
        - then apply the ROWNUM() to the result set ID-field

      • Adminmhagman (Admin, advantage) commented  ·   ·  Flag as inappropriate

        This may help?
        http://blog.advantageevangelist.com/2010/06/ads-10-tip-15-rownum.html
        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
        union all
        select * from #temp2

      Feedback and Knowledge Base