SELECT CMT.*, CNT.country_name, ST.state_name, ST.state_code, CT.city_name, CAT.name as category_name, CAT.uri_slug as category_uri_slug, (SELECT GROUP_CONCAT(category_id) FROM community_categories WHERE community_id = CMT.id ) as category_id_grp, (SELECT file_name FROM community_images WHERE community_id = CMT.id AND featured=1) as featured_image, (SELECT price FROM community_pricing WHERE community_id = CMT.id ORDER BY price ASC LIMIT 1) as starting_price, (SELECT GROUP_CONCAT(room_type) FROM community_pricing WHERE community_id = CMT.id) as room_type_grp, (SELECT GROUP_CONCAT(state_id) FROM community_locations WHERE community_id = CMT.id) as state_id_grp, (SELECT GROUP_CONCAT(city_id) FROM community_locations WHERE community_id = CMT.id) as city_id_grp ,(((acos(sin((37.751*pi()/180)) * sin((CMT.latitude*pi()/180))+cos((37.751*pi()/180)) * cos((CMT.latitude*pi()/180)) * cos(((-97.822-CMT.longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM communities as CMT LEFT JOIN categories as CAT ON CAT.id = CMT.category_id LEFT JOIN countries as CNT ON CNT.id = CMT.country_id LEFT JOIN states as ST ON ST.id = CMT.state_id LEFT JOIN cities as CT ON CT.id = CMT.city_id WHERE CMT.featured = '1' AND CMT.status = '1' ORDER BY CASE WHEN CMT.listing_featured_order REGEXP '^[0-9]' THEN 1 WHEN CMT.listing_featured_order IS NULL THEN 2 END, CASE WHEN CMT.listing_featured=1 THEN CMT.listing_featured_order END ASC, CASE WHEN CMT.listing_featured=0 THEN distance END ASC LIMIT 3