CODEWITHSUNDEEP
sqlgoogle-cloud-spanner
sunny babau
sunny babau

Reputation: 195

Spanner row_number() OVER ( PARTITION BY.. ORDER BY)

How do we write the following in Google Spanner, for some analytical function. If this is not possible any other alternate way to write the below SQL

row_number() OVER ( PARTITION BY c1,c2 ORDER BY c3 DESC )

Upvotes: 1

Views: 3314

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Assuming that c3 is unique, you can use a subquery:

select t.*,
       (select count(*)
        from t t2
        where t2.c1 = t.c1 and t2.c2 = t.c1 and t2.c3 >= t.c3
       ) 
from t;

This is usually much less efficient than window functions, but is a possibility in databases that do not support them.

Upvotes: 2

Related Questions