How to display certain listings first then order by

[curlypinky]

I have a site that offers real estate mls search and contains the listings of the client plus the whole county. It has a database where each listing has a corresponding officeId. This particular site has 2 offices with separate ids combined as one entity. On search results I previously had it ordering results by price, regardless of officeId but now they want to give their listings precedence over the listing from other offices. So I am trying to figure out how to order up their listings first in results. The closest I have got it using ORDER BY FIELD:

ORDER BY FIELD(officeId,1126,4136) DESC, price DESC

 

BUT the problem with this is it will list all of office 1126 listings high to low, then office 4136 high to low then everyone else high to low. What I need it to do is combine 1126 and 4136 listings and display them ordered by price then list everybody else.

 

Your expertise will be appreciated!

Thanks,

Alane

 

MySQL 5.0.81

PHP Version 5.2.5

[kickstart]

Hi

 

Probably best to add a new field to the list for the order and include that in the sort. Something like this:-

 

SELECT SomeHouseDetail, SomeHousePrice, SomeOfficeId, (CASE WHEN SomeOfficeId = 1126 THEN 0 WHEN SomeOfficeId = 4136 THEN 0 ELSE 1 END) AS OfficeOrder
FROM SomeTable
ORDER BY OfficeOrder, SomeHousePrice

 

All the best

 

Keith

[curlypinky]

Thank you so much Keith!

 

Your example set me on the right track, I had to modify the case statement a bit to get it to work for me but it's functioning great now!

$query = "SELECT SQL_CALC_FOUND_ROWS A.*, (CASE officeId WHEN 1126 THEN 0 WHEN 4136 THEN 0 ELSE 1 END) AS officeOrder FROM ".cms_db_prefix()."module_akamaidx_listings A";
$query .= ' ORDER BY officeOrder, price DESC';

 

Thank you!