CODEWITHSUNDEEP
mysqlsqlsql-order-by
frostrating kapoooo
frostrating kapoooo

Reputation: 3

MySQL order by field(column_name, subquery)

I'm trying to get data in order to display something like this:

db schema:

category_id parent_category
banana fruit
apple fruit
kiwi fruit
cucumber vegetable
celery vegetable
pork meat
beef meat
chicken meat
t-shirt clothes
jeans clothes
sweater clothes
meat food
fruit food
vegetable food
food null
clothes null 
select * from category 
order by 
field(category_id, "banana") desc,
field(parent_category, (SELECT parent_category FROM category WHERE category_id = "banana")) desc
field(category_id, (select category_id from category where parent_category <> (SELECT parent_category FROM category WHERE category_id = "banana"))) desc

my goal result is to display with following order:
banana first,
other kinds of fruits second,
other kinds of food third,
clothes last (all other data that are not considered as food.)
second and third level categories don't have to be displayed (meat, fruit, vegetable, t-shirt, jeans, sweater, food, clothes)

Upvotes: 0

Views: 46

Answers (1)

FanoFN
FanoFN

Reputation: 7114

Perhaps something like this:

SELECT * FROM category 
 WHERE 
    IF(parent_category='food',NULL,parent_category) IS NOT NULL
ORDER BY
   CASE WHEN category_id='banana' THEN 1
        WHEN parent_category='fruit' THEN 2
        WHEN parent_category='clothes' THEN 99
        ELSE 3 END;

Using CASE expression in the ORDER BY section and filter out parent_category that is food and NULL. Result will be like this:

category_id parent_category
banana fruit
apple fruit
kiwi fruit
cucumber vegetable
celery vegetable
pork meat
beef meat
chicken meat
t-shirt clothes
jeans clothes
sweater clothes

Demo fiddle

Upvotes: 3

Related Questions