SQL query to find greatest in columns and rows

Question

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?

So you want the greatest value of all thirty values? Or you want the greatest value per column (three values in total)? — Thorsten Kettner, Sep 22 '14 at 15:12

score 3 · Answer 1 · answered Sep 22 '14 at 15:02

3

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

answered Sep 22 '14 at 15:02

FuzzyTree

32,014
3
54
85

score 3 · Accepted Answer · answered Sep 22 '14 at 15:08

3

How about

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

answered Sep 22 '14 at 15:08

pjd

1,163
8
17

I wasn't quite sure about the number of date columns you had. You mention 4 columns in your first sentence but then mention three date columns in your second sentence. – pjd Sep 22 '14 at 15:10
this is a better way to do it – FuzzyTree Sep 22 '14 at 15:17
Is there a value to ignore the null value other than using the nvl ? I want to ignore the column from greatest. – Hari Sep 22 '14 at 17:51
That is a good question. It seems that returning null for the result of the `greatest` function evaluation is by design https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:524526200346472289, and in your case it could be somewhat tricky to overcome this. To avoid using `nvl`, see the answer(s) to this stackoverflow question, which is very similar to the problem you are facing: http://stackoverflow.com/questions/19186283/handling-null-in-greatest-function-in-oracle – pjd Sep 22 '14 at 19:23

Lalit Kumar B · Answer 3 · 2014-09-22T19:06:09.783

1

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.

edited Sep 22 '14 at 19:06

answered Sep 22 '14 at 15:18

Lalit Kumar B

47,486
13
97
124

1

Thus avoiding GREATEST's NULL problem. It's slower but does what's desired. +1 – Thorsten Kettner Sep 22 '14 at 18:50
@Thorsten, two good points. Thanks for mentioning. The NULL problem was already in my mind while posting the answer. But, regarding the performance of the query, I would be very much concerned. But with the newer releases and better hardwares, my concern is really not much of a deal. But yes, the legacy systems might not be that efficient. – Lalit Kumar B Sep 22 '14 at 18:58

score 0 · Answer 4 · answered Sep 23 '14 at 04:50

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;

SQL query to find greatest in columns and rows

4 Answers4