tags:

views:

41

answers:

3
+2  Q: 

Treat Null as Max

Hey,

So if I had a table in my database with the values

1
2
3
4
NULL

And I executed the query

SELECT MAX(col1) FROM <table>

I'd get 4. Is there any way to change this so Null would be treated as the maximum as oppose to the minimum?

Thanks!

+1  A: 
      SELECT MAX(ISNULL(col1, YouBiggestNumber)) FROM <table>
Michael Pakhantsov
+3  A: 
SELECT MAX(ISNULL(col1, 2147483647)) FROM <table> 

[2147483647 = 2^31 - 1]

Mitch Wheat
I can get that to work in my code, but that will actuall be returning 2147483647 if there is a null present, any way to get null?
mjmcloug
Just add a `if` statement around the `MAX(..` statement, turning 2147483647 back into null
Tobiasopdenbrouw
Of course! I'm blaming that second question on it being friday! thanks
mjmcloug
+1  A: 

Just as a variant, you can do this on Oracle.

SELECT *
  FROM ( SELECT col1 
           FROM <table>
          ORDER BY col1 DESC NULLS FIRST
       )
 WHERE rownum = 1

(OP hasn't specified any particular flavour of database)

Mark Baker
Doh, as the others guessed it was MSSql but I'm sure this will help futures
mjmcloug