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 '%Sugar Beach Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Welcome to our home, Sugar Beach 311. This is a non-smoking, professionally cleaned, recently renovated and updated one bedroom condo. It is an immaculately maintained and spacious one bedroom, third floor corner unit, with front and side views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This unit is remodeled and ready for you to call home for your stay! Walk down the stairs and onto the longest walking beach in Maui! Heading South on the beach, you are a 5 minute walk to delicious Sugar Beach Bakery and Ululani Shave Ice. Head...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
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
Hawaii> Kihei
Our ocean front condo has unobstructed views of the ocean with the islands of Kahoolawe and Molokini in the distance. SBR#226 is in the middle of the second floor of Plantation Building at Sugar Beach. The Resort has large, free, off-street ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Are you ready for some beach time? Maybe some 'down time?' Our A+ rated and recently updated, ground floor, oceanfront unit, is where you will want to spend your Hawaiian Vacation in Paradise. Call or e-mail now to ask questions and get ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Book with confidence - Sugar Beach is zoned as a Resort and won't be subject to any legislation that might change its usage.
*Note: September 9th - 16th 2024 and August 8th - 14th 2025 discounted $50/nt. due to pool closure.
Unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Ocean Front retreat with a cool ocean breeze and coastal decor. You will feel relaxed from the moment you enter this one-bedroom ocean-front condo. The bedroom has a king bed, allowing this condo to accommodate up to two guests. The kitchen is ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Welcome to Sugar Beach Resort #PH27– Your Beachfront Paradise!
Experience the serene beauty of North Kihei at Sugar Beach Resort, a picturesque beachfront condominium resort offering breathtaking Pacific Ocean views. Two six-story ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
Sugar Beach Resort #PH-14 is a penthouse suite towered on the sixth floor and located on the front row, inner-end location of the complex. From the oversized balcony, you are just a stones-throw away from the water. This panoramic, 180 degree ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Wake up each morning to the sights and sounds of the waves gently hitting the shore as you sit on the private lanai looking at the oceanfront view.
Sugar Beach Resort is a beachfront property, no roads to cross to get to the beach, just ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Welcome to Sugar Beach 518. This updated oceanfront 1 bedroom, 1 bathroom condo can accommodate 4 people. Enjoy ocean views throughout this unit and from your private lanai. Feel right at home with everything from central AC to in unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to Sugar Beach 534. This coastal style oceanfront 1 bedroom, 1 bathroom condo can accommodate 4 people. Enjoy ocean views throughout this unit & from your private lanai. Feel right at home with everything from central AC, to in unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 508
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Sugar Beach Resort 136
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort #PH-29 is a penthouse suite towered on the sixth floor and located on the front row, inner-end location of the complex. From the balcony, you are just a stones-throw away from the water. This direct ocean view is perfect for ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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. ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to Sugar Beach Resort Penthouse 34, one of the best ocean view condos in Maui. The top floor lanai is very private and looks out on endless ocean and 5 miles of white sandy beach - perfect for peaceful morning or evening walks. Watch ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
The condo is on the 5th floor of the Plant ation building #532. It is a roomy 700 square feet with full designer kitchen, living room, lanai, bathroom, and bedroom, with its king size Serta iComfort memory foam mattress. You will enjoy the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Come experience Paradise! 1 Bedroom Condo right at the water's edge. Perfect for rgose who want to get away from it all, this condo has everything you need. The living room and kitchen have full ocean views. This perfect piece of Paradise ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This fully remodeled 1bd/1ba penthouse (PH 25 on the 6th floor) in the Mill building at Sugar Beach Resort is beachfront with direct ocean views as soon as you walk in the door. You can whale watch from the lanai or take an easy elevator ride to...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 204
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 225
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to our home, Sugar Beach Resort 440. Our condo is a non-smoking, recently updated and professionally cleaned one bedroom, 4th floor, ocean front, end-unit with a side window. 440 is one of few units in Sugar Beach that offers views of...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Beach Front Sugar Beach Resort. 3rd Floor, Ocean View Unit. Beautifully updated unit with sweeping ocean views. Enjoy all of the amenities in a clean, well-kept property. The entire complex sits ocean front, there are no roads to cross to get ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Spectacular Ocean Front views from the top floor at the Sugar Beach Resort! Modern designs and contemporary furnishings complement this completely remodeled condo. You will fall in love with this condo and the amazing Ocean Front ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KIHEI
Beach Front Sugar Beach Resort, Ground Floor, Ocean View Unit. We recently remodeled this unit and it now represents outstanding quality. Enjoy all of the amenities in a clean, well kept property. The entire complex sits ocean front, there ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Fantastic ocean front view, this beach front condo features new flooring, a very comfortable California king-size bed, queen size sofa bed, central AC, and an entertainment center with digital HD flat-screen TV & surround sound, and free WiFi. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 523
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...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 526
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort PH13
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...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort PH02
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...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 214
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Unit 115 comfortably sleeps 4 - King size bed in bedroom and queen hide abed in living room. TV is located in the living room . Fully furnished unit including beach towels and beach chairs. Views of Haleakala and West Maui Mountains from your ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort #PH-40 is a penthouse suite towered on the sixth floor and located on the front row, southern-end location of the complex. From the oversized balcony, you are just a stones-throw away from the water. This panoramic, 180 degree...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Sugar Beach Resort 140
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Beach Front Sugar Beach Resort. Top floor, Ocean View Unit. Beautifully updated unit with sweeping ocean views. Enjoy all of the amenities in a clean, well kept property. The entire complex sits ocean front, there are no roads to cross to get ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
FULLY REMODELED, NEW APPLIANCES, NEW LINENS/TOWELS, NEW 55 inch TV, NEW FURNITURE AND MUCH MORE!!!
Amazing ocean view beach front condo that captures the magic of Maui with contemporary elegant style situated on popular Sugar Beach! Take ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
* Please note: The pool, hot tub and pool deck will be closed in September, for one week, starting the Monday after Labour Day/ the 2nd Monday of September, from Sept 9 - 15, 2024 * As well, the living room pull out couch was replaced with a ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 431
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Sugar Beach Resort 106
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 405
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 109
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 119
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Sugar Beach Resort 507
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Sugar Beach Resort 424
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...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
