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 '%Maalaea Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Enjoy this 2nd floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is tastefully decorated with the spirit of Aloha and has a portable a/c unit in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Wailuku
Enjoy this nicely remodeled 3rd floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is tastefully decorated with the spirit of Aloha and has 1 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Wailuku
Enjoy the beautiful seascapes, surrounding islands and lovely Hawaiian skies above meticulous, palm-lined grounds. Winter whale season from approx end of Nov to mid Apr. Catch the occasional view of resident turtles from the lanai. The peace ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Wailuku
This beautiful, upgraded, two-bedroom, two-bath condominium corner unit is 15 yards from the waters of Ma'alaea Bay with a spectacular view of Mount Haleakala, Molikini, and Kaho'lawe Island from the second floor lanai with southeast to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Maalaea
oceanfront central air 3 beds & sofa bed
Maalaea Kai condo is a two bedroom, two bath oceanfront condominium located in Maalaea village 70 hau oli st. This very quiet and peaceful village, only 15 minutes away from resort towns of Kihei and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Wailuku
