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

xqnpmsa8  于 2022-10-30  发布在  其他
关注(0)|答案(1)|浏览(134)

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.

brccelvz

brccelvz1#

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.

相关问题