OVER clause enhancement request - FIRST_VALUE, LAST_VALUE functions - by Itzik Ben-Gan

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 254395 Comments
Status Closed Workarounds
Type Suggestion Repros 4
Opened 1/28/2007 11:57:48 AM
Access Restriction Public


SQL Server 2005 introduced only partial support for the OVER clause. It is our strong believe that a more complete implementation of the OVER clause should be prioritized highly in consideration for future enhancements in SQL Server. A more complete implementation of the OVER clause can help in solving many common business problems with simpler, more intuitive, and faster solutions than available today and also substantially reduce the need for cursors. 
The following paper details the feature enhancement requests:
The current feedback item addresses the OVER clause enhancement request - FIRST_VALUE, LAST_VALUE functions, discussed in the paper in section 4 item vii.
Sign in to post a comment.
Posted by Microsoft on 7/13/2011 at 4:40 PM
Hello Everyone,
Thanks for your feedback. We have now added support for window functions that is compatible with ANSI SQL:2008 standard.
Below are the links to the documentation for latest CTP of SQL Server Denali:


Umachandar, SQL Programmability Team
Posted by Peter11111 on 7/13/2011 at 2:03 AM
In Denali CTP3 see
FIRST_VALUE : http://msdn.microsoft.com/en-us/library/hh213018%28v=SQL.110%29.aspx
LAST_VALUE : http://msdn.microsoft.com/en-us/library/hh231517%28v=SQL.110%29.aspx
Posted by SQLpro on 11/6/2009 at 1:47 AM
FIRST_VALUE, LAST_VALUE are now part of ISO standard definition of SQL since SQL:2008 with also lead, lag, ntile, and nth value

So why do not extend this demand with NTH_VALUE ?

A +
Posted by Microsoft on 9/11/2007 at 4:50 PM
Note: This feedback is for a series of requests for OVER clause enhancement. These items will be looked at both individually and together in future feature design.

This feature unfortunately did not fit into our schedule for SQL Server 2008. Based on customer input, we had prioritized ORDER BY for aggregates and the ROW/RANGE feature, but in the end decided that in order for this feature to be truly useful, we needed to implement more functionality than time would allow. This is a top priority for the next release, and we continue to look forward to great suggestions from you