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 '%Sugar Beach Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Sugar Beach Resort is located north of downtown Kihei on beautiful Sugar Beach. Sugar Beach stretches for miles on Maui's Maalaea Bay. Stay in our Hawaiian condo and it will be a vacation you will never forget. Sugar Beach Resort is just a 15 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
A special corner condo with Ocean Views. This condo is located at the end of the building, so it has an extra window in the living area, allowing more natural light to flood in. This bright and open condo is the perfect space to relax.
This ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Adventurous Beachside Condo with Easy Beach Access
Literally only a few steps to the beach where you can feel the sand between your toes before dipping them into the warm Pacific Ocean. This ground floor condo is the perfect location for your ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Bask in old Hawaiian luxury with this remodeled condo on Sugar Beach. You will enjoy lots of space to unwind and relax in this homey condo right on the water.
Beautifully renovated Ocean Front Condo located on the fourth floor. This one ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Enjoy spectacular Ocean Front views from the private lanai and a living area of this one-bedroom condo. There is a fully equipped kitchen that is perfect for making meals to dine-in or to take with you while on the go. The bedroom has a king ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> KIHEI
Relax and enjoy the tranquility of Maui from with breathtaking views from your private lanai.
This one bedroom unit features a King Size bed, TV's, full kitchen, floor Safe and a Washer/Dryer. Provided are: bed linens, bath and beach towels ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Sugar Beach Resort 536
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Sugar Beach Resort 415
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Sugar Beach Resort 304
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Sugar Beach Resort 231
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Sugar Beach Resort 309
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Sugar Beach Resort 505
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kihei
Sugar Beach Resort 513
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
One of the nicest one bedroom oceanfront condos at Sugar Beach Resort.
Completely updated with modern kitchen, washer/dryer, central air conditioning and private lanai. Renovated bathroom, new flooring and modern paint scheme are just a few of...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Sugar Beach Resort 230
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Join the many guests who return each year to Maui's Sugar Beach Resort to enjoy the...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
