How can I RANK() OVER PARTITION BY with a WHERE Clause

Question

I need to do 2 ranks in a table, one for all rows and one where Total Cum Production is greater than zero. Is there any way to do that with a Rank Function?

SELECT 
       LocationNumber
      ,[Date]
      ,Oil+Gas+Water as [TotalFluid]
      ,sum(Oil + Gas + Water ) over (partition by [LocationNumber] order by [Date] asc) as [CumTotalFluid]
      ,rank() over (partition by [LocationNumber] order by [Date] asc) as TABLE_DAY
      ,rank() over (partition by [LocationNumber] order by [Date] asc WHERE CumTotalFluid > 0) as Prod_DAY

FROM DV

I would spell out the word "Cumulative" in my object names if I were you. ; ) — Tab Alleman, Aug 06 '19 at 17:53

score 3 · Accepted Answer · answered Aug 06 '19 at 17:33

3

You can use conditional logic:

SELECT LocationNumber, [Date], (Oil+Gas+Water) as [TotalFluid],
       sum(Oil + Gas + Water ) over (partition by [LocationNumber] order by [Date] asc) as [CumTotalFluid],
       rank() over (partition by [LocationNumber] order by [Date] asc) as TABLE_DAY
       (CASE WHEN CumTotalFluid > 0
             THEN rank() over (partition by [LocationNumber], CASE WHEN CumTotalFluid > 0 THEN 1 ELSE 0 END order by [Date] asc
                              ) 
        END) as Prod_DAY
FROM DV;

The outer case only returns values where the condition is true. The case in the partition by divides the data into two (more) sets, so the ranking is correct.

answered Aug 06 '19 at 17:33

Gordon Linoff

1,242,037
58
646
786

Thanks for the idea - I tried your suggestion but I get an error using the expression to calculate [CumTotalFluid] that Windowed functions cannot be used in the context of another windowed function or aggregate. Any suggestions on how to get around that? – Ryan Aug 06 '19 at 17:50
1

@Ryan . . . You need to use a subquery or CTE if that is a calculated field. – Gordon Linoff Aug 07 '19 at 01:01
2

I don't know about those aliases :| – KirstieBallance May 31 '22 at 22:31

How can I RANK() OVER PARTITION BY with a WHERE Clause

1 Answers1