Ordering a food menu by 2 columns: dish & dishprice MSSQL

huangapple go评论61阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年5月11日 15:43:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/76225202.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定