SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with [data] (id, dept, age, [name]) as (
  select * from (
   values
     (1,  'A',  60, 'A A' ),
     (2,  'A',  70, 'B B' ),
     (3,  'A',  80, 'C C' ),
     (4,  'A',  90, 'D D' ),
     (5,  'A',  100, 'E E' ),
     (6,  'B',  10, 'F F' ),
     (7,  'B',  20, 'G G' ),
     (8,  'B',  30, 'H H' ),
     (9,  'B',  40, 'I I' ),
     (10, 'B',  50, 'J J' )
  ) as x (id, dept, age, [name])
)
SELECT DEPT,age, NAME, FIRST_VALUE(AGE) OVER (PARTITION BY dept ORDER BY NAME DESC) AS CASE_ONE
  , FIRST_VALUE(AGE) OVER (PARTITION BY dept ORDER BY NAME DESC RANGE BETWEEN CURRENT ROW  AND UNBOUNDED FOLLOWING) AS CASE_TWO
FROM [data]
order by age desc
/*
I really appreciate the time you have taken on this - so I'm not arguing with you, just trying to understand :)
As I said, I'm not that familiar with rows/range but I understand the usage for say a moving average or something where you only want a moving windows of rows...
But in the case of "FIRST_VALUE" the first value is *always* either the current row OR in the preceding values AFAIK.
I don't see how you can have a first value which comes later in the resultset? That defeats the definition surely?
*/

DEPT	age	NAME	CASE_ONE	CASE_TWO
A	100	E E	100	100
A	90	D D	100	90
A	80	C C	100	80
A	70	B B	100	70
A	60	A A	100	60
B	50	J J	50	50
B	40	I I	50	40
B	30	H H	50	30
B	20	G G	50	20
B	10	F F	50	10

DEPT	age	NAME	CASE_ONE	CASE_TWO
A	100	E E	100	100
A	90	D D	100	90
A	80	C C	100	80
A	70	B B	100	70
A	60	A A	100	60
B	50	J J	50	50
B	40	I I	50	40
B	30	H H	50	30
B	20	G G	50	20
B	10	F F	50	10

DEPT	age	NAME	CASE_ONE	CASE_TWO
A	100	E E	100	100
A	90	D D	100	90
A	80	C C	100	80
A	70	B B	100	70
A	60	A A	100	60
B	50	J J	50	50
B	40	I I	50	40
B	30	H H	50	30
B	20	G G	50	20
B	10	F F	50	10

DEPT	age	NAME	CASE_ONE	CASE_TWO
A	100	E E	100	100
A	90	D D	100	90
A	80	C C	100	80
A	70	B B	100	70
A	60	A A	100	60
B	50	J J	50	50
B	40	I I	50	40
B	30	H H	50	30
B	20	G G	50	20
B	10	F F	50	10