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@skype.net>