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 '%Island Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This rare ocean-front condo is stunning! Listen to the sound of the ocean from the comfort of your home, while watching whales (Dec-April) and Hawaiian Green Sea Turtles all year long!
Located a five minute walk from boats, Maui Ocean ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Location, location, location! Wake up to diamonds sparkling on the ocean top.
Spectacular top floor views of Makena, Molokini crater, and the Island of Kahoolawe. Whales in full view from our lanai during whale season, all while enjoying ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
With the ocean just a stones throw away, you will be amazed by the spectacular breathtaking panoramic views! Immaculate and upgraded this spacious 989 sf 2 bedroom, 2 bath, oceanfront condo is your oasis in paradise. Master bedroom and living...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Wailuku
New split system a/c cooling living room and both bedrooms! This 5th story unit offers gorgeous views of the ocean, Haleakala and peaceful Maalaea Bay that you can enjoy on your large, shady, private lanai which is accessible from the living ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
A wonderful just renovated 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
"Stay Longer, Save More: Enjoy Lower Rates on Your Ideal Vacation Rental".
Oceanfront Condo with Ocean and Mountain Views on Beautiful Ma`alaea Bay
Please review the property description and pictures carefully prior to booking, and...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Wailuku
"Stay Longer, Save More: Enjoy Lower Rates on Your Ideal Vacation Rental".
Please review the property description and pictures carefully prior to booking, and call/email if you have any questions. We want to ensure you have a wonderful ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Hawaii Tax ID: TA210-586-8288-01
TMK: 380140150061
Completely renovated beautiful oceanfront condo overlooking Maalaea Bay.
From your private lanai, enjoy the spectacular ocean views with Kahoolawe and Molokini in the backdrop. Or ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
A wonderful modern unit on Maalaea Bay with a beautiful ocean view. The unit was just totally renovated throughout to Five Star Quality with all new floor, bathroom, kitchen, bedroom, living room, and lanai surfaces and furniture. Pleae see the...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Cooling breeze throughout the condo in living room and both bedrooms! This 6th story unit offers gorgeous views of the ocean, Sea Turtle sanctuary, Haleakala and peaceful Maalaea Bay that you can enjoy on your large, shady, private lanai which ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This 5th story unit has gorgeous views of the ocean and west Maui mountains. Views of peaceful Maalaea Bay that you can enjoy on 2 separate private lanais which are accessible from the living room and master bedroom. NEW Split System AC ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Maalaea
Island Sands Resort 111
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This top floor corner unit offers amazing ocean views!! Enjoy radiant sunsets from your private lanai overlooking Ma'alaea Bay. Light and bright as you enter, this beautifully designed 2 bedroom, 2 bathroom unit features fresh paint and new wood...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Island Sands Resort 607
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Island Sands Resort 107
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Island Sands Resort 208
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Island Sands Resort 302
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
