0 come last when sorting ascending 0 come last when sorting ascending sql sql

0 come last when sorting ascending


You can do it by testing for price-ordering twice:

SELECT * FROM Product P ORDER BY  CASE WHEN @OrderBy='Date ASC' THEN Date  END ASC,          CASE WHEN @OrderBy='Price ASC' THEN CASE WHEN Price = 0 THEN 1 ELSE 0 END ASC,          CASE WHEN @OrderBy='Price ASC' THEN Price END ASC,          CASE WHEN @OrderBy='Title ASC' THEN Title  END ASC,          CASE WHEN @OrderBy='' THEN Match END

By the way, the implicit value of the case expression when @orderBy doesn't equal the string is null. When the sort column contains all nulls, it effectively disables sorting for that attribute.


I would suggest using a large dummy price:

ORDER BY CASE WHEN @OrderBy='Price ASC' THEN 99999999 ELSE A.Price END ASC

or if you DBMS supports NULLS LAST:

ORDER BY CASE WHEN @OrderBy='Price ASC' THEN NULLIF(A.Price,0) END ASC NULLS LAST


You can try with this syntax:

SELECT *,    CASE WHEN @OrderBy = 'Price ASC' AND Price = 0 THEN 1 ELSE 0 END AS OrderPriceZeroLastFROM Product P ORDER BY OrderPriceZeroLast,    CASE WHEN @OrderBy = 'Date ASC' THEN Date END ASC,    CASE WHEN @OrderBy = 'Price ASC' THEN Price END ASC,    CASE WHEN @OrderBy = 'Title ASC' THEN Title END ASC,    CASE WHEN @OrderBy = '' THEN Match END