Order by NULL then id asc

Question

I want to have the following order:

1
2
3
NULL
NULL

Currently I have the following query:

ItemInstance.objcts.filter(tv_series_id=item.pk).order_by('id')

This will order by id ASC, but NULL will come at the beginning. How would I push NULL to the end without doing a second query?

This previous question may help: http://stackoverflow.com/questions/5993109/order-by-asc-with-nulls-at-the-bottom — Gene, Aug 06 '15 at 23:12

Ozgur Vatansever · Accepted Answer · 2015-08-06T23:44:08.207

You need to somewhat figure out how to convert NULL values into something comparable. Here is one way of doing that using .annotate():

ItemInstance.objects.filter(tv_series_id=item.pk).annotate(
    null_ids=Count('id')
).order_by('-null_ids', 'id')

Alternatively, I think you can also achieve to get the same result with raw SQL using .extra():

ItemInstance.objects.filter(tv_series_id=item.pk).extra(
    'select': {
        'is_null': 'CASE WHEN id IS NULL THEN 0 ELSE 1 END'
    }
).order_by('-is_null', 'id')

JuniorCompressor · Answer 2 · 2015-08-06T23:27:58.230

1

You can use extra to define a custom field that is 0 when id is non null, and 1 otherwise. Then you can sort first by this field to get the non null values first, and then sort by id.

ItemInstance.objects.filter(tv_series_id=item.pk).extra(
    select={'isnull': 'CASE WHEN id IS NULL THEN 1 ELSE 0 END'}
).order_by('isnull', 'id')

edited Aug 06 '15 at 23:27

answered Aug 06 '15 at 23:20

JuniorCompressor

19,631
4
30
57

score 0 · Answer 3 · answered Aug 06 '15 at 23:20

If you are using Postgresql, you can set the index on that column to put NULLs first or last, eg:

CREATE INDEX myindex ON mytable (mycolumn ASC NULLS LAST);

Then the database will take care of it.

If the index already exists you can modify it.. you can also, if you are uncomfortable with direct SQL, use pgadmin.. right click on the index and select properties and you will find the settings within...

Order by NULL then id asc

3 Answers3