CODEWITHSUNDEEP
oracle-databaseranking
So4ne
So4ne

Reputation: 1182

Ranking without an order by

Is there a way to create a row_number "on the fly" without ordering by a column? I've got this query :

select regexp_substr(',A-B,B-C,C-D', '[^,]+', 1, level) as legs
          from dual
        connect by regexp_substr(',A-B,B-C,C-D', '[^,]+', 1, level) is not null

And I need to keep the order of my rotation, but I also need a rank to be sure the rotation will be good. I tried to take a look near rank, dense_rank, row_number,... But they all need an order by, which I can't use.

It would give something like this

Rank   |   Legs  
 1     |   A-B
 2     |   B-C
 3     |   C-D

Upvotes: 0

Views: 173

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

You can just use the level, though you need to alias it:

select level as rnk,
  regexp_substr(',A-B,B-C,C-D', '[^,]+', 1, level) as legs
from dual
connect by regexp_substr(',A-B,B-C,C-D', '[^,]+', 1, level) is not null;

       RNK LEGS       
---------- ------------
         1 A-B         
         2 B-C         
         3 C-D

Upvotes: 2

Related Questions