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, rewritten_description,
(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 '%Menehune Shores%') AND sleeps>=1 order by rating desc LIMIT 0, 50
UNDER NEW OWNERSHIP AND EXTENSIVELY REMODELED.
Menehune Shores 627 - oceanfront penthouse with breathtaking ocean views from both the living room and master bedroom. One of only two unique penthouses at the end of the building, it has 2 feet
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Nani Kai Escape is a 6th Floor Oceanfront Penthouse with breathtaking ocean views offering the perfect Hawaiian getaway. Maui is known for it's world-famous beaches so come check that off your bucket list and literally stay steps from the beach.
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
E Komo Mai to our oceanfront unit on the 4th floor with stunning ocean view at the Menehune Shores. Feel the ocean breeze, listen to the waves, and watch the whales (during winter time) and sunsets from our tranquil, oceanfront condo. From our
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Enjoy amazing sunsets from your private lanai, or the comfort of the reclining sofa in the living room. TVs in living room & bedroom.
Fully equipped kitchen with all major appliances, cookware, dining ware, and utensils. Take a stroll or find
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Forth floor 2 bed/2bath recently renovated condo, Menehune Shores oceanfront. Enjoy ocean and beach views from your balcony. The beach is just a few steps away from our condo. Enjoy the sunsets and sunrises. Our guests say the beach in front
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This beautifully updated coastal condo features 3 bedrooms, 2 bathrooms & a private lanai. The condo has a new split AC system & can accommodate up to 8 people. Admire breathtaking ocean views, sunsets, sailboats, kite surfing, & whale
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Menehune Shores #517
Experience the epitome of Hawaiian luxury at Menehune Shores 517, where the azure expanse of the Pacific Ocean stretches out before you, offering an unrivaled backdrop for your Maui escape. Nestled within the
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
Welcome to Menehune Shores #623, a beautifully remodeled penthouse unit completed in August 2016. With high ceilings and breathtaking views of the ocean and mountains, this top-floor condo offers the perfect blend of modern luxury and the
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Upgraded direct oceanview condo at Menehune Shores #306 with views, views, views! Enjoy watching the sunset, seasonal whale watching, outriggers, paddle-boarders and kite boarding from the comfort of your private oceanfront Lanai. Many
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Completely Remodeled Ocean Front two-bedroom condo located at the Menehune Shores in Kihei creates the perfect spot for your Maui getaway. The lanai is a great place to relax and enjoy the ocean views. The beach in front of the property is great
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Menehune Shores is an oceanfront property located in North Kihei, offering breathtaking views of the Pacific Ocean and easy access to the sandy beach. This comfortable and spacious condo is perfect for families or groups, with its proximity to a
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Welcome to Menehune Shores 606, a stunning oceanfront 2-bedroom, 2-bath condo in North Kihei, offering breathtaking views and direct access to the beach. Located on the top floor, this light and airy unit features remote-controlled split-system
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Beautiful third floor one bedroom condo at Menehune Shores in Kihei- If you like the water, this is as close as you can get The kitchen has been re-modeled and added new oak cabinets and stone countertops. It is well stocked for cooking at
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei Maui
When you come to Maui it’s all about the sun and surf, and that’s what you get when you stay at Menehune Shores 225. You have a beachfront view – no peek-a-boo or semi-view. At Menehune Shores 225 you can sit on your lanai and gaze
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Gorgeous Ocean Front views! This one-bedroom condo has spectacular Ocean Front views that are visible from most of the condo. Enjoy waking up with fabulous views of the ocean from the master bedroom. The fully-equipped kitchen has everything you
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
