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, 30
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: TA-047-203-9424-01, 350020180000, GE-047-203-9424-01
4 bedroom
6 bathroom
Sleeps 9
Hawaii> Honolulu
Gated custom designed immaculate contemporary ocean view home with outdoor deck, covered lanai, unheated pool and heated jet spa in quiet Diamond Head Kahala cul-de-sac on Diamond Head Crater slope. Ocean and Koolau mountain views from every ... Registration Number: 131048062000, GE-098-016-0512-01, TA-098-016-0512-01
4 bedroom
4 bathroom
Sleeps 8
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: 330310160000, GE-107-728-0768-01, TA-107-728-0768-01
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: 350150260000, TA-038-170-4192-01, GE-038-170-4192-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
For discounted rates on Main house only, please inquire. 12 Night Minimum over the Christmas/New Years Holiday Season.Registration Number: 350060160000, GE-005-323-3664-01, TA-005-323-3664-01
7 bedroom
7 bathroom
Sleeps 14
Hawaii> Honolulu
**If you need a smaller trip duration that what is posted, please send us a direct inquiry with the group size and preferred stay length.**Registration Number: GE-007-362-3552-01,TA-007-362-3552-01,1350230020058
7 bedroom
6 bathroom
Sleeps 16
Hawaii> Honolulu
The Kahala Beach Condo Is Fully Renovated with Koa Wood Flooring Throughout. Registration Number: GE-177-124-3520-01, TA-177-124-3520-01, 350230020072
2 bedroom
2 bathroom
Sleeps 6
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: GE-053-250-7136-01, TA-053-250-7136-01, 1350030350000
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
Royal Kahala Estate is Oahu’s most luxurious vacation rental near the foot of Diamond Head. The exquisite home features seven bedrooms, eight full baths, and a half-bath, encompassing 14,000 square feet all within a private beachfront estate. ... Registration Number: 350020500000, GE-199-064-7808-01, TA-199-064-7808-01
8 bedroom
10 bathroom
Sleeps 16
Hawaii> Honolulu
Kahala Luxury LivingRegistration Number: 350100100000, TA-088-184-4224-01, 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: GE-065-534-9760-01, TA-065-534-9760-01, 350030310000
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
30 night minimum Registration Number: GE-108-852-8384-01, 13526023, TA-108-852-8384-01
0 bedroom
0 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 with a 7 day minimum stay. This beautiful and spacious ... Registration Number: TA-094-014-7712-01, 1350300320000, GE-151-279-8208-01
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, GE-188-470-4768-01, TA-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: TA-143-627-7248-01, GE-143-627-7248-01, 350230020174
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Honolulu
NEWLY RENOVATED!Registration Number: 310410210000, TA-096-182-0672-01, GE-096-182-0672-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
**If you need a smaller trip duration that what is posted, please send us a direct inquiry with the group size and preferred stay length.**Registration Number: GE-007-362-3552-01,TA-007-362-3552-01,1350230020058
7 bedroom
5 bathroom
Sleeps 16
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: 1350300320000, GE-094-014-7712-01, TA-094-014-7712-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
Registration Number: 350070050000, TA-153-997-3120-01, GE-153-997-3120-01
5 bedroom
7 bathroom
Sleeps 12
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
**If you need a smaller trip duration that what is posted, please send us a direct inquiry with the group size and preferred stay length.**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: TA-008-444-7232-01, GE-008-444-7232-01, 230360390448
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
Waikiki Sunset #2503 – 229 Paoakalani Ave.Registration Number: TA-159-961-1904-01, GE-159-961-1904-01, 260280110249
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
**If you need a smaller trip duration that what is posted, please send us a direct inquiry with the group size and preferred stay length.**Registration Number: GE-007-362-3552-01,TA-007-362-3552-01,1350230020058
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
This lower level beautifully 2 bedroom, 2 bath home, 980 Sq. ft is perfect for enjoying your Hawaii vacation. This is an attached 2 bedroom, 2 bath fully equipped home with its own separate entrance. This home comfortably sleeps 4 adults. There... Registration Number: GE-189-110-4768-01, 1280300620000, TA-189-110-4768-01
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
The Historic Gingerbread House is a secure and gated residence directly across the street from Kahala beach. The neighborhood is between Diamond Head, the Kahala Hotel and the Waialae Country Club where the world renowned Sony Open is held. Our ... Registration Number: 350040120000, TA-019-756-4416-01, GE-019-756-4416-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
~Breezy Spacious and Welcoming Vacation Home~Registration Number: 330310320000, TA-122-615-2448-01, GE-122-615-2448-01
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Honolulu
A 10-minute walk from Waikiki, discover your own private paradise nestled at the base of Diamond Head in Honolulu’s renowned Gold Coast. Built in 1926 and beautifully remodeled with modern luxuries, Hale Mahie is notable as an example of the ... Registration Number: TA-044-602-3680-01, GE-044-602-3680-01, 310330200000
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Honolulu
