2

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.

5
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

  • @Wilson I've updated it to use NULLIF for that scenario. let me know if that works. – MguerraTorres Jul 20 '18 at 14:28
  • 2
    The part about 'allows you to add more values (columns) easily' is key! – Wilson Jul 20 '18 at 15:37
  • @Wilson Agreed! – MguerraTorres Jul 20 '18 at 16:04
  • 1
    MguerraTorres : Someone posted an answer and tried to alert you. – Wilson Jul 25 '18 at 17:56
4

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

  • looks great. I'm on 10g so I don't have Virtuals. Nice work. – MguerraTorres Jul 26 '18 at 23:33
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.