23 February 2008 10:46 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

23 February 2008 18:55 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

23 February 2008 20:38 by AdamMachanic

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

23 February 2008 21:30 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.