0 come last when sorting ascending

Question

I am trying to select from one table a list of products ordered by price, year, name, and others.... The problem is that I must make zero values come last when sorting ascending.

My code is:

SELECT * FROM Product P 
ORDER BY  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

This works but don't put the zero at the bottom of the list. So, I tried to transform it (see next code), but it gave me the error Incorrect syntax near ','.

SELECT * FROM Product P
ORDER BY CASE WHEN @OrderBy='Price ASC' THEN 

           (case A.Price WHEN  0 THEN 1 ELSE 0  END,A.Price )

END ASC

I appreciate any help

Answer 1

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.

Answer 2

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