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: 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
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
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: 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
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: 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 with a 5 day minimum stay. This beautiful and spacious ... 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
NEWLY RENOVATED!Registration Number: 310410210000, TA-096-182-0672-01, GE-096-182-0672-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
UNPARALLELED VIEWS! 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: GE-094-014-7712-01, TA-094-014-7712-01, 1350300320000
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
FORGET YOUR WORRIES IN THIS SPACIOUS AND SERENE SPACE. THIS MODERN RESIDENCE IS NEWLY RENOVATED AND EXTREMELY CLEAN. LOCATED ON THE BEAUTIFUL KAHALA BEACH. UNIT OFFERS PRIVATE OUTDOOR PATIO WITH SEATING AND DINING TABLE TO HELP YOU ENJOY THE ... Registration Number: TA-058-184-0896-01, 350230020118, GE-058-184-0896-02
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience unparalleled luxury in this Oahu gem, perfectly positioned between the esteemed Waialae Country Club and the illustrious Kahala Hotel. Just a whisper away from the beach, this condo offers an intimate escape from Waikiki's fervor, yet... Registration Number: TA-130-276-7104-01, GE-130-276-7104-01, 350230020018
2 bedroom
2 bathroom
Sleeps 5
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
UNPARALLELED VIEWS! Registration Number: GE-007-362-3552-01, TA-007-362-3552-01, 1350230020058
4 bedroom
3 bathroom
Sleeps 10
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: 1280300620000, TA-189-110-4768-01, GE-189-110-4768-01
2 bedroom
2 bathroom
Sleeps 5
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
Welcome to the Bird of Paradise, similar to a boutique hotel experience in the Diamond Head neighborhood. This Honolulu vacation rental is a short drive from Waikiki, International Marketplace, Ala Moana Shopping Center, Waikiki ZOO, and many ... Registration Number: TA-188-577-3824-01, GE-188-577-3824-01, 350140350000
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
