On Sep 25, 2:06 am, Alex 9999 <[email protected]> wrote: > Hello. > > I have a table like > items > id code0 name > ------------------------ [...] > > I use an order_by(code0) to sort query, and it sort like 1, 2, 43, > 321, None. I need to get None-values to top, like > None, 1, 2, 43, 321. Is it possible with order_by function?
Null ordering is handled differently by different DBs, and I don't think SQLAlchemy does anything to make it consistent. Some DBs may support the syntax "order by code0 nulls first", but I think in order to get that you'd have to use a text() block in SQLAlchemy to inject raw SQL. And many databases do not support that syntax, or have their own version of it. To do it in a way that will work everywhere, have a look at the coalesce function. This is a SQL function, not specific to SQLAlchemy. It would be something like order_by(func.coalesce (items.c.code0, REALLY_BIG_VALUE).desc()). The coalesce function will return the value of code0 if it is not NULL/None, and REALLY_BIG_VALUE otherwise. You should use something for REALLY_BIG_VALUE that is bigger than the biggest value in the table. You could always select max(code0) to find that out, but it will be more efficient if you know for a fact that it will never be bigger than, for example, 2**32, or something like that. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
