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
UNPARALLELED VIEWS! Registration Number: GE-007-362-3552-01, TA-007-362-3552-01, 1350230020058
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
UNPARALLELED VIEWS! Registration Number: GE-007-362-3552-01, TA-007-362-3552-01, 1350230020058
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Honolulu
Upscale neighborhood of Kahala that is walking distance to Kahala Mall. It's about a One mile drive to the nearest beach, Kahala Hotel, Waialae Country club. 6-minute drive to the world famous Waikiki Beach, Diamond Head hiking, Rainbow's ... 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
5 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
UNPARALLELED VIEWS! Registration Number: GE-007-362-3552-01, TA-007-362-3552-01, 1350230020058
7 bedroom
6 bathroom
Sleeps 16
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
STUNNING new home with views of Diamond Head, the ocean and Waikiki. Super spacious of 1980 sq. ft., immaculate 3 bedrooms (4 BEDS! King bed & 3 queen beds) and 2 full bathrooms, open floor plan, huge and well equipped kitchen with cookware, ... Registration Number: TA-048-102-1952-01, GE-048-102-1952-01
3 bedroom
2 bathroom
Sleeps 4
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
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
0 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
Our Kahala Beach Apartment sits on one of the most valuable strips of beachfront in Hawaii. This Elegant apartment is Beachfront resort-style Living at its best. The Apartment is steps away from the 4 star Kahala Hotel with its Elegant dining ... Registration Number: GE-143-627-7248-01, TA-143-627-7248-01, 350230020174
3 bedroom
3 bathroom
Sleeps 6
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
