ta.bu.shi.da.yu (9/17/2010)
2. Oracle has implemented more of the SQL standard that Microsoft. Case in point: try doing a lag, lead, or top analytic function in SQL Server... you can't! You can only use rank, denserank, and row_number. If you were able to use lag(column) over () then I'm almost certain we wouldn't need to be using non-supported clustered index approaches to running total aggregates... though we wouldn't have had one of the coolest articles around on this approach, of course 🙂
Heh... thanks for that. As a side bar, there is a super fast way to guarantee either that the running total worked or get a notification that it didn't that Paul White and Tom Thompson came up with. That's a good thing because just before that, Wayne S. was the first and only person I've ever met that broke a previously good running example. I'm thinking I need to update the article (hopefully) one more time and then maybe retire. 😛
Shifting gears, I wouldn't necessarily say that Oracle is ahead of the game for SQL standards. The last time I looked, Oracle had nothing with DATEDIFF, DATEADD, etc, etc and, IIRC, those are part of the SQL standards.
--Jeff Moden
Change is inevitable... Change for the better is not.