FIRST_VALUE and LAST_VALUE in SQL SERVER
FIRST_VALUE and LAST_VALUE
See the following SQL statements:
WITH testas (select 'Happy col' as name, 10 as score union all select 'Happy col' blog ', 15 union all select 'Happy col ', 20 union all select 'Microsoft Authentication', 30 union all select 'Microsoft Authentication', 40 union all select 'Microsoft Authentication', 40) select name, score, FIRST_VALUE (score) over (order by name) as fst, LAST_VALUE (score) over (order by name) as Lstfrom test
Result:
Name score fst Lst
Le Ke's blog 15 15 10
Le Ke's blog 20 15 10
Le Ke's blog 10 15 10
Microsoft certification 40 15 30
Microsoft certification 40 15 30
Microsoft certification 30 15 30
FIRST_VALUE (score) over (order by name) as fst, take the first row of score in ascending order by name, see the red font.
LAST_VALUE (score) over (order by name) as Lst, take the last line of score of the same name in ascending order by name, see the blue and purple fonts.