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 (registration_number like 'TA-%') AND (city = '96825' OR postalCode = '96825' OR region like '%|96825|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
There’s no better way to experience Oahu’s south shore than from this charming Honolulu vacation rental home. Ideal for a romantic retreat, this 1-bedroom, 1-bath home for 2 is a short stroll from the scenic Koko Crater trail system, 1 mile ...
Registration Number: TA-038-936-9856-01, GE-03
1 bedroom
1 bathroom
Sleeps 2
HI> Honolulu
Open floor plan, over 1500 sq. ft., filled w/ imported Chinese furniture, beautiful ocean, other islands, mountain views, full size decks - master bedroom & dining room, full size kitchen w/ appliances, house sits back from the road - private, ...
Registration Number: TA-174-234-4192-01, 13907
1 bedroom
2 bathroom
Sleeps 4
HI> Honolulu
Villa Luana (meaning happy or content) provides an ideal setting for relaxing in blissful comfort. With several living spaces, a gourmet kitchen, and a large dining area, HIGH SPEED WIFI, this elegant home perfectly accommodates everything from ...
Registration Number: TA-192-269-3632-01, 39081
6 bedroom
7 bathroom
Sleeps 14
HI> Honolulu
A mini resort tucked away in an upscale and peaceful neighborhood, this Villa is absolutely one of a kind! The entire Villa has just been exquisitely renovated with a swimming pool, Bali house, and waterfall. The landscaping is so immaculate, ...
Registration Number: TA-088-128-9216-01, GE-08
4 bedroom
4 bathroom
Sleeps 16
HI> Honolulu
Enjoy your own waterfront slice of Hawaii at Ohana Kai! Located in a gated community along Hawaii Kai’s stunning waterway, you’ll enjoy marina and Koko Head views from every room. Unwind in the casual charm of this peaceful paradise with its...
Registration Number: TA-177-601-5360-01
3 bedroom
3 bathroom
Sleeps 8
HI> Honolulu
**To see (50+) 5-star reviews for this property, please search listing ID 969030.
This home SLEEPS A MAXIMUM OF 6 PEOPLE including children.
Please provide names of all registered guests so our property manager can check you in. Unregistered ...
Registration Number: TA-098-033-4592-01, 39092
4 bedroom
3 bathroom
Sleeps 6
HI> Honolulu
Discover one of Oahu's luxury vacation rentals with its gorgeous private pool terrace overlooking the Pacific Ocean and Diamond Head, Aloha Nalu (meaning “warm, welcoming sea”) will surely sweep you into the spirit of aloha. The villa sits ...
Registration Number: TA-019-941-5808-01, GE-01
4 bedroom
4 bathroom
Sleeps 10
HI> Honolulu
This property is a legally established dwelling rented for periods of 30 consecutive days or more at any one time. Contact owner for more details.
A Rare Private House in Hawaii! This family-friendly house is 'On the Water' with boat dock, ...
Registration Number: TA-071-856-5376-01, 39032
4 bedroom
3 bathroom
Sleeps 10
HI> Honolulu
Set on the slopes of Koko Kai, this spacious, two-story, family-style home offers panoramic ocean views and breathtaking sunset over Diamond Head. Decorated with a touch of island decor, this villa is perfect for indoor/outdoor living. The large...
Registration Number: TA-137-893-8880-01, GE-13
3 bedroom
3 bathroom
Sleeps 10
HI> Honolulu
True Hawaiiana at its best! Luxury 3 bedroom 2.5 bath waterfront home with pool, spa, tiki bar, boat dock, two 60 inch flat screen TV's /DVD movie surround systems, Bose outdoor music near pool, granite, slate, and many more upgrade too numerous...
Registration Number: TA-092-645-7856-01, GE-09
3 bedroom
3 bathroom
Sleeps 8
HI> Honolulu
Located in the upscale neighborhood of Hawai’i Kai, Nani Wai is a 5-bedroom, 4-bathroom gated home located on a marina and with views of the majestic Koko Crater. This spacious home is ideal for larger groups, with lots of room to spread out ...
Registration Number: TA-211-784-0896-01, GE-21
5 bedroom
4 bathroom
Sleeps 13
HI> Honolulu
MONTHLY RATE - $9000
To be in compliance with local regulations, this property is rented for periods of 30 consecutive days or more.
Aloha and welcome!
This lovely home is located in Portlock, one of Honolulu's most prestigious areas. The ...
Registration Number: TA-065-699-8400-01, W0558
5 bedroom
4 bathroom
Sleeps 8
HI> Honolulu
Last Minute Spring Special - 15% OFF Rates!! Promotion applies to booking dates NOW until April 30th
This stunning property is now accepting longer-term stays at reduced rates. Please inquire for details.
Oahu's Newest Luxury EstateOcean House...
Registration Number: TA-199-064-7808-01, GE-19
6 bedroom
7 bathroom
Sleeps 12
HI> Honolulu
Amazing ocean, marina and mountain views.. Enjoy the relaxing resort lifestyle, great unit high floor, remodeled (2018). Designer decorated, new flooring, upgraded appliances, kitchen and bathroom. Resort like amenities include fitness center,...
Registration Number: TA-178-926-5920-01, GE-17
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
With breathtaking views of the beautiful Pacific Ocean, Koko Head, Hawaii Kai Marina, and Diamond Head, this mansion is perfectly situated above Kalama Valley. This 4BR, 4BA home is noteworthy for its impressive design elements including ...
Registration Number: TA-136-130-3552-01, GE-13
4 bedroom
4 bathroom
Sleeps 8
HI> Honolulu