Aggrigate Functions - Framing Units - Shorthand Vs Verbose

Question

Hi Pros,
I have a question (not sure this is a legitimate question :) ) about framing units when defining an aggregate window function.

If I want to eliminate a lower bound compared to the current row, I could use "ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW".

I noticed that as a shorthand way "ROWS UNBOUNDED PRECEDING" also works. If both are same, why two different constructs available to produce the same results?

Eg - following two SQL statements produces the same results.

    ,sum(e.qty) over(partition by e.empid order by e.empid rows unbounded preceding)
    ,sum(e.qty) over(partition by e.empid order by e.empid rows between unbounded preceding and current row)

Thanks and best regards,
Chandima

Answer 1

Because "rows between unbounded preceding and current row" is kind of long-winding to type. But at the same time it is more general, as this permits us to say things like "rows between 5 preceding and 5 following" to implement a sliding window.

But since "rows between unbounded preceding and current row" is very common, some people may appreciate a shorthand. (Myself, I always use the long version, but that is because I have not paid attention to the shorthand.)

Answer 2

Hi @Lakmal Fonseka ,

Microsoft official documents have detailed explanations and examples, please refer to：

SELECT - OVER Clause (Transact-SQL) ROWS or RANGE

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.