Re: Does anybody use ORDER BY x USING y?

On P, 2005-09-18 at 18:04 -0400, Tom Lane wrote:
> Another thing that's flaky in the current treatment is
> the question of whether NULLs sort before or after ordinary values.
> We've essentially tried to force NULLs to sort "high" (as if they
> compare greater than all ordinary values), so that ASC and DESC
> orderings can be obtained from forward and backwards scans of an
> ordinary btree index. This is going to break entirely in the
> presence of reverse-sort opclasses --- given the current btree code,
> such an opclass would cause NULLs to appear to sort "low". I suspect
> we have to bring out the NULL sort behavior as an explicit property
> of opclasses, but I'm not sure just how to do that. A related point
> is that we not infrequently get requests for a way to make ORDER BY
> sort nulls low; it'd be nice if we could actually support that,
> rather than going in the direction of making sure it can't happen.

I think that placement of NULL's should be a property of ORDER BY and
separated from opclass.

From: http://opensource2.atlassian.com/projects/hibernate/browse/HHH-465
------------------------------------------------------------------------

support of nulls first / last in order clause

"NULLS LAST" is part of the SQL 99 standard.

The syntax is as follows:

ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST]

In different DBs, the sorting of nulls relative to other values is
handled differently.

PostgreSQL - Nulls are considered HIGHER than non-nulls.
DB2 - Higher
MSSQL - Lower
MySQL - Lower
Oracle - Higher

The following DBs have supported this functionality:
DB2 V7
Oracle 9i

PostgreSQL, MySQL, SQLServer do not appear to support this from what I
can gather.

see http://forum.hibernate.org/viewtopic.php?
t=942176&start=0&postdays=0&postorder=asc&highlight=

--
Hannu Krosing <hannu(at)skype(dot)net>