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

      




)