When to use "UNBOUNDED PRECEDING AND CURRENT ROW" together vs. just "UNBOUNDED PRECEDING"

Question

I'm running an old co-workers query and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is used in the query. I'm curious as to when to just use UNBOUNDED PRECEDING by itself or like is written as above

I tried both methods and I get the same result but I only tried it on a subset of the data. I'm worried I could be missing something if I update the code, so i wanted to get a clear understanding of the concepts

E.g

SELECT *
  , sum(pct) OVER(
    PARTITION BY id, id2, zip 
    ORDER BY amount desc 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
  ) AS cumulative_pct
FROM #a

vs.

SELECT *
  , sum(pct) OVER(
    PARTITION BY id, id2, zip 
    ORDER BY amount desc ROWS  UNBOUNDED PRECEDING 
  ) AS cumulative_pct
FROM #a

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-04-09T19:31:10

They do the same thing. The default for ROWS UNBOUNDED PRECEDING is to extend the window to the current row.

I always use BETWEEN, simply because that is what I learned when I first learned window functions. Either is acceptable, and I don't see an advantage of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

When to use "UNBOUNDED PRECEDING AND CURRENT ROW" together vs. just "UNBOUNDED PRECEDING"

1 Answers