select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 and (registration_number like 'TA-%') AND (`headline` like '%waikiki banyan%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Deluxe Ocean View Condo
Sleek and modern condo just a block from world famous Waikiki Beach!
Sleeping arrangements include two queen-sized beds in the bedroom and a sofa sleeper in the living room. The bathroom has a second door for your ...
Registration Number: TA-006-364-9792-01
1 bedroom
1 bathroom
Sleeps 6
HI> Honolulu
Waikiki Banyan 1812 Tropical Oasis Parking and WiFi
1.5 blocks to white sand Waikiki Beach. Air Conditioned spacious living room that leads to the Lanai to relax and enjoy the views of the Ocean, Diamond Head and the famous Waikiki Banyan ...
Registration Number: TA-163-424-8704-01
0 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
** PLEASE NOTE:
- The tennis court is temporally closed until further notice -
POOL CLOSE for Maintenance FROM May 8th to May 18th, 2022
**POOL OPENS May 19th at 8am
SPAS CLOSE for Maintenance May 9th to May 27th, 2022
** SPAS OPEN May 28th at...
Registration Number: TA-121-959-0656-01
1 bedroom
1 bathroom
Sleeps 5
HI> Honolulu
Deluxe Panoramic Mountain View Condo - 37th Floor, Free parking & Wifi
Enjoy paradise with an incredible mountain view Waikiki Banyan condo suite only one block from world famous Waikiki Beach! Condo suite is located on the 37th floor and has a...
Registration Number: TA-024-432-8448-01
1 bedroom
1 bathroom
Sleeps 6
HI> Honolulu
** PLEASE NOTE:
- The tennis court is temporally closed until further notice -
POOL CLOSE for Maintenance FROM May 8th to May 18th, 2022
**POOL OPENS May 19th at 8am
SPAS CLOSE for Maintenance May 9th to May 27th, 2022
** SPAS OPEN May 28th at...
Registration Number: TA-163-657-1648-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
Deluxe High level condo with Ocean View
Enjoy a fully-equipped kitchen and comfortable seating in the living room. Kitchen includes standard kitchen wares and there is plenty of counter space and an island with seating for four people.
...
Registration Number: TA-080-891-6992-01, NUC:
1 bedroom
1 bathroom
Sleeps 6
HI> Honolulu
** PLEASE NOTE:
- The tennis court is temporally closed until further notice -
POOL CLOSE for Maintenance FROM May 8th to May 18th, 2022
**POOL OPENS May 19th at 8am
SPAS CLOSE for Maintenance May 9th to May 27th, 2022
** SPAS OPEN May 28th at...
Registration Number: TA-088-191-6416-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
GET ID# GE-031-951-0528-01 TAT ID#: TA-031-951-0528-01
The Waikiki Banyan is located in the heart of East Waikiki and just 1 block away from the warm sands and azure waters of world famous Waikiki Beach. Just steps away are the Honolulu Zoo, ...
Registration Number: TA-031-951-0528-01, GE-03
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
Waikiki Banyan 1909 Amazing Views and Just Steps to the Beach
Beautiful Views of Ocean and Diamond Head, Relax on the Lanai with a tropical drink and enjoy this Beautiful view's and Recreation deck with Pool Loaded in Lounge Chairs, 2 ...
Registration Number: TA-068-957-1840-01
1 bedroom
1 bathroom
Sleeps 5
HI> Honolulu
** PLEASE NOTE:
- The tennis court is temporally closed until further notice -
POOL CLOSE for Maintenance FROM May 8th to May 18th, 2022
**POOL OPENS May 19th at 8am
SPAS CLOSE for Maintenance May 9th to May 27th, 2022
** SPAS OPEN May 28th ...
Registration Number: TA-202-106-0608-01
1 bedroom
1 bathroom
Sleeps 5
HI> Honolulu
Waikiki Banyan 912 Your Luxurious Escape to Paradise
Beautifully and Completely Remodel with New Comfortable Furnishings Clean and Fully Equipped for your stay in Paradise. You will Love the Air Conditioned 1-bedroom Suite with Two Queen beds....
Registration Number: TA-118-973-0304-01
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
** PLEASE NOTE:
- The tennis court is temporally closed until further notice -
POOL CLOSE for Maintenance FROM May 8th to May 18th, 2022
**POOL OPENS May 19th at 8am
SPAS CLOSE for Maintenance May 9th to May 27th, 2022
** SPAS OPEN May 28th at...
Registration Number: TA-072-451-9936-01
1 bedroom
1 bathroom
Sleeps 5
HI> Honolulu
The LoveRentals portfolio is comprised of curated, professionally managed vacation rental homes around the world. We only select professional property managers that meet our high standards. Book with peace of mind knowing that you will have the ...
Registration Number: TA-207-821-3120-01, GE-20
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
The Ultimate Waikiki Beach Unit!
Ideal ocean view Waikiki beach unit #1514 in the desirable Ocean Side Tower #1 is located just one block from the famous Waikiki Beach and has direct view of beach and ocean. This 1 bedroom corner unit is 10% ...
Registration Number: TA-120-218-4192-01, GE-12
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
Deluxe Ocean View Tower 2 Unit, Free Parking & Wifi - 2019 Brand New Remodel
Enjoy this newly remodeled corner unit, with outstanding views of Diamond Head, and a spectacular view of the ocean! The entire unit has been completely remodeled, ...
Registration Number: TA-161-136-2304-02
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu