23 February 2008 10:46
Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax
To address a very common requirement, Itzik Ben-Gan is suggesting the following syntax:
TOP(@n) OVER(PARTITION BY ... ORDER BY ...)
For instance, the requirement "select three latest orders for every customer" would be implemented as
SELECT TOP(3) OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) ...
all intentions are expressed in one place, in an intuitively clear
way, similar to existing OLAP functions syntax. The alternative is to
use ROW_NUMBER(), and the implementation of the requirement is
scattered all over the query, nd requires an inline view.