sql - Sorting empty values last instead of first

ID : 274394

viewed : 27

Tags : sqlsql-servertsqlsortingstored-proceduressql





Top 5 Answer for sql - Sorting empty values last instead of first

vote vote

90

... ORDER BY CASE WHEN YourField = '' THEN 2 ELSE 1 END, YourField 
vote vote

86

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.

vote vote

78

Your second snippet will not compile because you are trying to return more than one value in the WHEN @SortOrder = 1 branch of the outer CASE. You could fix it if you made a couple of tiny changes to the said branch:

  1. Change 2 and 1 to , for example,'2' and '1' respectively.

  2. Replace the comma (,) with the concatenation operator (+).

And so the resulting ORDER BY clause would look like this:

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 
vote vote

63

OK! I have found out a way to solve this using all three answers combined. +1 for each answer! What I did is first to create a 'virtual' derived column, assigning null values to 1 and non-null values to 0, sorting that first, then sorting out the rest:

ORDER BY     l.IsActive DESC,     CASE     WHEN @SortOrder = 0 THEN (Case When l.DisplayName Is Null Then 1 Else 0 End)     WHEN @SortOrder = 1 THEN (Case When n.DisplayName Is Null Then 1 Else 0 End)     WHEN @SortOrder = 2 THEN (Case When ec.EquipmentAbbr Is Null Then 1 Else 0 End)    END,    CASE        WHEN @SortOrder = 0 THEN l.DisplayName       WHEN @SortOrder = 1 THEN n.DisplayName       WHEN @SortOrder = 2 THEN                 ISNULL(el.ShopID,'') +                 ISNULL(ec.EquipmentAbbr,'') +                 ISNULL(el.ClassSequenceNumber,'') +                 ISNULL(el.EquipmentComponent,'') +                 ISNULL(el.CompSequenceNumber,'')    END 

This way, the sorting of if a field is null or not is done before the actual sorting is done, which brings the null values at the end.

vote vote

54

Top 3 video Explaining sql - Sorting empty values last instead of first







Related QUESTION?