Put NULLS Last on MySQL

NULLs are a wonderful but strange feature of SQL. They are the source of some deep coolness but also some lost hair. One place where NULLs can bite you is when you are sorting. The way you sort in SQL is by appending the ORDER BY clause to the end of a SELECT statement. The problem is that the SQL standards say that for the purpose of sorting, NULL = NULL but they don’t specify how NULL values should be ordered relative to non-NULL ones. Doh!

In this post I describe the solution to this problem in the context of MySQL and Ruby on Rails.

Now imagine you’ve got a Ruby on Rails app that manages shipments and those shipments have an attribute called expected_at:

    1       t.column :expected_at, :datetime

And then let’s say you’d like to present a list of shipments ordered by their expected time. You’d like shipments expected soonest to appear before shipments expected later. You might have a core snippet of code like this:

    1 @sorted_shipments = association.find(
    2   :all,
    3   :order => 'expected_at ASC',
    4   :limit => @shipment_pages.items_per_page,
    5   :offset => @shipment_pages.current.offset)

Now what happens if you don’t know the expected time for a shipment? Well the natural thing to do in SQL is to leave the value NULL. Now the problem arises. If for instance you’re running on MySQL, shipments with NULL expected_at times will appear before those with non-NULL ones. So what can you do?

Well my first thought was to have two separate associations — one for shipments that had NULL expected_at times and a second one for those that didn’t. The problem with that approach (if you’re using the Ruby on Rails Paginator framework) is that it leaves you to do a bunch of bookkeeping in application code. You really don’t want to hand code pagination over multiple associations. Or I should say — I certainly don’t want to.

My second thought was that I was missing some key knowledge of Ruby on Rails. When in doubt — assume that Rails has thought of it. But a fair amount of spelunking turned up nothing. Rails is mute on this issue.

So where to turn next? The database of course! An initial search for “sql sort null” led me to an Oracle reference that mentioned Oracle’s NULLS LAST clause. When I tried NULLS LAST on MySQL, naturally it failed. MySQL doesn’t support it. A subsequent search for “mysql nulls last” yielded gold. Turns out there is an obscure syntax available in MySQL which you can use to control the relative ordering of NULL and non-NULL values:

NULLs are considered lower than any non-NULL value, except if a – (minus) character is added before the column name and ASC is changed to DESC, or DESC to ASC; this minus-before-column-name feature seems undocumented.

Clear enough. Er. Not very. If you’re like me you need an example. To make the previous example do what we want, here’s what the code looks like now:

    1 @sorted_shipments = association.find(
    2   :all,
    3   # this is the same as doing 'expected_at ASC NULLS LAST' on Oracle
    4   :order => '-expected_at DESC',
    5   :limit => @shipment_pages.items_per_page,
    6   :offset => @shipment_pages.current.offset)

So that’s… stick a minus in front of the column name and invert the apparent sense of the ordering. Now you’ll get earlier arrivals before later ones and you’ll get NULL’s (indeterminate arrivals) at the very end. Whew.

About these ads
This entry was posted in Ruby on Rails, SQL. Bookmark the permalink.

18 Responses to Put NULLS Last on MySQL

  1. Adam Keys says:

    Thanks for the handy tip I’d never have to look for.

    FYI, paginators are On Notice these days (last I heard). I think Bruce Williams’ paginator gem might be the new hotness, but I haven’t had the need to paginate just yet, so I may be wrong.

  2. Bill Burcham says:

    Yeah, I’ve heard those rumblings. Do the new paginators handle the sorting issues or is it still up to the user to provide the sorted objects?

  3. Pingback: Cuentos Web

  4. thanks a lot! great solution!

  5. Rick says:

    What about this?

    ORDER BY (expected_at IS NOT NULL)

  6. Bill says:

    I haven’t tried that Rick. My guess is that it would partition the results but that it would fail to order by expected_at (for those records having a non-NULL expected_at).

  7. no way!

    mysql is so quirky

  8. Frank Wöckener says:

    If I want to sort a model “Project” (belongs_to :person) by a foreign attribute:

    Project.all(:include => ‘person’, :order => ‘-people.sname DESC’)

    it doesn’t throw an error but doesn’t work either. BUT – when I do it like this:

    Project.all(:include => ‘person’, :order => ‘-people.sname DESC, people.sname ASC’)

    it magically works – nulls at the end. Very mysterious…

  9. Roman Kofman says:

    This should be more intuitive:

    :order => "expected_at IS NULL ASC, expected_at"

    Essentially, (expected_at IS NULL) returns a 0 or 1, and the rows are sorted by that value. A secondary sort is then applied by the expected_at column, with the nulls staying where they are.

  10. bburcham says:

    Oh good one Roman!

  11. Shannon says:

    Thank you!

  12. Joe Ellis says:

    @Roman, that’s a great tip as most of the solutions seem to only work when sorting ascending. Yours, however, allows you to control both, but still keep the NULLs at the bottom regardless of sort type.

  13. Michiel says:

    Thanks Roman!!!

    Can’t believe mysql does not allow for easier control, but anyway, this seems to work.

  14. Simon says:

    sweet dude thanks for that. saved me big time.

  15. Lee says:

    Awesome! Thanks so much! Saved me hours, I’m sure!

  16. Trevor says:

    Who would have thought just a minus sign on the column name would ignore null values! I did look for hours and tried all sorts of funky ways of doing it. Thank you for the simple and slick example!

  17. Pingback: Ordenar campos con valores NULL en MySQL: Por qué los NULL primero | Virtual Consulting Magazine

  18. Einar Lielmanis says:

    Please be careful using “-field desc” solution. The minus is a simple mathematical minus, and all it does is it silently turns the field value to a (negative) number, which is then sorted in the usual fashion: it just happens to work with date and numeric fields (date values are turned into a unix timestamp, e.g “select -now()” gets converted -20120925150855).

    As such, this solution won’t work at all with strings: “… order by -email desc” will convert all emails to integers for sorting (and that will be just zero), and then sort these zeroes, i.e. won’t sort anything at all. So you’re in for a suprise if willing to sort names, surnames, emails or similar data.

    Thus, it’s much, much safer to use “is null asc” solution, as that will work under all circumstances.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s