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, 50
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 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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
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 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 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 Lauloa 410, a stunning top-floor retreat offering unparalleled views of Maalaea Bay, the majestic Haleakala volcano, and the twinkling Kihei lights at night. From your private lanai, take in the soothing sounds of the ocean and the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
