How to grab values years dynamically in Oracle/SQL depending on whether they are available (not Null) or not (Null)

Question

Let's say that I have a table that has a person with their weight by year.

 NAME     2012        2013       2014         2015        2016         2017      2018         2019
JOHN      180          185       192          205         199          198       193          null
MIKE      190          191       191          195         195          195       195          195

What I am trying to do is grab an min/max of the last NON NULL years. (as shown in the result set below)

NAME     MIN         MAX
JOHN     192         205
MIKE     195         195 

Below is what I've tried, is there a better way to do this?

SELECT 
    CASE WHEN 2019 IS NULL
         AND 2018 > 2017 > 2016 > 2015 > 2014
         THEN 2018
         WHEN 2019 IS NULL
         AND 2017 > 2016 > 2015 > 2014 > 2018
         THEN 2017
         WHEN 2019 IS null
         AND 2016 > 2017 > 2018 > 2015 > 2014
         THEN 2016
         WHEN 2019 IS NULL
         AND 2015 > 2018 > 2017 > 2016 > 2014
         THEN 2015
         WHEN 2019 IS NULL
         AND 2014 > 2018 > 2017 > 2016 > 2015
         THEN 2014
         WHEN 2019 IS NOT NULL
         AND 2019 > 2018 > 2017 > 2016 > 2015 > 2014
         THEN 2019
         WHEN 2019 IS NOT NULL
         AND 2018 > 2017 > 2016 > 2015 > 2014 > 2018
         THEN 2018
         WHEN 2019 IS NOT null
         AND 2017 > 2017 > 2018 > 2015 > 2014 > 2019
         THEN 2017
         WHEN 2019 IS NOT NULL
         AND 2016> 2015 > 2018 > 2017 > 2019 > 2014
         THEN 2016
         WHEN 2019 IS NOT NULL
         AND 2015 > 2014 > 2018 > 2017 > 2016 > 2019
         THEN 2015
         WHEN 2019 IS NOT NULL
         AND 2014 > 2015 > 2015 > 2018 > 2017 > 2016 > 2019
         THEN 2014
          END                                  as MAX

Thanks in advance FROM Table WHERE

Answer 1

Regarding your remark on Tim's answer

if 2019 is null, we only want to calculate the max from 2018-2013. if 2019 is available only 2019-2014

you want min/max over 6 years.

The easiest way is to normalize the data first using UNPIVOT, by default this removes NULLs:

SELECT NAME, weight, yr,
   Row_Number() Over (PARTITION BY NAME ORDER BY yr DESC) AS rn
FROM mytable
UNPIVOT(weight
    FOR yr
    IN (
        y2012 as 2012
       ,y2013 as 2013
       ,y2014 as 2014
       ,y2015 as 2015
       ,y2016 as 2016
       ,y2017 as 2017
       ,y2018 as 2018
       ,y2019 as 2019
    )
)

Then you apply your logic using Windowed Aggregates to return the last 6 years only. Using row_number you get the last 6 rows

WITH cte AS 
 (
    SELECT NAME, weight, yr,
       Row_Number() Over (PARTITION BY NAME ORDER BY yr DESC) AS rn
    FROM mytable
    UNPIVOT(weight
        FOR yr
        IN (
            y2012 as 2012
           ,y2013 as 2013
           ,y2014 as 2014
           ,y2015 as 2015
           ,y2016 as 2016
           ,y2017 as 2017
           ,y2018 as 2018
           ,y2019 as 2019
        )
    )
 )
SELECT NAME, Min(weight), Max(weight), Min(yr), Max(yr)
FROM cte
WHERE rn BETWEEN 1 AND 6
GROUP BY NAME

Using max you get the rows from the last 6 years (which will return a different result if there are additional NULLs):

WITH cte AS 
 (
   SELECT NAME, weight, yr, 
      Max(yr) Over (PARTITION BY NAME) AS maxyr
    FROM mytable
    UNPIVOT(weight
        FOR yr
        IN (
            y2012 as 2012
           ,y2013 as 2013
           ,y2014 as 2014
           ,y2015 as 2015
           ,y2016 as 2016
           ,y2017 as 2017
           ,y2018 as 2018
           ,y2019 as 2019
        )
    )
 )
SELECT NAME, Min(weight), Max(weight), Min(yr), Max(yr)
FROM cte
WHERE yr BETWEEN maxyr -5 AND maxyr
GROUP BY NAME

See dbfiddle

Answer 2

Greatest() and Least() functions would help in this case

Greatest() function compares the columns and provide maximum value from those columns and Least() function provide minimum value from those columns.

     select name,
             LEAST(2012,2013,2014,2015,2016,2017,2018) as MIN_WEIGHT
            , GREATEST(2012,2013,2014,2015,2016,2017,2018) as MAX_WEIGHT
     from your_table
     /

Answer 3

Your table design is not optimal, and it would be best to normalize it get each weight per year on a separate record, instead of column. That being said, if you must continue, one trick you can do is to use COALESCE to replace a NULL column weight with -1. This will effectively take it out of the picture. Then, use the scalar GREATEST function to find the legitimate maximum:

SELECT
    GREATEST(COALESCE("2019", -1),
             COALESCE("2018", -1),
             COALESCE("2017", -1),
             COALESCE("2016", -1),
             COALESCE("2015", -1),
             COALESCE("2014", -1),
             COALESCE("2013", -1),
             COALESCE("2012", -1)) AS max_weight
FROM yourTable;

Note: In the off chance that every column might be NULL, then the above call to GREATEST would return -1. In this case, you can either leave that value there as a market for no valid data, or you could wrap in a CASE expression and replace with some other value.