select p1.*,p2.* from wp_property_propertypilot as p1 LEFT join wp_property_propertypilot_meta_options as p2 on p1.property_id = p2.property_id where p1.property_status = '1' and property_name = 'status' and (p2.property_value NOT IN('Let','Sold','Sold STC')) and (p1.property_leasehold >= 0 or p1.property_freehold >= 0) GROUP By p1.property_id ORDER BY CASE WHEN (p2.property_value in ('To Let','For Sale/To Let','For Sale')) THEN 1 WHEN (p2.property_value in ('Under Offer')) THEN 2 ELSE 3 END, p1.id desc LIMIT 0, 12