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, 30
- 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
1 Bedroom, 2 Bath, Sleeps 4
Maui Sunset B107 is a slice of paradise, with partial ocean and gorgeous garden views from the living room and lanai! Experience the convenience and ease of ground floor living with easy access to the pool, ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
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
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.
Second floor means easy access to tropical gardens and the ocean beyond. Quiet ...
- 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
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, ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
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
New Luxury Vinyl Planking floor just installed Spring 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.
Large ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
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
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.
Second floor means easy access to tropical ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei
The true spirit of aloha will long endure in your memories after your stay in this ground floor oceanfront Pohailani studio. Located just 15 feet from the ocean on the first floor of a two-story building, you will enjoy panoramic views of the ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahana
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. ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
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
Our privately owned condo is located in sunny Kihei at the Maui Sunset. With the traditional charm and grace of old Hawaii, the Maui Sunset is located on over 5 acres of oceanfront, with immaculately maintained grounds, including a 60 foot ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
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
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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei, Maui
