Redshift select while keeping row order-CodePudding

I'm using ROW_NUMBER to assign a unique row id to Redshift table rows like:

with 
    testset (rowid) as (
    select '1' union
    select '2' union
    select '3' union
    select '4' union
    select '5' union
    select '6' union
    select '7' union
    select '8' union
    select '9' union
    select '10'
),
testset_sorted as (
select
   row_number() over (),
   rowid
   from testset
   order by row_number
)
select * from testset_sorted order by row_number

but the resulting table has assigned row_number cols not in the same order of col rowid:

row_number,rowid
1,7
2,6
3,3
4,10
5,5
6,8
7,9
8,2
9,4
10,1

so the ordering is not respected:

select
   row_number() over (),
   rowid
   from testset
)

I get

row_number,rowid
1,4
2,2
3,5
4,8
5,9
6,10
7,7
8,1
9,6
10,3

While I would expect to have

row_number,rowid
1,1
2,2
3,3
4,4
5,5
...

CodePudding user response：

The row_number() window function just needs to know what order you want things in with an "order by" clause.

with 
    testset (rowid) as (
    select '1' union
    select '2' union
    select '3' union
    select '4' union
    select '5' union
    select '6' union
    select '7' union
    select '8' union
    select '9' union
    select '10'
),
testset_sorted as (
select
   row_number() over (order by rowid),
   rowid
   from testset
   order by row_number
)
select * from testset_sorted order by row_number;

For more info see - https://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html