CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Hari
Hari

Reputation: 267

SQL query to find greatest in columns and rows

How does one in oracle find the greatest of three columns in say 10 rows?

The requirement is I have three dates column and I have need to find greatest of three columns in 10 rows. I know greatest will find in one row.

How?

Upvotes: 3

Views: 1738

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Here are two ways to circumvent GREATEST's NULL problem (i.e. the problem that GREATEST not only returns NULL when all values are NULL, but already when at least one value is null, which makes working with GREATEST often a nuisance).

For n columns you can use n COALESCE expressions. Each expression must contain all columns, each beginning with a different column. 

select 
  max(
    greatest(
      coalesce(col1,col2,col3,col4),      
      coalesce(col2,col3,col4,col1),
      coalesce(col3,col4,col1,col2),      
      coalesce(col4,col1,col2,col3)
    )
  )
from mytable;

An alternative is not to use GREATEST at all, but compare with CASE and COALESCE. Each value gets compared with all other values. col1 >= coalesce(col2,col1) ensures that col1 is regarded greater or equal the expresson, when col2 is NULL, as long as col1 itself is not NULL. In CASE all columns are NULL the CASE expression defaults to NULL. (One could add else NULL to make this visible to the unexperienced reader.)

select 
  max(
    case 
      when col1 >= coalesce(col2,col1) and col1 >= coalesce(col3,col1) and col1 >= coalesce(col4,col1) then col1
      when col2 >= coalesce(col1,col2) and col1 >= coalesce(col3,col2) and col1 >= coalesce(col4,col2) then col2
      when col3 >= coalesce(col1,col3) and col1 >= coalesce(col2,col3) and col1 >= coalesce(col4,col3) then col3
      when col4 >= coalesce(col1,col4) and col1 >= coalesce(col2,col4) and col1 >= coalesce(col3,col4) then col4
    end
  )
from mytable;

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

With data as(
  Select col1 dt from table union all
  Select col2 from table union all
  Select col3 from table union all
  Select col4 from table
)
Select max(dt) max_dt
  from data
/

Assuming the 4 columns are DATE data type. It only uses MAX, and not GREATEST.

Update : Expanding a good point mentioned by @Thorsten in below comment

The issue with GREATEST() function is that, whenever you need to handle the NULL values being passed to it, you must use NVL to get proper output. There have had been numerous questions on the subject, like "How to avoid NULL values with GREATEST function" etc.

Note : From performance point of view, please test it before applying such logic in your production environment.

Upvotes: 1

pjd
pjd

Reputation: 1173

How about

select max(greatest(date1, date2, date3, date4)) from my_table;

Upvotes: 3

FuzzyTree
FuzzyTree

Reputation: 32392

you can use greatest again in your order by

select * from (
  select greatest(c1,c2,c3,c4) from mytable
  order by greatest(c1,c2,c3,c4) desc
) t1 where rownum = 1

Upvotes: 3

Related Questions