title: Sorting based on multiple conditions
body: <p>I am doing a exercise 
  I'll fire a query on the DB and get some 500 results. Now i want to sort this list based on some conditions and present the sorted list in client side.
  I am using Java/Java EE and MySQL server 5.5
  Conditions are like this,
  Example: Consider a table having listed with cars
  So, i ll fire a query on the table and it will list some 500 cars. now i want to sort this list based on user criteria.
  conditions are age of car, colour of car and facilities of cars. List should be sorted like this
  First appears the list of cars which satisfies all three conditions ie., same age as mentioned by end user, same colour and with all facilities user selected.
  Second appears any 2 conditions satisfying cars list and one condition not satifying.
  Third appears any one condition satisfying cars list and not the other two.
  And finally appears the list of cars of which no conditions are satisfied.</p>
  
  <p>How can i achieve this. I have searched in google, asked in irc channels regarding this. Couldn't get any help.
  I have tried using RANK function by defining the CASES and finally order by RANK. It works for me while the conditions fields (columns) are of same table. In my case the fields are from a parent table as well as its child tables which has many to one relationship with its parent. Like in this example, age and color of the cars are stored in parent table and facilities that cars has are stored in another table. I tried doing the same using inner join, but no luck.</p>
  
  <p>I tried something like this:</p>
  
  <p>Query:</p>
  
  <pre><code>select distinct t0.id,t0.name,t0.price,
  CASE
  WHEN   
  t1.age='2' AND t1.colour='Red'  AND t2.facilities_id=9 THEN 1
  WHEN   
   t1.age='2' AND t1.colour='Red' AND t2.facilities_id!=9  THEN 2 
  WHEN  
  t1.age='2' AND t1.colour!='Red'  AND t2.facilities_id=9  THEN 3
  WHEN   
  t1.age!='2' AND t1.colour='Red'  AND t2.facilities_id=9  THEN 4
  WHEN   
  t1.age!='2' AND t1.colour='Red'  AND t2.facilities_id!=9  THEN 5
  WHEN   
  t1.age='2' AND t1.colour!='Red'  AND t2.facilities_id!=9  THEN 6
  WHEN   *
  t1.age!='2' AND t1.colour!='Red'  AND t2.facilities_id=9  THEN 7
  ELSE 8
  END as pre_status
  from cars_listing t0
  inner join
  cars_listing_details t1
  on t0.id=t1.mg_listing_id
  inner join
  cars_facilities_listing t2 
  on t1.cars_listing_id=t2.listing_id
  where t0.type='new_cars'
  order by pre_status
  </code></pre>
  
  <p>Thanks in advance for helping.</p>