Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by David Poole A genuine use for a SQL CLR Aggregate RE: A genuine use for a SQL CLR Aggregate

  • December 29, 2010 at 3:59 pm

    #1267455

    Bradley Deem (12/29/2010)

    I think part of this limitation is due to the lack of ability to perform an ORDER BY clause with an aggregate.

    Assuming I've implemented it correctly, this could produce output such as

    firstRowValue,secondRowValue,thirdRowValue

    or maybe parallelism kicks in (on a large set) and produces output where the order changes ie

    secondRowValue,firstRowValue,thirdRowValue

    Now, if we had we had the ability to perform the ORDER BY clause with the OVER clause ie

    SELECT dbo.List(myStringColumn,',') OVER(ORDER BY myStringColumn)

    FROM myTable

    Then we could guarantee the correct (expected) order of output.

    Hello Bradley. I am not sure that I agree that this cannot be done in the Aggregate (hence requiring the ORDER BY). Maybe I am thinking of something that is not accurate, but I think this can be handled in the "Terminate()" method. The parallelism issue that you mention is handled via the "Merge()" method but the output still needs to go through the "Terminate()" method so it is there that you could properly order the string. Would that work?

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR