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, rewritten_description,
(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
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
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
