jbossteiid

Assigning row number without any order

How can I use row_number() function without any order

Example Table:

COL A           COL B
42123345990000  0
42123345990000  0.33333334
42123345990000  0.6666667
42123345990000  1
42123345990000  0.86340976
42123345980000  0
42123345980000  0.1
42123345980000  0.2
42123345980000  0.3432426
42123345980000  0.5
42123345980000  0.53144264

Desired Output:

ROW     COL A           COL B
1   42123345990000  0
2   42123345990000  0.33333334
3   42123345990000  0.6666667
4   42123345990000  1
5   42123345990000  0.86340976
1   42123345980000  0
2   42123345980000  0.1
3   42123345980000  0.2
4   42123345980000  0.3432426
5   42123345980000  0.5
6   42123345980000  0.53144264

I would like partition to be existing on COL A but no ordering.

Solution

  • The general answer to the question of a row_number without an ordering is to order over a constant - row_number() over (order by 1)

    In your case the expected output shows that the row number is actually a rank based upon the col b values, so what you actually want is - dense_rank() over (order by COLB)