select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Banyan Tree%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Complete oceanfront penthouse with two bedrooms and two condos at the top floor.
Wondrous views of the ocean, reefs, moon and sunset plus all the facilities that your family and you enjoy..
Surfers and long waves have entertained visitors ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua Kona
Experience amazing views from every room of this gorgeous oceanfront condominium.
You'll love waking up to the site and sounds of this ideal Hawaii condominium, which comfortably accommodates up to four guests. The beautifully furnished air ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kailua Kona
OCEAN FRONT JEWEL!
You will think you're in heaven when you see the dazzling view from this 2 bedroom, 2 bath 3rd floor condo. Enjoy spectacular Kona sunsets, whales in winter, dolphins, and surfers just below at a famous surf break.
...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Knutson & Associates is very proud to represent this recently acquired unit. Banyan Tree 404 is a top floor condo in a secure and private building with a elevator, pool, spa and barbecue!
Look no further to have the perfect vacation! This condo ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
HI> Kailua-Kona