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 Sunset%') AND sleeps>=1 order by rating desc LIMIT 0, 50
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
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
See my other properties #3156864 and #3197017 and #3512192 for more 5-star reviews and to book multiple condos for large groups. Located on a high floor of the Waikiki Sunset with sweeping ocean, coastline, and park views this condo ...
Registration Number: TA-065-133-9776-01, GE-065-133-9776-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
The best Diamond Head view in all of Waikiki is yours to behold from this immaculate, beautifully furnished and well-appointed condominium. This is for you, the guest who appreciates quality and value. A spectacular, direct Diamond Head ...
Registration Number: 260280110064, GE-212-869-3248-02, TA-212-869-3248-01
3 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
**Looking for other accommodations, please view my other unit located in the same building #2824127** This unit is located on a high floor of the Waikiki Sunset with sweeping Diamondhead, ocean, coastline, and park views, this condo ...
Registration Number: TA-065-133-9776-01, W10360673-01, 1260280110204
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
**Looking for other accommodations, please view my other unit located in the same building #2773517** This unit is located on a high floor of the Aston Waikiki Sunset Hotel, this condo offers one bedroom (one king size bed), one bath, ...
Registration Number: TA-065-133-9776-01, 1260280110395, GE-064-617-2672-01
2 bedroom
1 bathroom
Sleeps 6
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: GE-202-106-0608-01, TA-202-106-0608-01, NUC #: 90/TVU-1287/1990/NUC-1454, 260280110184
2 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: 260280110034, TA-201-860-0960-01, GE-201-860-0960-01, 2211-CCH-0236
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals. The Waikiki Sunset has long been a favorite of savvy vacationers who enjoy the best of both worlds! This elegant condominium hotel resort located...
Registration Number: GE-029-812-5312-01, TA-029-812-5312-01, 260280110319
2 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: GE-202-106-0608-01, TA-202-106-0608-01, NUC #: 90/TVU-1210/1990/NUC-1367, 260280110084
2 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: NUC #: 90/TVU-1271 - 1990/NUC-1438 Exp. date: 09/30/2025, TA-161-478-9120-01, GE-161-478-9120-01, 126280110165
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome and Aloha! One bedroom-Full Kitchen standard view of ocean "Self Catering" condominium unit. We are just 1 short block from "World Famous" Waikiki Beach and two blocks from the Honolulu Zoo. The Waikiki Sunset offers its guests ...
Registration Number: GE-078-028-0832-01, 260280110024, TA-078-028-0832-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Be Cool... This is my own Hawaii!! Monochromatic! Ocean and Diamond Head View with 1 Free Parking!! This renovated 1 bedroom will provide you the perfect vacation!! As you arrive at Waikiki Sunset, you will be met with the modern ...
Registration Number: 260280110238, GE-012-217-3440-01, TA-012-217-3440-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome and Aloha! This 20th floor unit of the Waikiki Sunset is fully furnished with one king size bed in the bedroom, one queen size sofa bed in the living room, and kitchen counter with four dining chairs. The full size kitchen ...
Registration Number: TA-068-483-1232-01, GE-068-483-1232-01, 260280110182
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome and Aloha! This 15th floor unit of the Waikiki Sunset is fully furnished with two twin size beds in the bedroom, two sofa couches/beds in the living room, and kitchen counter with three stools. The full size kitchen has ...
Registration Number: GE-078-028-0832-01, 260280110113, TA-078-028-0832-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Partial Ocean View Waikiki Sunset Condo, Free Parking & Wifi Soak in the view from this high level condo. The view from the lanai is of Diamond Head Crater and Kapiolani Park to the left and the ocean to the right. This condo suite is...
Registration Number: GE-121-578-0864-01, 260280110090, TA-121-578-0864-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Right in Waikiki close to the World Famous Waikiki Beach!! Great place to take a vacation in Waikiki. Make Waikiki like its your second home. We have a room ready for you with all the unities needed for your daily life. From towels, coffee ...
Registration Number: TA-012-352-0000-01, GE-012-352-0000-01, 260280110261
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
***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 simple! Before making a reservation request, kindly ...
Registration Number: 260270350000, TA-033-867-4688-01, GE-199-896-9344-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu