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 '%Lauloa%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This nicely updated top 4th-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and spacious ensuite ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This nicely remodeled 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This updated and spacious 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy Cal king bed and ensuite bathroom...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
This updated 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite offers a comfy king bed and ensuite bathroom with a walk-in ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
This updated 2nd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a walk-in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Two bedroom-two bath condo on second floor of Lauloa. Walk to end of block and visit the Maui Ocean Center, several restaurants and shops, or take a cruise from the harbor with the Pacific Whale Foundation.
At the other end of the block you ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This updated 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a walk-in ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Maalaea
This nicely updated top 4th-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and spacious ensuite ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
This spacious and quiet 3rd-floor condo just steps from the ocean offers three bedrooms and stunning panoramic views of the Pacific and southwest Maui with brilliant sunrises over the Haleakala volcano each morning. The primary suite has a comfy...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Maalaea
Welcome to your dream Maui getaway! This highly updated 2-bedroom, 2-bath condo in Maalaea offers the perfect blend of modern comfort and breathtaking oceanfront views making it an ideal haven for families or friends seeking an unforgettable ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
