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, 20
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 ...
- 1 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 ...
- 1 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
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 Resort #227– 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
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 ...
- 1 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 ...
- 1 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. ...
- 1 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
