CODEWITHSUNDEEP
sqloracle-databaseplsql
Adam
Adam

Reputation: 777

Query to Find max of three columns in a row

I have three dates in table, for each record i need to find out max of three columns and need to ignore incase if the column is null, could you please help on this ?

I am using oracle 10g version.

Table-1
---------
SL NO date1 date2 date3 age

Upvotes: 4

Views: 229

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726779

Since GREATEST will return NULL if any of the values that you pass it is NULL, you need a combination of GREATEST and COALESCE:

SELECT GREATEST(
    COALESCE(date1, date2, date3)
,   COALESCE(date2, date1, date3)
,   COALESCE(date3, date1, date2)
)
FROM my_test_table

Upvotes: 4

Andrew
Andrew

Reputation: 8758

If I'm understanding you correctly (your post is not very clear), you can use the GREATEST function:

sslect
...,
GREATEST (date1,date2,date3)
from...

Upvotes: 0

Mureinik
Mureinik

Reputation: 311723

You can use the GREATEST function, which returns the largest of the given n arguments:

SELECT GREATEST(date1, date2, date3)
FROM   table1

Upvotes: 1

Related Questions