ActiveRecord: Order by firstcolumn, (secondcolumn='foobar'), thirdcolumn...possible?

I have an activerecord query that works as follows:

A user searches for a zipcode - let's say 90210. The query then identifies the state (California), and returns all results that have a ma开发者_如何学Ctch either in the 'zipcode' field, or in the 'state' field.

What I'd like to do is order the results that satisfy the exact zipcode first. So results with the precise zipcode '90210' will be ranked higher than results which just satisfy 'California' as a state...keeping in mind that results with a 90210 zipcode may also have 'California' as their state.

Any suggestions?

You don't say what dmbs you use - in msysql you can do a conditional order like

:order => "IF(zipcode = '#{params[:zipcode]}', 0, 1)"

Other dbms's might do this differently, i think the above is standard though. An alternative way to do it is

 :order => "(zipcode = '#{params[:zipcode]}') desc)"

The desc is just necessary because the if test implicit in the above returns 1 for true and 0 for false, and 1 comes after 0 so true results come after false results. Hence the desc to shove the true ones to the top.

I'm a bit confused about exactly what you want to do though. If you want to find all results where the state or zipcode matches params[:zipcode], but push those matching zipcode to the top, it would be

YourModel.find(:all, 
  :conditions => ["zipcode = ? or state = ?", params[:zipcode], params[:zipcode]], 
  :order => "IF(zipcode = '#{params[:zipcode]}', 0, 1)")

If you want to order by other columns first (as suggested by the post title) just put them before the IF call in the order option, eg

:order => "name, IF(zipcode = '#{params[:zipcode]}', 0, 1), zipcode"