Say I, in MySQL 4.1, have a table "test" with a single column - "test".
There are four rows in the table - 3, 2, 1, and null.

Running "SELECT * FROM test ORDER BY test DESC" yields the following:

3
2
1
null

Whereas doing it by ASC yields this:

null
1
2
3

Since null's aren't supposed to be comparable, it seems that ORDER BYs
treatment of them is sorta arbitrary. If so, maybe that "treatment"
can be changed? eg. maybe I could make DESC yield this:

3
2
1
null

and ASC yield this?:

1
2
3
null

Is using UNIONs or JOINs the only way to achieve this effect?

yawnmoth wrote:
SELECT * ,
IF(ISNULL(test), 1, 0)
AS isnull
FROM test
ORDER BY isnull ASC

The original phrase fromt he ANSI/ISO Standards is "implementation
defined" and early products did it differently, mostly based on their
internal model for NULLs.

Use a COALESCE() or a product with the newer sorting options on the
ORDER BY clause.

--CELKO-- wrote:
looks as though MSSQL, PostgreSQL, and MySQL support it, whereas IF and
ISNULL, together, only seem to be supported by MySQL. Is it as
ubiquitous as the COUNT aggregate function is? If it isn't, that's
okay - I'm just curious, heh

yawnmoth wrote:
COALESCE is standard SQL.

Not sure what you mean by "IF" and "ISNULL". "IS NULL" (space separated) is
standard SQL. There is a "NULLIF (V1, V2) function. I am not finding
references to "IF" by itself except as a procedural SQL construct (i.e., for
stored procedures).

- Lew

"yawnmoth" <terra1024 (AT) yahoo (DOT) com> wrote

Ordering by values that do not exist is kinda arbitrary and capricious,
inherently, dontcha think?

I think there *are* some DBMSes that deliver the order you prefer.

ANSI/ISO Standard SQL and it is more powerful than the proprietary
ISNULL()

COALESCE correctly promotes its arguments to the highest data type in
the expression:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)