mysql query using order by clause

Question

I have field that store yes/no value that name is SelectionFavorite

and I want fetch record like

first all record that contain SelectionFavorite value yes and other remaining records

I do some query like

SELECT p.Id,
       p.NameLatin,
       p.NameEnglish,
       cm.DescriptionEnglish,
       p.SelectionFavorite,
       p.SelectionGarden,
       p.SelectionPerso1,
       SelectionPerso2,
       SelectionPerso3
FROM plant p
INNER JOIN CategoryMain cm ON p.CategoryMain = cm.id
ORDER BY (p.SelectionFavorite=yes)

but that return only those record that have yes value.

I want to this type of result

Id NameLatin NameEnglish DescriptionEnglish SelectionFavorite .....

1 name1 eng1 desc1 yes yes yes no no

2 name2 eng2 desc2 yes yes yes no no

3 name3 eng3 desc3 yes yes yes no no

4 name4 eng4 desc4 yes yes yes no no

5 name5 eng5 desc5 yes yes yes no no

6 name6 eng6 desc1 no yes yes no no

7 name7 eng7 desc2 no yes yes no no

8 name8 eng8 desc3 no yes yes no no

9 name9 eng9 desc4 no yes yes no no

how to solve this query ?

Answer 1

You can use a solution like this:

SELECT p.Id,
   p.NameLatin,
   p.NameEnglish,
   cm.DescriptionEnglish,
   p.SelectionFavorite,
   p.SelectionGarden,
   p.SelectionPerso1,
   SelectionPerso2,
   SelectionPerso3
FROM plant p
INNER JOIN CategoryMain cm ON p.CategoryMain = cm.id
ORDER BY CASE p.SelectionFavorite WHEN 'yes' THEN 1 ELSE  2 END;

Answer 2

The order by clause to put the "yes" values first followed by the others is:

ORDER BY (p.SelectionFavorite = 'yes') desc

Your query should not be filtering anything out, although it would be putting the non-yes values first.