Hello, I’ve a problem with the ordering generated by row_number, I have two STRING type columns (str_dt,str_ts) I need to order the column generated by:
COALESCE(REPLACE(str_ts,", N
ULL), str_dt) AS bigint
But the order of row_number doesn't seem to make sense with id= 1, especially at row_number=8
id str_dt str_ts Int_ts row_number
1 20240202 20240202105612 20240202105612 1
1 20240202 20240202115613 20240202115613 2
1 20240202 20240202125612 20240202125612 3
1 20240202 20240202135611 20240202135611 4
1 20240202 20240202170000 20240202170000 5
1 20240202 20240202170001 20240202170001 6
1 20240202 20240202170003 20240202170003 7
1 20240202 20240129165611 20240129165611 8
2 20231211 20231211 2
2 20231212 20231212 1
This is the query:
ROW_NUMBER()
OVER(PARTITION BY id ORDER BY cast(COALESCE(REPLACE(str_ts,", N
ULL), str_dt) AS bigint) DESC) AS row_n
This is the expected order:
20240202170003
20240202170001
20240202170000
20240202135611
20240202125612
20240202115613
20240202105612
20240129165611
I’ve noticed that with REPLACE(str_ts,", N
ULL)AS bigint, without Coalesce works correctly
Is there a problem with COALESCE?