Saturday, February 23, 2008 10:46 AM Alex_Kuznetsov

Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

Description
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) ...

Advantages: 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.


Comments

# re: Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

Saturday, February 23, 2008 6:55 PM by jamiet

Great idea Alex. I voted 4 and added it to my watch list!!

-Jamie

# re: Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

Saturday, February 23, 2008 8:38 PM by AdamMachanic

# re: Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

Saturday, February 23, 2008 9:30 PM by Alex_Kuznetsov

Adam,

A duplicate indeed. Note that because all the details of Itzik's suggestion were in an attached document, the search did not bring it up for me.