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 (registration_number like '%TA-%' or registration_number like '%TA %') AND (city = '96816' OR postalCode = '96816' OR region like '%|96816|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Treat yourself to ultimate island luxury in this architecturally designed $5.5M home on 15,000 sq ft lot in one of the most desired locations on Oahu's south shore. Nestled on the slopes of Diamond Head just outside of Waikiki, this exquisite ... Registration Number: 350020180000, GE-047-203-9424-01, TA-047-203-9424-01
4 bedroom
6 bathroom
Sleeps 9
Hawaii> Honolulu
**Short Stays Welcome!** While local regulations prevent us from advertising rentals for less than 30 days, we're happy to accommodate shorter stays within a 30-day booking window. If you're looking for a stay under 30 days, please reach out to ... Registration Number: GE-123-456-7890-01, TA-123-456-7890-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
**Minimum 30 days required for reservation**Registration Number: 230360390448, TA-008-444-7232-01, GE-008-444-7232-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
Enjoy magnificent unobstructed views of the ocean, Diamondhead and Waikiki from this historic vacation home while living in a spacious, updated house with loads of amenities. Plan your day, drinking a mellow cup of Kona coffee on the lanai, ... Registration Number: GE-107-728-0768-01, TA-107-728-0768-01, 330310160000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
This home-away-from-home exudes island style and comfort! Open, airy Kahala Cottage features two charming living spaces, a nicely-appointed kitchen, and a dedicated dining area that allow guests to enjoy quality time together at home. Flexible ... Registration Number: TA-038-170-4192-01, GE-038-170-4192-01, 350150260000
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Registration Number: 350070050000, TA-153-997-3120-01, GE-153-997-3120-01
6 bedroom
7 bathroom
Sleeps 12
Hawaii> Honolulu
For discounted rates on Main house only, please inquire. 12 Night Minimum over the Christmas/New Years Holiday Season.Registration Number: GE-005-323-3664-01, TA-005-323-3664-01, 350060160000
7 bedroom
7 bathroom
Sleeps 14
Hawaii> Honolulu
Kahala Oceanside is a private paradise retreat that comfortably sleeps 14. This luxurious ocean front hideaway on the South Shore of Oahu, known for its spectacular sunsets, is just minutes away from fine eateries, world-famous hikes, and the ... Registration Number: 1350030350000, GE-053-250-7136-01, TA-053-250-7136-01
6 bedroom
7 bathroom
Sleeps 15
Hawaii> Honolulu
Let us welcome you to Ohana Paradise, a sophisticated, tropical retreat located in one of the most affluent and prestigious areas of Diamond Head-Kahala, world renowned for its beauty, beaches, parks, restaurants, and family fun. Leave the ... Registration Number: GE-203-504-0256-01, TA-203-504-0256-01
6 bedroom
5 bathroom
Sleeps 12
Hawaii> Honolulu
This spectacular French Tudor exudes a casual elegance and the comfort of the islands. Inside you will find gleaming wood floors, an elegant fireplace, crown molding and spacious bright rooms. The kitchen is fully equipped with a new ... Registration Number: TA-112-501-9648-01, GE-112-501-9648-01
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Prices start at $150,000/month and will not be pro-rated based on length of stay. Please inquire to get an accurate quote.Registration Number: TA-199-064-7808-01, GE-199-064-7808-01, 350020500000
8 bedroom
10 bathroom
Sleeps 16
Hawaii> Honolulu
Kahala Luxury LivingRegistration Number: TA-088-184-4224-01, 350100100000, GE-088-184-4224-01
5 bedroom
4 bathroom
Sleeps 12
Hawaii> Honolulu
Newly renovated 3 bedroom, 2.5 bath home on Maunalani Hts. Tastefully furnished with high quality bedroom, living room and dining room furniture. HD Smart T.V.s in living room, kitchen and bedroom. Fully functional kitchen with new dishes, ... Registration Number: TA-187-879-4240-02, 330180700000, GE-186-879-4240-01
0 bedroom
0 bathroom
Sleeps 6
Hawaii> Honolulu
Experience the laidback luxury and authentic island living of Kahala Breeze, a spacious four-bedroom home that features elegant decor and design cohesive with the natural beauty that surrounds it. Registration Number: 350030310000, TA-065-534-9760-01, GE-065-534-9760-01
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
30 night minimum Registration Number: TA-108-852-8384-01, GE-108-852-8384-01, 13526023
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
The perfect studio for a couple on their Hawaiian get-away who want to live like locals during their stay. Please inquire about your specific dates and we can try to accommodate your stay. This beautiful and spacious studio with 1 bathroom is ... Registration Number: TA-094-014-7712-01, GE-151-279-8208-01, 1350300320000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Kahala Seaside, a spacious two-story garden view home featuring four bedrooms, three-and-a-half bathrooms, and lush garden views. Nestled in the upscale seaside burg of Kahala, you’ll have access to all the fun and excitement of ... Registration Number: 350040510000, TA-188-470-4768-01, GE-188-470-4768-01
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Honolulu
* For less than 30 day stays, please send a email onlineweb- OceanViewOahuRegistration Number: GE-007-362-3552-01, TA-007-362-3552-01, 1350230020058
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Elegantly remodeled in 2018, this one level, very clean, 4 bedroom & 3 bath home, has 2 master suites, a gourmet kitchen, air conditioning in every bedroom & high end finishes throughout this private oasis. Please inquire about your specific ... Registration Number: GE-094-014-7712-01, TA-094-014-7712-01, 1350300320000
5 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
Diamond Head Ohana - 30-Night MinimumRegistration Number: 1310040400000, TA-087-083-2128-01, GE-087-083-2128-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
