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
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.
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 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.
SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
...
ORDER BY CASE WHEN YourField = '' THEN 2 ELSE 1 END, YourField
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With