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