CODEWITHSUNDEEP
sqlsql-server
Sebastián
Sebastián

Reputation: 482

SQL row_number() with conditions

I have the following information in SQL Server table:

enter image description here

How can I add C1-C2-C3-C4 columns? To do this each colum has different conditions. I'm using row_number() order by id_pv desc, but it doesn't work.

Upvotes: 5

Views: 59984

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you can do this with nested case statements -- both in the partition by clause and outside the row_number(). For the first column:

select t.*,
       (case when expiry_date > @somdate and
                  row_number() over (partition by cod_suc, cod_ramo,
                                                  (case when expiry_date > @somdate then 1 else 0 end)
                                     order by id_pv desc) as col1
              then 1 else 0
         end)
from table t;

Upvotes: 13

APH
APH

Reputation: 4154

Assuming from your example you want it to place 0 when your conditions are not met, and the row number otherwise, try:

Select [your columns]
, case when ExpiryDate >= @someDate then row_number() 
    over (order by [list of columns]) 
    else 0 end as c1
, case when ExpiryDate >= @someDate and Cod_grupo = 4 then row_number() 
    over (order by [other list of columns]) 
    else 0 end as c2

Upvotes: 1

Related Questions