FIRST_VALUE and LAST_VALUE in SQLSERVER

FIRST_VALUE and LAST_VALUE in SQL SERVER

FIRST_VALUE and LAST_VALUE

See the following 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 '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 Lst from 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.