SQL Language (MOSC)

MOSC Banner

Treat Null value in Max function

edited Dec 19, 2011 3:32AM in SQL Language (MOSC) 4 comments
Hi all,
I  need to find out the max. value of a column. But if there is a null in this column, I need to return null.

For example, the table Table_1 has column Col_1 with following rows:
1, 2, null.

If I issue SQL:

select max(col_1) from table_1,

Oracle will return 2.

But I want to return null.

Is it any way to do it?

what I can think is:

select decode(max(case when col_1 is null then 1 else 0), 1, max(col_1), null)
from table_1.


Is there any better way? Since I need to do the aggregate function twice.


Thanks,
Kenny.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center