select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `address`,round(price) as price,minimum_nights, `rating`, `review_count`,`latitude`, `longitude`,case when propertyName='' then trim(address) else CONCAT(CONCAT(propertyName,'(',trim(address)),'',')') end as addre,propertyName,
(6371 *
acos(
cos( radians( 21.2759167 ) ) *
cos( radians( `latitude` ) ) *
cos(
radians( `longitude` ) - radians( -157.8233677 )
) +
sin(radians(21.2759167)) *
sin(radians(`latitude`))
)
) as distance from `vacationrentalindex` a inner join vacationrentalindexfeed b on a.uniturl=b.unitUrl and a.headline!='' and a.thumbnailUrl!='' and b.enabled=1 and b.blocked=0 ) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kona Bay Estates%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This charming property has been kept the same since the original build to keep the "sugar plantation" style of the old Hawaii days. No major upgrades have been made to keep an old Hawaii days feel. The RATES REFLECT the style accordingly. This ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua Kona
This luxury, 2-story oceanfront estate on Big Island boasts one of the best locations on the entire Kona coast. With 3 bedrooms, 3.5 bathrooms, expansive living areas, and unique amenities for up to 10 guests, you’ll truly be able to claim the...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kailua Kona