Using PHP/MYSQL to sort data based on partial values

Question

I got prefix values setup for my products that help me identify drinks from food. If its a drink it will contain (D) as the prefix.

(D)Beer
Burger
(D)Soft Drink
Sandwich

SELECT * FROM orders  order by pname

I want to sort the drinks together and the food together in a single query. Hope that makes sence and thx for the help :)

Why do you put use a prefix to distinguish drinks from food? Why don't you use another column in the table, call is is_drink? — Barmar
– Barmar, Commented Aug 19, 2014 at 9:21
@Austin and I seem to have interpreted your question differently. Could you show the results you want to get? — Barmar
– Barmar, Commented Aug 19, 2014 at 9:22

Barmar · Accepted Answer · 2014-08-19 09:23:55Z

3

SELECT *
FROM orders
ORDER BY LEFT(pname, 3) = "(D)", pname

LEFT(pname, 3) = "(D)" will be 0 for drinks, 1 for foods. So this will put all drinks first, then all foods. So the results will be:

(D)Beer
(D)Soft Drink
Burger
Sandwich

answered Aug 19, 2014 at 9:17

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hakim Saifee · Accepted Answer · 2014-08-19 10:02:23Z

0

Try the below query, will work..

***select * from orders order by case

when pname LIKE "(D)%" then 1

else 2

end***

answered Aug 19, 2014 at 10:02

Hakim Saifee

715 bronze badges