Ordering Results by Input?

[Guest]

I have a very simple searchable database – the user selects three items and the database returns some results based on that selection.  This works just fine.  But, now I want to order the results.  I can easily order them by the column from which they came in the db – I have no problem with this.  What I want to do is order the results based on one of the inputs.

For example, let’s say that a user wants to buy a bicycle – comes to my site and selects the type of bicycle, his price range, and the state that he is in.  The db returns the results of ALL the states that have those bicycles in that price range.  But, I want the results to be ordered in a way that the results from the user’s state come up first – then all the results from the other states are show.

i.e. – This user’s state is TX – there are results for the bicycle he wants, in his price range from TX, CA, MA, FL.  I want the results from TX to show first – then the remaining results to follow.  If I use an order by clause on the state column ASC – I would get CA first – then the rest in alphabetical order.

How could I get the results from the user’s state to show up first?

Here is the way I have it set up:

SELECT DISTINCT bike_name, company_name, company_location, bike_description, company_id, bike_id

FROM company, bike

WHERE FIND_IN_SET(name, bike_name) AND FIND_IN_SET(state, company_location) AND (company_id = bike_id) OR FIND_IN_SET(name, bike_name) AND FIND_IN_SET('ALL', company_location) AND (company_id = bike_id)

Is there a way to do this?

[David_Powers · Accepted solution]

Use ORDER BY IF().

ORDER BY IF(state='TX',0,1), state

This puts TX at the top of the pile. In your query, replace 'TX' with a variable and set its data type to text.

The 0,1 in the IF() function makes the selected value float to the top of the pile.

[Guest]

Mr. Power,

Thanks again - that worked great.  Would have never thought to use an IF statement - the sites that I tried to research this only used the standard order by "column".

Thanks again.

[David_Powers]

I learned that technique from Paul DuBois, author of the best books on MySQL.