• Jeff Moden (9/2/2013)


    I guess "possible" depends a lot on whether or not you consider the "Quirky Update" to be a form of ordered aggregate or not

    If by "quirky update" you mean that you use the UPDATE statement and you get a desired solution when there are certain indexes in place, stars are aligned, I don't consider that to be an ordered aggregate or an acceptable solution at all for that matter.

    As reasonable as it sounds, though, I would't assume that any given class has been updated to the latest version epecially when the OP posted in a 2008 forum.

    The instructor that does not accept an ANSI-compatible solution that runs on the latest version is grossly incompetent.

    That said, a good instructor should encourage his students to try different solutions, since not all platforms support ordered aggregates. There is certainly all reason to teach the solution with the correlated subquery, despite its dreadful performance. After all, the purpose of teaching SQL is not only to get the students to learn specific patterns, but also to learn the building blocks, and to that end the correlated subquery for running sums is an excellent exercise.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]