英文:
Ordering a food menu by 2 columns: dish & dishprice MSSQL
问题
I'm trying to order by 2 columns: dish and dish price. They are both stored as strings so it casts the dish price 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.
英文:
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
得分: 0
如果您将查询更改为
SELECT dish, CAST(dishprice AS DECIMAL(18,2)), category FROM tbldishes
您将注意到第二列在所有情况下都是整数(0,1,2)
尝试使用
SELECT dish, dishPrice, category FROM tbldishes ORDER BY CAST(dishPrice AS DECIMAL(18,2)) , dish
或者更好的做法是,在您的表中使用正确的数据类型。
英文:
If you change your query to
SELECT dish, CAST(dishprice), category FROM tbldishes
you will note that the second column is an integer in all cases (0,1,2)
try using
SELECT dish, dishPrice, category FROM tbldishes ORDER BY CAST(dishPrice AS DECIMAL(18,2)) , dish
Or better yet, use the correct data type in your table.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论