First_value and last_value in SQL Server
First_value and Last_value
Look at the following group of SQL statements:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
With test as (select ' Le Cola can's blog ' as name,10 as score union ALL select ' Le Cola ' blog ', union ALL SELECT ' Le cola can blog ', union ALL SELECT ' Microsoft Certified ', union ALL SELECT ' Microsoft Certified ', union ALL SELECT ' Microsoft Certified ', ' SE Lect Name,score, First_value (score) over (order by name) as FST, Last_value (score) over (the order by name) as Lst from test |
Results:
Name Score FST Lst
Le Cola Blog 15 15 10
Le Cola Blog 20 15 10
Le Cola Blog 10 15 10
Microsoft Certification 40 15 30
Microsoft Certification 40 15 30
Microsoft Certification 30 15 30
First_value (score) over (ordered by name) as FST, take the first row of score in ascending name, see the Red font.
Last_value (Score) over (type by name) as Lst, with the last row of the same name in ascending order name score, see Blue and purple fonts.