What is the practical use of FIRST_VALUE and LAST_VALUE of SQL Server 2012 (Code name: Denali) CTP 3

By : mcUser
Source: Stackoverflow.com
Question!

With Denali's CTP 3 release , we have more analytical functions out of which I am interested in two:

a) First_Value
b) Last_Value

I understood that FIRST_VALUE returns first value based on partition and order by clause while Last_Value returns last value based on partition and order by clause.

But in what practical situation they will be useful? A sample real time situation will help me understand this.

By : mcUser


Answers
These functions can help you get other information from the resultset without using complicated self-joins, derived tables, etc. For example, let's say you have twenty forum messages in a table, and you want to know who started the thread, and who posted the last response. They are ordered by date/time, so while MIN() & MAX() can help you identify when the first & last posts occurred, they can't tell you who those authors were unless you went out and got that additional information somehow. Even that can be complicated - if you don't have a natural or artificial key column for example (you could join on an identity column), you might be tempted to join on the date/time values, which are not guaranteed to be unique...



This video can help you solving your question :)
By: admin