SQL query to find the largest number in columns and rows

Could you please tell me a query in oracle to find the largest of 3 columns in 10 rows.

The requirement is that I have three columns of dates and I need to find the largest of the three columns in 10 rows. I know the largest number will be found in one line.

Any request will help with this, I am using oracle 11g.

+3


source to share


4 answers


What about



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

      

+3


source


you can use greatest

again inorder by



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

      

+2


source


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 4 columns are datatype DATE

. He only uses MAX

, not GREATEST

.

Update: Extending the good point mentioned by @Thorsten below the comment

The problem with a function GREATEST()

is that whenever you need to process the values ​​passed to it NULL

, you must use NVL

to get the correct output. There were numerous questions on this subject such as "How to avoid NULL values ​​with the GREATEST function" etc.

Note. From a performance standpoint, please test it before applying such logic in a production environment.

+1


source


Here are two ways to get around the BIG NULL problem (that is, the problem with which 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 frustrating).

For n columns, you can use n COALESCE expressions. Each expression must contain all columns, each starting 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;

      

The alternative is not to use GREATEST at all, but to compare with CASE and COALESCE. Each value is compared with all other values. col1 >= coalesce(col2,col1)

guarantees that col1 is considered greater than or equal to exphone when col2 is NULL, unless col1 itself is NULL. In CASE, all columns are NULL, and CASE is NULL by default. (May be added else NULL

to make this visible to the inexperienced 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;

      

0


source







All Articles