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
- 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
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 condominium complex on the...
- 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
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 condominium complex on the...
- 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
Watch the life of Maui unfold from the morning till evening from your private extra large 180 sq foot private view deck. Premium condo suite located near the pool area.
Large private condominium apartment at the resort condominium ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
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. ...
- 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
