How to order by various values in MySQL?

Question

Example:

----------------------------------------------
P.No     |     Relation   |  Name 
----------------------------------------------
2        |     Self       | Kumar
----------------------------------------------
1        |     Husband    | Selvam
----------------------------------------------
2        |     Son        |  Manoj
----------------------------------------------
1        |    Self        |   Gandhi
----------------------------------------------

How can I the rows based on column values preference?

I want something like this:

Order By P.No & 
 ( Self 1 st preference ,  
   Husband 2nd preference,
   son 3rd Preference ) 

And I'm expecting this output:

----------------------------------------------
P.No     |     Relation   |  Name 
----------------------------------------------
1        |      Self       |   Gandhi
----------------------------------------------
1        |     Husband    | Selvam
----------------------------------------------
2        |     Self       | Kumar
----------------------------------------------
2        |     Son        |  Manoj
----------------------------------------------

Please help me resolve my problem. Thank you.

Answer 1

You want to translate The triple (Self, Husband, Son) into something, that is compareable. There are a few ways to do that:

The naive way:

ORDER BY IF(Relation="Self",0,IF(Relation="Husband",1,2))

Or the funky way:

ORDER BY (Relation="Self")+2*(Relation="Husband")+3*(Relation="Son")

Answer 2

I think you could probably do something like:

order by p.`No`, `Relation`='Self', `Relation`='Husband', `Relation`='Son'

The expressions Relation='Self', Relation='Husband', Relation='Son' return 0 or 1 (in the order added) depending if satisfied or not. Thus can generate the needed ordering

You can also use the FIELD function of MySQL as:

order by p.`No` ASC, FIELD(`Relation`,'Self,Husband,Son') ASC