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 newly remodeled.
Located at Sugar Beach Resort, a popular destination on Maui, this direct oceanfront property offers spectacular views, and is centrally located, allowing for easy exploration of the island, seasonal ...
- 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 '24 discounted $50/nt. due to pool closure.
Unit #323, one of the nicest one ...
- 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
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
Sugar Beach #227 Maui Vacation Rental
Welcome to Sugar Beach Resort, a tranquil beachfront condominium retreat located in North Kihei, offering sweeping views of the Pacific Ocean. Our resort features two six-story buildings equipped with...
- 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
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 436
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 229
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 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
