Home Dashboard Directory Help
Search

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


Status: 

Active


7
0
Sign in
to vote
Type: Suggestion
ID: 329781
Opened: 2/23/2008 8:43:02 AM
Access Restriction: Public
Primary Feedback Item: 254390
0
Workaround(s)
view

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
Details
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

Sign in to post a workaround.