last_Value関数の使用(SQL SERVER 2012 RC)

時々、最終日など、最値の記録を取り戻したいと思っています.
例を見てみましょう

if OBJECT_ID('test','u') is not null
drop table test
go
create table test(
Id int,
Name nvarchar(50),
Date date,
Amount int)
go
insert into test(Id,Name,Date,Amount)
values
(1,'  ','2011-01-22',15),
(1,'  ','2011-01-28',20),
(2,'  ','2011-01-02',5),
(2,'  ','2011-01-18',8),
(2,'  ','2011-02-14',17)
go
select * from test

今、私たちは最後の日付の記録を望んで、LAST_を使うことができますVALUE関数

select Id,Name,Date,Amount,
LAST_VALUE(Id) over(order by Date RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) as LastId,
LAST_VALUE(Name) over(order by Date RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) as LastName,
LAST_VALUE(Amount) over(order by Date RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) as LastAmount
					from test
					order by Id

ID通りにグループ化したいなら?とても簡単で、

select Id,Name,Date,Amount,
LAST_VALUE(Id) over(PARTITION BY ID order by Date RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) as LastId,
LAST_VALUE(Name) over(PARTITION BY ID order by Date RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) as LastName,
LAST_VALUE(Amount) over(PARTITION BY ID order by Date RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) as LastAmount
					from test
					order by Id

FIRST_VALUE使用とLAST_VALUE同様、