Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql order by field with NULL values last

Tags:

php

mysql

I have been having an issue when using the FIELD function in my order by clause.

My situation is a product can have three categories and the user can choose what category to show first. So there are three possible queries that can be formed. These are:

Query 1

SELECT 
    *
FROM
    my_table
WHERE
    main_categories_id = 2
ORDER BY FIELD(product_condition,
        'graded',
        'new',
        'used');

Query 2

SELECT 
    *
FROM
    my_table
WHERE
    main_categories_id = 2
ORDER BY FIELD(product_condition,
        'new',
        'graded',
        'used');

Query 3

SELECT 
    *
FROM
    my_table
WHERE
    main_categories_id = 2
ORDER BY FIELD(product_condition,
        'used',
        'new',
        'graded');

This does not work well when the product condition is NULL because it always shows the rows with a NULL value first. I need these to appear last.

I have tried adding NULL to the FIELD function but this doesnt seem to work.

Does anyone know a way I can achieve this?

Thanks for your help.

like image 573
David Jones Avatar asked Mar 06 '14 09:03

David Jones


People also ask

How do I ORDER BY NULL last?

If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.

How do I order a NULL last value in SQL?

If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.

When data is sorted in descending order are NULL values listed first or last?

So ordering in DESC order will see the NULLs appearing last. To force NULLs to be regarded as highest values, one can add another column which has a higher value when the main field is NULL.

When data is sorted in ascending order NULL values appear first in the list?

SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.


1 Answers

You can either:

  1. Explicitly sort first by whether the column is NULL and then by its value:

    ORDER BY product_condition IS NULL, FIELD(...)
    

    This works because product_condition IS NULL will be 0 for non-NULL columns and 1 for NULL columns; and in the (default) ascending order, the former will obviously come first.

  2. Rely on the fact that NULL is sorted last in descending orderings and reverse the arguments to FIELD():

    ORDER BY FIELD(product_condition, ...) DESC
    
like image 186
eggyal Avatar answered Oct 04 '22 15:10

eggyal