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 '%hololani%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Stunning penthouse corner unit which sits ocean front on one of the most breathtaking Maui beaches in Kahana. Take the elevator or walk up the stairs to your 8th floor large sprawling unit that comfortably sleeps 6. Enjoy whale watching from the...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Hololani Resort A104 is a stunning resort condominium featuring spacious and elegant designs, offering panoramic views of the Pacific Ocean and the nearby islands of Molokai and Lanai. The condo comes equipped with all the modern amenities you ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
We invite you to stay in this beautiful oceanfront seascape with views of the Pacific Ocean and the neighboring islands of Molokai and Lanai. Relax and enjoy West Maui in Quam Properties Hololani B304. We look forward to hosting you!
This...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
"The area of this condo was unaffected by the recent Lahaina Fires. Your visit will help Maui heal and rebuild after the fires. West Maui, has come back to life. Restaurants and shops are opening. Our Beaches, waterfalls and hikes are ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Ocean views & Indoor/Outdoor living. This fully remodeled 2 bedroom, 2bathroom unit features ocean & Molokai island views from multiple rooms. Enjoy all the new high end finishes and furnishings throughout. This unit can accommodate 6 people. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Hololani B803
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Treat yourself to a tropical getaway you'll never forget when you book your next...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Stunning sea views greet you the moment you step into this 2 Bedroom, 2 Bathroom oceanfront suite in Hololani Resort. Enjoy the upgraded, fully equipped kitchen, in-unit washer & dryer, private lanai, free Wi-Fi, and complimentary assigned ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina