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 = '96815' OR postalCode = '96815' OR region like '%|96815|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Top-of-the-line modern, high-end designer remodel. The direct ocean view will greet you as soon as you open the door! This studio unit boasts a unique feature of separation doors that create a one bedroom atmosphere. The open floor plan sweeps ...
Registration Number: GE-170-984-8576-01, TA-170-984-8576-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Waikiki Sunset 2105 Paradise Awaits 1-bedroom Superior Suite with Incredible Views
World Famous Waikiki White Sandy Beaches only one block away, Incredible Views. After playing all day in the sun, you will be happy to come "home to Your...
Registration Number: GE-181-163-0080-01, TA-181-163-0080-01
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Foster Tower is located steps away from beautiful Waikiki Beach. This is a one-bedroom unit in a highly desirable building and location. Enjoy beautiful ocean views and immediate access to the beach for surfing, swimming, and snorkeling, ...
Registration Number: 260260140106, TA-069-363-0976-01, GE-069-363-0976-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Waikiki Shore #1302 – 2161 Kalia Rd.
(ALL ONLINE RESERVATIONS CONSIDERED REQUESTS SUBJECT TO APPROVAL.)
You'll enjoy spectacular ocean and Diamond Head views from this luxurious 2-bedroom, 2-bath condo on one of the uppermost, ...
Registration Number: GE-039-999-8976-01, 260040120125, TA-039-999-8976-01
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
Four Paddle #2311 – 2140 Kuhio Ave
(30-Night Minimum. ALL MONTHLY RESERVATIONS ARE SUBJECT TO APPROVAL.)
Located on the cool side of the building, you'll enjoy lovely Ala Wai Canal and Ko'olau Mountain views from this well-equipped ...
Registration Number: GE-173-535-8976-01, TA-173-535-8976-01, 260170570225
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Castle at Waikīkī Grand #1003 - Relax with mai tais on the sundeck with ocean and Diamond Head view. This stylish, upgraded 396sf studio features air conditioning, open kitchenette with granite countertop breakfast bar, WiFi, flat screen, DVD,...
Registration Number: TA-203-430-0928-01, GE-203-430-0928-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> HONOLULU
Updated in 2014, this deluxe one-bedroom one-bathroom suite with Pacific Ocean views has modern features that beach-goers will love! This bright and airy suite has a beachy décor, modern island furnishings, and Hawaiian-style artwork. You’ll ...
Registration Number: GE-066-371-9936-01, TA-066-371-9936-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> HONOLULU
Get comfy in this island-designed one-bedroom one-bathroom deluxe ocean view suite! You’ll love the wicker furniture, full-size kitchen, and enclosable bedroom with a King size bed. Watch the sun set over the Pacific Ocean from your private ...
Registration Number: GE-132-795-8016-01, TA-132-795-8016-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> HONOLULU
Enjoy your stay in this well-designed Waikīkī beach deluxe studio with views of the Pacific Ocean! You’ll love the charming wicker King-sized bed frame, modern furnishings, and granite-top kitchenette. This unit has everything you need for ...
Registration Number: GE-158-478-1312-01, TA-158-478-1312-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Your Waikīkī vacation begins in this deluxe one-bedroom one-bathroom ocean view suite, which was updated in 2015. You’ll love the kitchen, which has lovely wooden cabinetry, stainless steel appliances, and a view! You’ll also have indoor ...
Registration Number: GE-199-290-0608-01, TA-199-290-0608-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> HONOLULU
"This tastefully decorated island suite is fully equipped with everything you need to have a fantastic Waikīkī vacation. With a stellar location, you’ll enjoy gorgeous views of the Pacific Ocean and Waikīkī beach from your furnished, ...
Registration Number: TA-076-605-4400-01, GE-076-605-4400-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Though you’re just steps from the excitement of Waikīkī and the sparkling waters of Waikīkī Beach, this comfortable deluxe studio is quietly tucked away amid the tropical greenery of Fort DeRussy Park. Unit 314 includes a platform queen ...
Registration Number: TA-199-188-0704-01, GE-199-188-0704-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
This authentic Waikīkī beach deluxe ocean view studio is the perfect island getaway! You’ll love the unique décor, like the teal backsplash in your kitchenette, white wicker furniture, and the ocean-toned walls. This studio has a furnished ...
Registration Number: GE-176-531-4560-01, TA-176-531-4560-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> HONOLULU
Imagine your self on the beaches of Waikiki staying in a luxury condo. Hale Kuhio (Home on Kuhio) is the best 30 day Waikiki vacation rental hands down.
This property may not be rented for fewer than 30 consecutive days. Rental prices will ...
Registration Number: GE-104-002-4576-01, TA-104-002-4576-01, 260170570018
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
OUR GUIDELINES FOR COVID-19: OUR PROPERTY IS PROFESSIONALLY CLEANED AND SANITIZED AFTER EACH STAY, AS PER CDC CLEANING GUIDELINES FOR COVID-19. WE HAVE AT LEAST A 24 HOURS GAP BETWEEN THE CHECKOUT AND THE NEXT CHECK-IN FOR OUR PROPERTY.
I...
Registration Number: 260250050692, GE-085-643-4688-01, TA-085-643-4688-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Great condo, awesome view and it includes the best selection of free beach gear in Waikiki (If you use this, it will add up to significant savings. See below for details.)
One of the best ocean view apartments in Waikiki, ideally located ...
Registration Number: 260100020153, TA-027-478-0160-01, GE-027-478-0160-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Aloha from Midway Vacations! Our team of local hospitality experts is dedicated to turning your Hawaiian getaway into a reality. When you stay at one of our properties, you can count on a professionally cleaned and meticulously maintained ...
Registration Number: TA-174-630-5024-01, GE-174-630-5024-01, 260210160166
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Aloha from Midway Vacations! Our team of local hospitality experts is dedicated to turning your Hawaiian getaway into a reality. When you stay at one of our properties, you can count on a professionally cleaned and meticulously maintained ...
Registration Number: 260250050736, TA-088-191-6416-01, GE-088-191-6416-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Want to be steps from Waikiki beach, fitness venues, and shopping, while having the advantage of a well-equipped kitchen for healthy meals? We offer fully renovated, upscale studio with all the conveniences of home. Fitness and ...
Registration Number: GE-130-575-5648-01, TA-130-575-5648-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
This rental is on the 19th floor of the Ilikai Hotel with direct lagoon and ocean views. This 1 bedroom apartment has been remodeled into a large 500 sq ft studio and is gorgeous! High-end materials were used throughout the apartment. We used ...
Registration Number: GE-177-208-7296-01, 260100070792, TA-177-208-7296-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
