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 '%Milowai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Overview:
Marvel at the ocean and mountain views as you relax in this beautiful 4th floor, corner penthouse, oceanfront condo on Maui. Exit from your private elevator directly into the thoughtfully renovated and unique Dolphin Penthouse. Make...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Maalaea
This condo is a peaceful retreat where you can hear the ocean waves from your king size bed and queen size Italian sofa sleeper. The uniquely positioned south facing balcony over the ocean allows you to enjoy both sunrise and sunset views! ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Escape to paradise and experience the wonders of Ma'alaea Harbor in this exceptional 2-bedroom condo. With its array of features and stunning ocean views, your vacation here promises to be truly unforgettable.
Step onto the grand ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Enjoy breathtaking ocean views from this 4th/top floor unit. One of the largest lanais at the complex! The bedroom has a new comfy king bed, beautifully remodeled tiled walk-in shower, 40" Roku smart TV and a split system a/c unit to keep you ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Maalaea
A wonderful modern unit on Maalaea Bay with a beautiful ocean view that was recently renovated with all new kitchen, bathroom, floor surfaces and furniture.
The oceanfront pool is a wonderful place to spend the day followed by a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Enjoy breathtaking ocean views from this 3rd floor unit. The master provides a comfy California king bed with ensuite tub/shower and cable tv, while the guest bedroom has a comfy queen bed, cable tv and guest bathroom with updated tiled walk-in ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
