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 '%Alii Villas%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Brand new to the rental market
This is one of the most stunning oceanfront condos in Alii Villas.
OUR RESPONSE TIME is less than one hour on inquiries!!
Totally upgraded and set up for your comfort. Sleeps 4 people comfortably with walk in ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
**Construction Notice: Complex Spalling Repairs ongoing through 2022.**
Top floor 1 bedroom 1 bathroom condo nicely upgraded with ocean view. Unit 324 has a great location! The spacious lanai offers a beautiful view of the ocean, and offers a...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
This is one of the most stunning oceanfront condos in Alii Villas.
OUR RESPONSE TIME is less than one hour on inquiries!!
Totally upgraded and set up for your comfort. Sleeps 4 people comfortably in 2 queen beds, walk in showers in both ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Wonderfully tropical Hawaiian experience. Light and bright charming condo with air conditioning in the living room and bedroom.
OUR RESPONSE TIME is less than one hour on inquiries!!
Private lanai overlooks the pool area and lush gardens with a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Located in lovely maintained oceanfront resort complex called Alii Villas on Alii Drive, less than one mile south of downtown Kona 180 NIGHT MINIMUM!, $2400.00 per month . Oceanview swimming pool and barbeque area to relax and work on your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kialua-Kona
Upgraded garden view unit with a peek of the ocean. A short 2 minute walk to the waterfront seating area.
OUR RESPONSE TIME is less than one hour on inquiries!!
Top floor. well equipped condo with everything you need for a comfortable stay. Flat...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
This top floor Alii Villas condo, Unit #340, has NEW owners as of December 11th 2020 and a NEW look. It welcomes visitors with a bright, clean, open air floor plan that boasts a large lanai facing the ocean. Watch the beautiful western evening ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
**Construction Notice: Complex Spalling Repairs ongoing through 2022.**Alii Villas 107 will be a building over from spalling work April-June 2022
Alii Villas 107. Come and join us in paradise in this 1 bedroom, 1 bathroom and enjoy the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
New to the rental market!
Lovely ground floor garden one bedroom unit surrounded by lush greenery, birds and flowers galore. Alii villas#106 is a short walk down to the ocean seawall. Private lanai area to enjoy sitting outside day or night. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
Newly released!
Alii Villas #242 is a lovely one bedroom/ one bath condo located within steps to the ocean. Kitchen and bath have been recently updated. Comfortably furnished with portable AC unit in the living room and the bedroom. Free Wifi ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
Brand new to the rental market! Totally updated top floor condo with an ocean view! #322 boasts a mix of Tropical and Balinese style furnishings. This is not your typical one bedroom unit in Alii Villas. Fully equipped Kitchen, separate laundry ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
