In sqlalchemy, is there a way to sort so that empty cells are at the end regardless of the sort order?

Question

I have a pretty standard setup and want to sort by column:

someselect.order_by(asc(table1.mycol))

However, I want the lines with ''

or NULL

for to mycol

appear at the end of the results. Is there a way to do this?

+3

rohithpr 05 june 17 at 19:02

2 answers

univerio · Answer 1 · 2017-06-05T23:20:54+0000

On databases that support NULLS LAST

you can sort NULL

at the end by doing

SELECT * FROM table1 ORDER BY mycol ASC NULLS LAST;

You need to convert ''

to NULL

so you can do this (which I recommend doing anyway), either in data or as part of a query:

SELECT * FROM table1 ORDER BY (CASE mycol WHEN '' THEN NULL ELSE mycol END) ASC NULLS LAST;

Alternatively, a more portable approach

SELECT * FROM table1 ORDER BY (CASE WHEN mycol IS NULL OR mycol = '' THEN 1 ELSE 0 END) ASC, mycol;

To write this to SQLAlchemy:

.order_by(case([(or_(tabel1.mycol.is_(None), table1.mycol == ""), 1)],
               else_=0), table1.mycol)

wodow · Answer 2 · 2017-10-16T15:39:28+0000

SQLAlchemy has a modifier NULLS LAST

for expressions ORDER BY

:

sqlalchemy.sql.expression.nullslast(column)