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 '%Maui Sunset%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Maui Sunset is a beautiful oceanfront resort and is the perfect vacation home on Maui. Swim in the large swimming pool or just enjoy sitting on a chaise lounge by the pool or in the jet spa. It is especially beautiful in the evening relaxing...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
- Tastefully appointed, ground-floor, oceanfront condo.
- Breathtaking, unobstructed sunset views!
- Manicured park grounds directly accessed from the unit's lanai.
- Tropical landscaping and outdoor pool with an activity ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
- Updated ground-floor, oceanfront condo.
- Breathtaking, unobstructed sunset views!
- Manicured park grounds directly accessed from the unit's lanai.
- Tropical landscaping and outdoor pool with an activity desk for booking...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
- Updated, ground-floor, oceanfront condo.
- Breathtaking, unobstructed sunset views!
- Manicured park grounds directly accessed from the unit's lanai.
- Tropical landscaping and outdoor pool with an activity desk for ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
New floors throughout 2023. Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck. Premium condo suite located near the pool area. Maui Sunset A111, ground floor paradise
Ground ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Located near the pool area, easy access to BBQ's, pool and beach. New floors through 2023, new sleeper couch 2022! Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private patio.
Condo has a ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Watch the life of Maui unfold from the morning till evening from your private view deck. Premium condo suite located near the pool area.
Quiet location features large private view deck and renovated interior. Doesn't get any better....
- 2 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei
Ground floor means easy access to tropical gardens and the ocean beyond. Quiet location features large private view deck and renovated interior. New Flooring throughout 2023. Doesn't get any better.
Large private condominium ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Condo rental on the beach in sunny Maui, Hawaii. Condo A415 has one of the best locations for views of the ocean and Maui vistas.
Great location, one bedroom two bath ocean view. Watch the life of Maui unfold from the morning till ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Top floor, best location of any 1 bedroom #A516
Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck. Premium condo suite located near the pool area. New flooring throughout 2023. ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck. Located on the fourth-floor view of tropical gardens and the ocean beyond. Quiet location features large private view deck and ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
One of the best locations in the "B" building on the 4th floor near the pool. View of the ocean and West Maui Mountains. Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck.
...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Your own cottage in Kaanapali with beautiful sunsets and ocean views. Visitors to Maui are being welcomed back. Come and explore beautiful areas including Kaanapali, Napili Bay and Kapalua as well as the many small towns and sights the entire ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Island Vacation at its finest! This partial ocean view condo is designed for comfort. Partial Ocean view, the unit has been nicely designed and features tile flooring, granite kitchen counters, stainless steel appliances and more. A large lanai ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Clean, private, updated, one-bedroom condo perfect for couples and families. Condo has additional queen-size Murphy bed.
Located in the desirable Maui Sunset complex in Kihei, on the leeward, sunny side of Maui. The condo comes fully ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
A stunning oceanfront sunset condo in Kihei! This updated top unit 2-bdr, 2-bath with AC offers stunning sunsets from manicured grounds. Step onto the private lanai to unwind and enjoy panoramic ocean views! Take a dip in the pool or stroll to ...
- 2 bedroom
- 4 bathroom
- Sleeps 5
Hawaii> Kihei
Maui Sunset #A-103 is a ground floor 1 bed 2 bath unit with great garden views, a peek-a-boo ocean view, fully equipped kitchen, washer and dryer, window unit air conditioning in both master bedroom and living room, and ceiling fans throughout....
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
MAUI SUNSET IS A FABULOUS OCEAN FRONT PROPERTY. Has a community swimming pool, heated during winter months, hot tub, gym, tennis courts, putting green, barbecue grills.
Just STEPS TO THE BEACH allows for WINDSURF, WHALE WATCHING in season, ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Lovely, Kihei Maui condo, just steps from the sand. Ocean side of road, one bedroom, two bath condo. Enjoy the ocean view from the lanai of this 4th floor condo and all the amenities of the highly acclaimed Maui Sunset resort, including swimming...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Beautiful, spacious, Kihei Maui ocean side of road, two bedroom, two bath condo. Maui Sunset, unit B201. Top quality remodel in 2015 with granite counter, custom kitchen and bathroom vanities. Enjoy the garden view and all the amenities of the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
