SELECT DISTINCT(p.propid) ,if((SELECT pi.id FROM property_image AS pi WHERE pi.propid=p.propId and ((photo_thumb AND (isilink=0 AND escapia=0)) or (path!='' AND (isilink=1 or escapia=1)))  GROUP BY propid),1,0) as photo_thumbimg,if(min_rate or max_rate OR min_week or max_week ,1,0) as Price,r.revs, p.reviews,if(p.reviews!="",p.reviews,r.revs) as newrev,p.category_id,category.name as cat_name,p.people_sleep,p.bedrooms,p.bathrooms,p.children,p.airport,p.features,p.pets,p.smoke,p.zip,
		p.wheelchair,p.prop_title,p.prop_desc,p.property_type,prop_date,display_calender,other_url_calender,is_feature,p.isilink_coid, p.isilink_prop_id, p.isilink, p.escapia, p.escapia_prop_id, p.min_rate, p.max_rate,p.min_week, p.max_week,p.min_month, p.max_month, p.review_rating, s.offer_start_date as special
		
		FROM property p 
		LEFT JOIN 
                    (
                        SELECT propid,count(id) revs
                        FROM review
                        WHERE status = 1
                        GROUP BY propid
                    ) r
                        ON r.propid = p.propid
        LEFT JOIN special_offer s ON ( p.propid = s.propid )                
		LEFT JOIN category ON (p.category_id=category.id) 
		LEFT JOIN effective_dates AS ed ON(ed.propId=p.propId)
        
		WHERE  p.active IN("yes","d")   AND ( p.bedrooms>=4  )  AND (  (p.people_sleep >= 14 AND p.people_sleep <= 18) )  GROUP BY p.propid ORDER BY photo_thumbimg desc,Price desc, newrev ,p.is_feature DESC,p.category_id asc,p.propid ASC LIMIT 0,20