Aggrigate Functions - Framing Units - Shorthand Vs Verbose

Lakmal Fonseka 21 Reputation points
2021-02-03T18:50:35.277+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2021-02-03T22:30:47.44+00:00

    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.)

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-02-04T06:07:58.683+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments