Getting the first and last result Oracle

Question

Based on this query is it possible to take the first and last result of the year and annualized_return.

CREATE TABLE t (YEARS, PERCENTAGE) AS
SELECT 2014,  38.15 FROM DUAL UNION ALL
SELECT 2015, -25.51 FROM DUAL UNION ALL
SELECT 2016,  -8.47 FROM DUAL UNION ALL
SELECT 2017,  18.51 FROM DUAL UNION ALL
SELECT 2018,  -2.07 FROM DUAL UNION ALL
SELECT 2019,  16.27 FROM DUAL UNION ALL
SELECT 2020,  108.94 FROM DUAL UNION ALL

SELECT 2021,  29.67 FROM DUAL

in this select how can i take the first row (result) from YEARS 2014 and annualized_return 16.71

SELECT YEARS,
       PERCENTAGE,
       ROUND(
         (EXP(Sum(LN(1 + PERCENTAGE/100)) OVER(Order By Years)) - 1)*100,
         2
       ) AS ACCUMULATIVE,
       
       
       
       ROUND(
         POWER(
           EXP(Sum(LN(1 + PERCENTAGE/100)) OVER(Order By Years)),1/COUNT(*) OVER (ORDER BY Years)) * 100 - 100,2) AS annualized_return
FROM   tbl

Answer 1

If you just want the first and last rows based on years then you can use the ROW_NUMBER analytic function with my original query to filter for the row with the earliest and latest year:

SELECT years, percentage, annualized_return
FROM   (
  SELECT YEARS,
         PERCENTAGE,
         ROUND(
           (EXP(Sum(LN(1 + PERCENTAGE/100)) OVER(Order By Years)) - 1)*100,
           2
         ) AS ACCUMULATIVE,
         ROUND(
           POWER(
             EXP(
               Sum(LN(1 + PERCENTAGE/100)) OVER(Order By Years)
             ),
             1/COUNT(*) OVER (ORDER BY Years)
           ) * 100 - 100,
           2
         ) AS annualized_return,
         ROW_NUMBER() OVER (ORDER BY Years ASC ) AS rn_asc,
         ROW_NUMBER() OVER (ORDER BY Years DESC) AS rn_desc
  FROM   t
)
WHERE  rn_asc  = 1
OR     rn_desc = 1;

Which, for the sample data, outputs:

YEARS	PERCENTAGE	ANNUALIZED_RETURN
2021	29.67	16.71
2014	38.15	38.15

db<>fiddle here