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 '%Hapuna Beach%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Aloha nui Loa! Minutes from Mauna Kea and Hapuna beaches, the Red 'Ohana 2 bedroom is located in Waimea is a quaint town with breezes and milder temperatures that provide an idyllic Hawaiian environment.
The ultimate in comfort and ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kamuela
Aloha! The Red 'Ohana 3 bedroom is located on the beautifully scenic Kohala Mountain Ranch Road on the 'sunny side' of Waimea/Kamuela, above Mauna Kea and Hapuna Beach. Waimea is a quaint town with breezes and milder temperatures that provide an...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kamuela
This extraordinary, oceanfront, newly-constructed, third level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a magnificent chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
This unrivalled, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a magnificent chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
Relax in the luxurious comfort and privacy of this oceanfront, deluxe studio penthouse, just steps from the white sands of world famous Hapuna Beach. Enjoy spectacular sunset ocean views from the unique, four floor-to-ceiling radius glass ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> KAMUELA
This smart and chic, oceanfront, newly-constructed, second level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAMUELA
This beguiling, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an exquisite chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kamuela
This divine, oceanfront, newly-constructed, fourth level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a professional-grade chef’s kitchen outfitted with Wolf, Sub-Zero and Miele appliances, and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This incomparable, oceanfront, newly-constructed, ground level penthouse offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a superb chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> KAMUELA
Enjoy effortless beachfront living at B32, the premier oceanfront 1-bedroom at the newly constructed Hapuna Beach Residences. B32 is located on the third floor and is the largest 1-bedroom available (882 sqft) with bonus square footage and an ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kamuela
This stylish, oceanfront, newly-constructed, third level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This marvelous, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a luxe chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
Special 20% Off posted rate for a 7 night rentals now til Oct 2024
Shorter Stays possible with a 20% discount please contact us for details
Rare opportunity to rent an oceanfront condominium within the iconic Mauna Kea Resort. ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kamuela
This immaculate, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek, epicurean kitchen with Wolf and Sub-Zero appliances, wine storage and hand-crafted ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This magnificent, oceanfront, newlyconstructed, island masterpiece offers floortoceiling window walls that create indooroutdoor living space, a sleek, gourmet kitchen with Wolf and SubZero appliances, double ovens, a cappuccino machine, wine ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
New to market, A26 is a modern two-bedroom condo at the Hapuna Beach Residences offering the best beachfront location in Hawaii! Completed in 2022, this luxurious residence provides guests a front-row seat to gorgeous sunrises and the pristine ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kamuela
This superb, oceanfront, newlyconstructed, island masterpiece showcases floortoceiling sliding window walls that create indooroutdoor living space, a magnificent chefs kitchen outfitted with professionalgrade Wolf, SubZero and Miele appliances, ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Enjoy unobstructed beachfront views in this modern, luxurious two-bedroom unit decorated with Restoration Hardware furniture throughout. Overlooking the white sandy beach, the living and master bedrooms feature floor-to-ceiling sliding glass ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Breathtaking oceanfront penthouse on Hawaii's picturesque Hapuna Beach! This modern vacation home in the newly-constructed Hapuna Beach Residences features a coveted floorplan, with triple ocean frontage on the penthouse level where one can take...
- 3 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kamuela
Stylish, island-modern residence housed within the renowned Mauna Kea Resort area overlooking the beautiful Hapuna Beach. Just steps to the ocean as well as amenities to allow for easy access to play, dine, unwind, and connect. Available access ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
