CODEWITHSUNDEEP
sql
Gamliel Beyderman
Gamliel Beyderman

Reputation: 23

alternative to sql rank () over (partition by... order by..)

I am trying assign a rank like the one in the example below (rowFinal), where the rows are partitioned by GalleryId and ordered by StartDateKey ascending. However, I need to have a 'New' EventName restart the count back from 1 like in the example below with StartdateKey 20131219. 

GalleryId   StartDateKey    row EventName   rowFinal    
425934233   20130226         1    Renew        1    
425934233   20130326         2    Renew        2    
425934233   20130426         3    Renew        3    
425934233   20130526         4    Renew        4    
425934233   20130626         5    Renew        5     
425934233   20131219         6    New          1    
425934233   20140119         7    Renew        2    
425934233   20140219         8    Renew        3    
425934233   20140319         9    Renew        4    
425934233   20140419         10   Renew        5    
…                   
425931351   20121210         1    Renew        1    
425931351   20130110         2    Renew        2    
425931351   20130210         3    Renew        3    
425931351   20130310         4    Renew        4    
425931351   20130410         5    Renew        5    
425931351   20130510         6    Renew        6    
425931351   20130610         7    Renew        7    
425931351   20130710         8    Renew        8    
425931351   20130810         9    Renew        9

Parsing and updating each row (while loops) proved not practical due to speed. Any suggestions would be profoundly appreciated.

Upvotes: 1

Views: 6508

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can do this with a trick. The idea is to group rows based on the number of "New" records before it. Then you can do this using a cumulative sum:

select t.*,
       row_number() over (partition by GalleryId, numRenew order by startdatekey) as rowFinal
from (select t.*,
             sum(case when EventName = 'Renew' then 1 else 0 end) over 
                 (partition by partition by GalleryId order by startdatekey) as numRenew
      from table t
     ) t;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select *, 
row_number() over(partition by galleryid,substring(startdatekey,7,2) order by startdatekey)
as rowFinal
from tablename;

It looks like you are partitioning on galleryid and also on the last 2 characters of startdatekey.

Upvotes: 0

Adam Miller
Adam Miller

Reputation: 777

Try separating the different sections into different queries and unioning them together.

Upvotes: 0

Related Questions