TOP(@n) OVER(PARTITION BY ... ORDER BY ...) - by AKuz

Status : 

 


7
0
Sign in
to vote
ID 329781 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 2/23/2008 8:43:02 AM
Access Restriction Public
Primary Feedback Item 254390

Description

To address a very common requirement, I am 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 my 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. 

Cheers,
Alex Kuznetsov,
SQL Server MVP
Sign in to post a comment.
Posted by Microsoft on 2/26/2008 at 12:59 PM
Dear Alex

Thanks for your feature request. Since this request has been filed already earlier in http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390, I am closing this item as duplicate and request all further feedback be provided through feedback item 254390.

If you disagree with this resolution, feel free to reopen this item.

Thanks and best regards
Michael
Posted by Adam Machanic on 2/23/2008 at 12:37 PM
This is a duplicate of:

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