Matt Miller (3/27/2008)
Christopher Ford (3/27/2008)
According to the link:SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
That works fine...
Why can't I remember what it was about that, that would be really nice to have...Several people have talked about it, especially when it comes to ordered updates...
What did you mean Matt! you're not making sense today! =)
Hehe...Put the caffeine down Chris...:) Wow!
We're agreeing. PARTITION BY works fine...Mike C meant he wanted the ORDER BY to work in the OVER clause as well (for running totals) as part of his "wish list" of new features to have. He typoed it and said PARTITION BY (so I pointed it out to him, and he corrected himself), and thus this convoluted thread.
As of now the SUM() OVER(PARTITION BY) is a way to get grouped totals without a GROUP BY clause. The SQL (92?) standard mentioned using this OVER notation to implement "running" aggregates as well, but that hasn't materialized yet (though there are active rumors that this might make it into 2008).
Just to clarify for Chris - when you use the OVER clause with the ranking functions (RANK, ROW_NUMBER, etc.) you can use both the ORDER BY and PARTITION clauses. When you use OVER with an aggregate function (SUM, AVG, etc.) you can only use the PARTITION BY clause. If SQL Server implements the PARTITION BY and ORDER BY clauses for the OVER clause in aggregate functions, you can do some pretty handy running sum type calculations without cursors or a bunch of inner joins. I'd like to see the ORDER BY clause option added to the aggregate function OVER clause. This functionality is already available on other DBMS platforms like Oracle and DB2.
I believe the OVER clause for aggregate functions was introduced either in SQL:1999 or SQL:2003, but not sure which.
BTW - it doesn't appear to be in CTP 6 of 2008. Maybe it will be implemented around SP 1 time? 🙂