designedly big value in SQL

Ok, I have to put null values last. The query should run under Oracle and MySQL.

I've already came up with

ORDER BY

    CASE WHEN some_table.ord IS NULL THEN 9999999999 ELSE some_table.ord END

I should use value > max(some_table.ord) instead of 9999开发者_如何学C999999.

I think subquery to determine this value is too ugly here.

If this was C++ I can use some macro like INT_MAX for this purpose. Can you name its cross-DBMS SQL twin?

UPDATE

the question is if can I put something .. beautiful instead of 9999999999, so that query will work both in Oracle and MySQL,

not how to put null values last

Use an extra column for the null flag:

order by 
   case when some_table.ord is null then 2 else 1 end ,
   some_table.ord

Or, if you have enough knowledge of the values that this column can take, just hard-code a number that is larger than anything in there:

order by coalesce(some_table.ord, 9999999999)

In Oracle, it's simply

ORDER BY some_table.ord NULLS LAST

Something like the following might work:

SELECT S.VAL1, S.VAL2, S.VAL3, COALESCE(S.ORD, O.MAX_ORD+1) AS ORD
  FROM SOME_TABLE S,
       (SELECT MAX(ORDER) AS MAX_ORD FROM SOME_TABLE) O
  WHERE S.whatever = whichever AND
        S.something <> something_else
  ORDER BY ORD

Not sure if MySQL allows sub-queries in the FROM list. The idea here is to avoid the use of a magic value to handle the NULL case.

Share and enjoy.