I'm trying to order by 2 columns: dish and dish price. They are both stored as strings so it casts the dishprice to Decimal. Its basically a takeaway food menu, so I want it to start with the lowest price item and end with the most expensive item. However, it's nice to have them in some sort of alphabetic order too. Here's what I'm getting for the sundries menu:
MS SQL:
SELECT dish, dishPrice, category FROM tbldishes ORDER BY CAST(dishPrice AS DECIMAL) ASC, dish ASC
Output:
Barbeque Sauce 0.85
Coke Zero 1.00
Coleslaw 0.85
Garlic Mayo 0.85
Pepsi Max 1.00
Chips 2.95
It want and would have expected this..
Barbeque Sauce 0.85
Coleslaw 0.85
Garlic Mayo 0.85
Coke Zero 1.00
Pepsi Max 1.00
Chips 2.95
Which keeps the sauces together and drinks together whilst still in price order. The menu's are obviously much bigger than this. Any help appreciated.
1条答案
按热度按时间brccelvz1#
If you change your query to
you will note that the second column is an integer in all cases (0,1,2) try using
Or better yet, use the correct data type in your table.