How to order 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 use rows based on column preferences?

I need something like this:

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

And I expect this conclusion:

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

Please help me solve my problem. Thanks.

Answer 1

I think you could do something like:

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

Expression Relation='Self', Relation='Husband', Relation='Son'return 0, or 1(in the order of adding) depending on whether or not satisfied. Thus, it can generate the required order

You can also use the FIELD function of MySQL as:

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

Answer 2

(Self, Husband, Son) -, . :

:

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

:

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

Answer 3

Try to complete the request

select *,if(Relation = 'Self',1,if(Relation = 'Husband',2,if(Relation = 'Son',3,4))) as rel_ord from table order by p.No asc ,rel_ord asc

Answer 4

Just list them in order of preference.

ORDER BY P.No,Name,Relation ASC