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, 50
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
- 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 ...
- 2 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 ...
- 3 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....
- 3 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 ...
- 2 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 ...
- 2 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. ...
- 2 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 ...
- 2 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.
...
- 2 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 ...
- 2 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Spacious 2 bedroom, beachfront condo, recently renovated and beautifully decorated for a fresh, clean, beachy feel. Enter this luxurious open floorplan to hardwood flooring throughout and stunning ocean views from the living room AND kitchen. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Spectacular Ocean View Condo w/ Balcony, Pool/Hot Tub/Tennis, Free WiFi!
E Komo Mai! (Welcome) to the luxurious Maui Sunset 317A. Come stay in the sunniest part of Maui in this recently-updated two bedroom, two bathroom Kihei condo that is ...
- 2 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
Escape to the sunny shores of Kihei, Maui and experience the ultimate island getaway at our charming 1-bedroom condo. Nestled just a short 18-minute drive from the stunning Wailea Beach and 10 minutes from the pristine Kamaole Beach Park, you'll...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Maui Sunset 1032 S Kihei Rd - unit B109; Full kitchen and both bathrooms remodeled making the unit modern and sleek.
The unit has 1 queen bed in master bedroom, 1 queen sofa bed,1 murphy (single)bed in main living area. Beautiful, spotless ...
- 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 ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Ocean front condo with extra large private lanai offers a premium location to enjoy great views from the convenience and comfort of your condo. The spacious living area is light and bright, giving immediate ease and comfort when you step inside....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Maui Sunset offers the perfect Hawaiian getaway. This spacious two bedroom, two bath offers gorgeous views of the ocean and West Maui mountains.Experience a magical Maui sunset or peaceful morning on your private lanai. Perfectly located ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Maui Sunset #B-417 is a fourth floor 2 bed 2 bath unit with beautiful ocean views. It has a fully equipped kitchen, washer and dryer, window unit AC in both bedrooms as well as the living room, and ceiling fans throughout. In addition this unit ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Recently renovated in July of 2024, featuring new cabinets, vanities, countertops, appliances, fixtures, doors and painted throughout. Spend your vacation on the beach, at the beautiful Maui Sunset complex, where our lovely, one bedroom condo ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Summary:
Condo in Beautiful Oceanfront Resort - ground floor tastefully-updated with all new furniture (April 2022) 1-Bedroom 2-bath with King Bed, Queen Sleeper & Twin Murphy bed in LR. Family Friendly, sleeps up to 5, Full Kitchen, AC, ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Summary:
Oceanfront Resort Condo Suite - ground floor tastefully-updated with all new furniture (April 2022) 1-Bedroom 2-bath with King Bed, Queen Sleeper & Twin Murphy bed in LR. Family Friendly, sleeps up to 5, Full Kitchen, AC, ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Maui Sunset B309, great location in Kihei! This 1bedroom/2bathroom condo is the perfect Maui getaway! Enjoy peaks of the ocean and the beautifully manicured grounds from your private lanai.
As you enter Maui Sunset B309 you are welcomed ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Spectacular one bedroom, two bath ocean view ground floor condo. Newly renovated and new furniture and 85 inch smart TV. Equipped with a large oversized washer/dryer. Desirable stainless appliances for all of your kitchen needs. Open floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Maui Sunset #B-513 is a fifth floor 1 bed, 2 bath Beachfront Breathtaking view and a freshly renovated Condo. Maui Sunset #B-513 has a fully equipped kitchen with granite countertops, all stainless appliances include an induction/convection ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Nice one bedroom, two bath partial ocean view ground floor condo. Relax and enjoy the outdoors right off your lanai. Located next to Waipuilani Beach, which is a long sand and coral beach protected by reef and suitable for swimming, long walks,...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Summary:
Oceanfront Resort Condo Suite - ground floor tastefully-remodeled (2020) 1-Bedroom with King Bed, 2-bath w/ Queen Sleeper & Queen Murphy bed in LR. Family Friendly, sleeps up to 5, Full Kitchen, AC, Washer/Dryer in-suite, Free WIFI &...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Maui Sunset A-509 – 1BR 2BA Oceanfront Condo with a Whale-Watching Lanai
There’s a reason many of our lovely guests return year after year to our oceanfront Maui condo.
Actually, there are lots of reasons.
The condo community’s...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Wonderful, breathtaking views in this 3 bedroom, 3 bath condo. Owner's put the touch of Hawaiiana w/ mango cabinets & doors and new tile floors thru out. 2 complete Primary bedroom suites with Cal King beds and 3rd bedroom with 2 twin beds + ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kihei
Ocean front! Ground Floor! B-122 is the closest 2 bedroom to the ocean. Best location and your living area is the 10 acres of manicured lawns and gardens just steps from the large "L" shaped swimming pool of Maui Sunset.
Large ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Maui Sunset is the perfect vacation home on Maui. Your island days can be filled with recreation and fun. Swim in Maui Sunset's beautiful pool with a shallow end for small children or just relax on a chaise lounge by the pool or in the jet ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Maui, Kihei
Maui Sunset is located on the ocean in sunny Kihei on 5.5 beautifully landscaped acres overlooking crystal blue pacific waters. Your island days can be filled with recreation and fun. Swim in Maui Sunset's beautiful pool with a shallow end for...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This 3rd floor condo is the perfect spot to base your Maui vacation. Casual Hawaiian themed decor with all the comforts of home. Spend your vacation as busy -- or as relaxed -- as you'd like. You can explore the island or just stay in and enjoy ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kihei
A310 is a beautiful 1 bedroom, 2 bath oceanview condo at Maui Sunset Resort which is the perfect vacation home. The resort is oceanfront and has two tennis courts, a large swimming pool, hot tub, a shuffleboard court, putting green, and ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Maui Sunset is the perfect vacation home on Maui. Your island days can be filled with recreation and fun. Swim in Maui Sunset¹s beautiful pool with a shallow end for small children or enjoy relaxing in the jet spa. This lovely resort also ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
5th floor ocean view, quite location! Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck. New flooring throughout 2023
Large private condominium apartment at the resort ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
From on the fifth 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.
Large private condominium apartment at the resort ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Watch the life of Maui unfold from the morning till evening from your private extra-large lanai. A319 Two-bedroom two bath sleeps up to seven people. Twin size Murphy bed and queen pull our sofa in the living room. New floors throughout 2023. ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei
Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck. New flooring throughout 2023
Second floor means easy access to tropical gardens and the ocean beyond. Quiet location features large ...
- 3 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. Premium condo suite located near the pool area on the fourth floor View of the ocean and tropical gardens. New floors throughout 2023....
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Fourth floor ocean view in the "B" building. Watch the life of Maui unfold from the morning till evening from your private 114 sq foot private view deck. New flooring throughout 2023 Premium condo suite located near the pool area. ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei
New custom renovations just competed, perfect for a family or a couple. Condo B104 at the Maui Sunset.
Picture yourself on the ocean front in sunny south Maui. Kihei is well known for its laid-back atmosphere, world famous beaches, ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
