example.sql

with data(id, dept, age, name) as
(
   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' )
)
SELECT DEPT, FIRST(AGE) OVER (PARTITION BY dept ORDER BY NAME DESC) AS CASE_ONE,
             FIRST(AGE) OVER (PARTITION BY dept ORDER BY NAME DESC RANGE UNBOUNDED PROCEDING UNBOUNDED FOLLOWING) AS CASE_TWO
FROM data
-- the following line can be added or not (without it will be clearer what is going on)
GROUP BY DEPT


      




)

Code sample for this so answer https://stackoverflow.com/a/63223784/215752