Select the greatest value from each column, even if one of the values is null

Question

I have a road_age_test table:

create table road_age_test (
    surface_year int,
    base_year int
);

insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (surface_year, base_year) values (30,null);
insert into road_age_test (surface_year, base_year) values (null,40);
insert into road_age_test (surface_year, base_year) values (null,null);
insert into road_age_test (surface_year, base_year) values (50,50);

commit;

select * from road_age_test;

SURFACE_YEAR  BASE_YEAR
------------ ----------
          10         20
          30       null    
        null         40
        null       null
          50         50

I would like to select the greatest value from each column, even if one of the values is null:

GREATEST_YEAR
-------------
           20
           30
           40
         null
           50

What's the simplest way to do this?

---

Note: Where both values are null, I want to return null, not 0.

Answer 1

select
GREATEST(nvl(surface_year,0),nvl(base_year,0)) as greatest_year
from
road_age_test;

--Edit using NULLIF 
select
NULLIF(GREATEST(nvl(surface_year,0),nvl(base_year,0)),0) as greatest_year
from
road_age_test;

--Adding a segmented version for readability (some find it worse, eye of the beholder I guess)
select
NULLIF(
    GREATEST(
            nvl(surface_year,0),
            nvl(base_year,0) --Just keep adding columns here if you need to
            )
      ,0) as greatest_year
from
road_age_test;

Can't imagine it's much more efficient (and shouldn't be used in a where clause due to indexing), but it's a little cleaner than the case statement and allows you to add more values (columns) easily.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm

Answer 2

Re: MguerraTorres answer, I don't have the rep to comment:

If you are using 11g or higher, you could define a virtual column, which can be indexed and used in queries (among other things).

create table road_age_test (
    surface_year int,
    base_year int,
    greatest_year number generated always as
 (nullif(greatest(nvl(surface_year,0), nvl(base_year,0)),0) ) virtual
);

insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (surface_year, base_year) values (30,null);
insert into road_age_test (surface_year, base_year) values (null,40);
insert into road_age_test (surface_year, base_year) values (null,null);
insert into road_age_test (surface_year, base_year) values (50,50);

commit;

select greatest_year from road_age_test;


GREATEST_YEAR
-------------
   20
   30
   40

   50

5 rows selected.

create index greatest_year_idx on road_age_test(greatest_year);

Index created.

select greatest_year from road_age_test
where GREATEST_YEAR > 30

GREATEST_YEAR
-------------
       40
       50

2 rows selected.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#BABIJABG

Answer 3

I can do this with a case statement, if I convert the nulls to zeros:

select
    case 
        when nvl(surface_year,0) > nvl(base_year,0) then surface_year
        else base_year 
    end as greatest_year
from
    road_age_test

GREATEST_YEAR
-------------
           20
           30
           40
         null
           50

However, this seems like a bit of a clumsy approach. Is there a simpler way?