designedly big value in SQL

Question

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 9999999999.

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

Answer 1

In Oracle, it's simply

ORDER BY some_table.ord NULLS LAST

Answer 2

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)

Answer 3

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.