NULLS FIRST/LAST support?

[SOS]

I notice there is no support for the NULLS FIRST/NULLS LAST specifier
on ORDER BY clauses.

Am I overlooking something or is this really the case?
Is there any chance of support for this coming soon? Or is it too
vendor-specific to be in SQLAlchemy?
If it is feasible, has anyone created a patch/addon that would enable
this?

Cheers,

SOS

[Michael Bayer]

i was totally guessing mysql on this one...but its oracle ! who knew.

you're free to just use a string and say order_by="somecolumn NULLS
FIRST" on this. we could add an operator to the oracle module if
that helps, something like (assume 0.4 usage)
order_by=oracle.nullsfirst(mycolumn.desc()) , i guess that is
important if youre applying ordering to relations which get aliased.

[Oleg Deribas]

Hello,

Michael Bayer said the following on 09.08.2007 17:09:

> we could add an operator to the oracle module if
> that helps, something like (assume 0.4 usage)
> order_by=oracle.nullsfirst(mycolumn.desc()) , i guess that is
> important if youre applying ordering to relations which get aliased.

It is not only Oracle. Firebird also supports NULLS FIRST/LAST since 1.5

--
Oleg

[Michael Bayer]

ah that changes things...if NULLS FIRST/LAST is part of ANSI sql then
id feel comfortable adding a core construct, like
order_by=column.nullsfirst().

[Oleg Deribas]

Hello,

Michael Bayer said the following on 09.08.2007 20:50:

> ah that changes things...if NULLS FIRST/LAST is part of ANSI sql then
> id feel comfortable adding a core construct, like
> order_by=column.nullsfirst().

Here is what I've found in SQL2003 draft:

"In addition, NULLS FIRST or NULLS LAST may
be specified, to indicate whether a null value should appear before or
after all non-null values in the ordered
sequence of each <value expression>"

--
Oleg

[Michael Bayer]

OK just to double check, the syntax looks like:

SELECT * FROM sometable ORDER BY foo NULLS FIRST

SELECT * FROM sometable ORDER BY foo DESC NULLS LAST

? i.e. is "DESC"/"ASC" before the "NULLS" part ? or doesn't matter ?

we can add this to 0.4.

[Oleg Deribas]

Hello,

Michael Bayer said the following on 09.08.2007 22:53:

> OK just to double check, the syntax looks like:
>
> SELECT * FROM sometable ORDER BY foo NULLS FIRST
>
> SELECT * FROM sometable ORDER BY foo DESC NULLS LAST

Yes.

> ? i.e. is "DESC"/"ASC" before the "NULLS" part ? or doesn't matter ?

It does matter according to Firebird Null Guide:
http://www.firebirdsql.org/manual/nullguide-sorts.html

--
Oleg

[Michael Bayer]

OK ive added ticket #723 for this, it will go into 0.4xx.