This is my requirement:
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/J2EE 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.
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
Thanks in advance for helping.