create user defined aggregate function with over (order by) clause

Dmitry 1 Reputation point
2022-05-26T07:31:20.963+00:00

how could I create my own (via clr or other options) aggregate function that will have working "over (order by ...)" clause?

it was impossible in 2010 (https://social.msdn.microsoft.com/Forums/en-US/7f9a6990-072c-4867-b7d0-b0be70e4abb9/can-i-create-a-clr-ud-aggregate-function-with-over-order-by-clause?forum=sqlnetfx), but may be today we have this possibility?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-26T09:57:28.377+00:00

    No, nothing has changed in this regard. Overall, the CLR is an area where very little has happened since SQL 2008.

    0 comments
  2. YufeiShao-msft 7,056 Reputation points
    2022-05-27T07:32:15.073+00:00

    Hi @Dmitry ,

    Yes, basically no change in this area.

    From the official doc:SELECT - OVER Clause (Transact-SQL)

    Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.