Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sorting empty values last instead of first

I am trying to implement a sorting procedure which will sort according to the value of a drop down list. What happens is that when the list is being sorted, the NULL fields will be replaced by specified replacement value, in this case ''. This is being sorted first because we are sorting ascendingly, now I would want to sort ascendingly BUT with null values LAST. How could I implement this?

ORDER BY l.IsActive DESC, 
        CASE WHEN @SortOrder = 1 THEN n.DisplayName
             WHEN @SortOrder = 2 THEN CASE ec.IsEquipmentRelated
                                            WHEN  1 THEN ISNULL(el.ShopID,'') + 
                                                    ISNULL(ec.EquipmentAbbr,'') + 
                                                    ISNULL(el.ClassSequenceNumber,'') + 
                                                    ISNULL(el.EquipmentComponent,'') + 
                                                    ISNULL(el.CompSequenceNumber,'')
                                            WHEN  0 THEN ISNULL(ec.EquipmentAbbr,'')
                                            ELSE NULL
                                      END
             ELSE l.DisplayName
        END

Edit: Running MS SQL Server 2008 / T-SQL

Edit: @Joe Stefanelli, I have tried this, it's not compiling:

ORDER BY l.IsActive DESC, 
    CASE WHEN @SortOrder = 0 THEN l.DisplayName
         WHEN @SortOrder = 1 THEN CASE WHEN n.DisplayName = '' THEN 2 ELSE 1 END, n.DisplayName
         WHEN @SortOrder = 2 THEN 
                                  CASE ec.IsEquipmentRelated
                                        WHEN  1 THEN ISNULL(el.ShopID,'') + 
                                            ISNULL(ec.EquipmentAbbr,'') + 
                                            ISNULL(el.ClassSequenceNumber,'') + 
                                            ISNULL(el.EquipmentComponent,'') + 
                                            ISNULL(el.CompSequenceNumber,'')
                                        WHEN  0 THEN ISNULL(ec.EquipmentAbbr,'')
                                        ELSE NULL
                                  END
     END
like image 833
JF Beaulieu Avatar asked Jun 29 '11 18:06

JF Beaulieu


People also ask

How do I sort by null value in last?

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?

Ordering. When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. 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.

How are NULL values sorted?

How Are NULLs Sorted by Default? The SQL standard does not define the default ordering of NULLs. What does this mean? If you apply the ORDER BY clause to a column with NULLs, the NULL values will be placed either first or last in the result set.

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.


2 Answers

...
ORDER BY CASE WHEN YourField = '' THEN 2 ELSE 1 END, YourField
like image 124
Joe Stefanelli Avatar answered Sep 22 '22 17:09

Joe Stefanelli


Best way is to add a derived column that would have sort order values (CASE that checks whether the other column is null) and then order by that column.

like image 33
Stan C Avatar answered Sep 21 '22 17:09

Stan C