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.
source to share
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.
source to share
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;
source to share