rank function using order by timestamp_tz(9) in snowflake is not working properly

Question

snowflake rank function order by same ROW_MODIFIED_TMST function is generating unique numbers.

for example:

Table1

Column1  ROW_MODIFIED_TMST                    
A        2022-04-03 17:42:41.009 +0000        
b        2022-04-03 17:42:41.009 +0000        
c        2022-04-03 17:42:41.009 +0000        
d        2022-04-03 17:42:41.009 +0100

select 
rank() over(partition by column1 order by ROW_MODIFIED_TMST desc) from table1

Column1  ROW_MODIFIED_TMST                    RANK
A        2022-04-03 17:42:41.009 +0000        1
b        2022-04-03 17:42:41.009 +0000        2
c        2022-04-03 17:42:41.009 +0000        3
d        2022-04-03 17:42:41.009 +0100        4

Here rank function should be 1,1,1,2 instead of 1,2,3,4

Please suggest

Answer 1

So if we take you example data, and run it:

with table1(Column1,ROW_MODIFIED_TMST) as (      
    SELECT * FROM VALUES
    ('A', '2022-04-03 17:42:41.009 +0000'::timestamp_tz),    
    ('b', '2022-04-03 17:42:41.009 +0000'::timestamp_tz),      
    ('c', '2022-04-03 17:42:41.009 +0000'::timestamp_tz),    
    ('d', '2022-04-03 17:42:41.009 +0100'::timestamp_tz)
)
select 
rank() over(partition by column1 order by ROW_MODIFIED_TMST desc) from table1

RANK() OVER(PARTITION BY COLUMN1 ORDER BY ROW_MODIFIED_TMST DESC)
1
1
1
1

It gets exactly what I would expect, and what Lukazs points out.

But you said:

Here rank function should be 1,1,1,2 instead of 1,2,3,4

But it doesn't get 1,2,3,4, and it should not get 1,1,1,2 as the four Column1's values are all different.

Now if you drop the PARTITION BY of those four distinct Column1 values

we can see how to two type of RANK work, and compare to ROW_NUMBER()

select 
    rank() over(order by ROW_MODIFIED_TMST desc) as sparse,
    dense_rank() over(order by ROW_MODIFIED_TMST desc) as dense,
    row_number() over(order by ROW_MODIFIED_TMST desc) as rn
from table1

gives:

SPARSE	DENSE	RN
1	1	1
1	1	2
1	1	3
4	2	4

Answer 2

In this example the partition by column1 is the issue. Each value is different a,b,c,d.

To avoid gaps DENSE_RANK should be used instead of RANK.

The code should rather be:

select *, dense_rank() over(order by ROW_MODIFIED_TMST desc) 
from table1