select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) 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, 15
- 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 desk for booking ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
HI> Kihei
Welcome to Grand Champions 79! Located in the lush Wailea resort area, this 1 bedroom/two bath villa has been completely updated with new furnishings, a plush new King sized mattress and a fresh paint job. Once you step inside the ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Wailea-Makena
Never miss a sunset right from your 5th floor lanai! Outstanding location with one of Kihei's best ocean view accommodations in Kamaole Beach Royale. 222 steps from the elevator to Kam 1 beach, cafes, shops, general store and organic foods ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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, BBQs, ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
***The Mahana Resort's Lanai building has ongoing exterior spalling repair work that begins at 7am and ends no later than 5pm during the week. This includes the ocean front side of unit 214 through the first week of February and the mountain ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kaanapali
Great place to relax and unwind. Watch whales, turtles, and sea life – right from your own lanai! NO RESORT FEES!
Located between Lahaina and Kapalua. Direct oceanfront, you would have to be on a cruise ship to be any closer!
Our 2nd floor...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
- 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 tours, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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
HI> 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 tours.
- 2 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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, and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> 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
HI> 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
HI> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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
HI> 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
HI> Kihei
