CODEWITHSUNDEEP
mysqlsorting
Dinesh G
Dinesh G

Reputation: 1365

How to order by various values in MySQL?

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.

Upvotes: 4

Views: 64

Answers (4)

SicLeaf
SicLeaf

Reputation: 11

Just list them in order of preference. 

ORDER BY P.No,Name,Relation ASC

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33813

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

Upvotes: 4

Shrey Prajapati
Shrey Prajapati

Reputation: 559

Please try below query

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

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65264

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")

Upvotes: 5

Related Questions