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

nulloracleoracle-12cselect

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.

Best Answer

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

Related Solutions

Postgresql – Getting SELECT to return a constant value even if zero rows match

SELECT col1, 
       col2, 
       col3, 
       1 AS query_id 
FROM players 
WHERE username='foobar'
union all 
select null,
       null,
       null,
       1
where not exists (select 1 from players where username = 'foobar');

Or as an alternative (might be faster as no second subselect is required):

with qid (query_id) as (
   values (1)
) 
select p.*, 
       qid.query_id
from qid 
  left join players as p on (p.useranme = 'foobar');

You can re-write the above to a more "compact" representation:

select p.*, 
       qid.query_id
from (values (1)) as qid (query_id)
  left join players as p on (p.useranme = 'foobar');

But I think the explicit CTE (with...) is more readable (although that is always in the eyes of the beholder).

Postgresql – Select query to retrieve rows with null values

This example retrieves only "name" and the "SUM(credit)", when the "credit" has values.

The query you presented will retrieve a row for every present name, even if all associated credit columns are NULL. You get a row with a NULL value for SUM(credit) then. Null values are just ignored by the aggregate function sum():

You only get no row for a particular name if no row for that name exists in the table expenses for the given WHERE expressions.

I am assuming you want
.. only names matching 'vendor0%'
.. but all of those, even if they have no expenses in 2013.

Your query could work like this:

SELECT name, SUM(CASE WHEN date LIKE '2013%' THEN credit END) AS credit
FROM   expenses
WHERE  name LIKE 'vendor0%'
GROUP  BY name
ORDER  BY name

CASE defaults to NULL if no ELSE branch is given.
Aside: You shouldn't store date / time values as text. Use an appropriate type, it has many advantages.
And don't use "name" or "date" as identifiers. "name" is not a descriptive name and "date" is a reserved word in standard SQL and a function and base type name in Postgres.

Best Answer

Related Solutions

Postgresql – Getting SELECT to return a constant value even if zero rows match

Postgresql – Select query to retrieve rows with null values

Related Question