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 (`headline` like '%Waikiki Beach Tower%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Waikiki Beach Tower
Relax in this exclusive upper floor unit at the most luxurious vacation rental tower on the beach in Waikiki. This 1,200 SF newly renovated condo has the only open floor plan in the entire building and its sky high ...
Registration Number: 260030070083, TA-118-133-4016-01, GE-118-133-4016-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Relax in this beautifully renovated unit with ocean views at Waikiki’s finest oceanfront condo building. Wake up to the crystal blue ocean from the luxurious king bed in the master bedroom, entertain family and friends in the open kitchen ...
Registration Number: TA-013-407-1808-01, 260030070029, GE-013-407-1808-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
Feb 10: Association of Waikiki Beach Tower 4th floor deck renovation through May 2025. During this period there will be some noise disruption from 9:00 ...
Registration Number: TA-169-845-9648-01, GE-169-845-9648-01, STR # 1826, 260230070034
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Summary:
Located in the heart of Waikiki, this 2 Bed 2 Bath Luxury Oceanfront Suite is located in the Aston Waikiki Beach Tower Hotel! This property features floor to ceiling windows which open to a large private balcony that overlooks ...
Registration Number: 260230070019, TA-086-342-6560-01, GE-086-342-6560-01
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Honolulu
Visit https://www.youtube.com/watch?v=CVPLLGUG9Jw for a video walk through of this royal penthouse suite. Truly a WOW Top floor Penthouse!
Soar above Waikiki with a 2 bed, 2 bath, 1,263 sq ft suite, featuring stunning coastline, sandy ...
Registration Number: TA-058-201-7024-01, 260230070142, GE-058-201-7024-01
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Honolulu
Aston Waikiki Beach Tower2 bedroom unit Sleeps 6
***PLEASE DO NOT PUT IN A RESERVATION REQUEST USING THE “REQUEST TO BOOK” FEATURE***
Book our Amazing Luxury Villa & let The Timeshare Brokers, LLC make the booking process ...
Registration Number: TA-162-883-7888-01, 260230070000, GE-199-896-9344-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Experience the ultimate in beachfront luxury with this 2-bedroom, 2-bath condo that offers stunning ocean views and comes with complimentary valet parking. Situated just a few footsteps away from the beach, you'll be in the most vibrant ...
Registration Number: TA-170-708-6336-01, GE-170-708-6336-01, 260230070003, STR#1780
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
Please note: The 4th floor lower deck will be under renovation through May 2025. The upper deck, pool, hot tub, BBQ grills, cabanas, yoga rooms, fitness rooms, and hospitality lounge will remain open during this process. Work hours are ...
Registration Number: GE-103-524-9152-01, TA-103-524-9152-01, 1260230070008, STR#2143
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Honolulu
