Complex order by clauses

I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)

I thought I'd be able to do something like:

ORDER BY (price != 0), price

thinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.

any suggestions?

thanks!

try to do SOMETHING LIKE IN this EXAMPLE:

select

*into #testfrom(select 129.89 price)aa

insert

into #testselect 19.89

insert

into #testselect 1.89

insert

into #testselect 49.89

insert

into #testselect 39.89

insert

into #testselect 29.89

insert

into #testselect 0

insert

into #testselect 0

select

*from #TEst

order

by
case price
When 0then 1
else
0
END,
price

drop

table #test