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 '%ilikai marina%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. Access to the lanai will be
Registration Number: TA-016-821-4528-01, GE-016-821-4528-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Welcome to our oceanfront home offering stunning ocean views and Hawaiian sunsets, all within walking distance to the beach. Whether you're seeking adventure, family fun, or relaxation, this is the perfect destination while visiting Oahu. On
Registration Number: GE-040-719-3088-01, TA-040-719-3088-01, 260100020114
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
*Exciting Updates in Progress!
We’re currently renovating to make your stay even better. In the meantime, please take a look at our beautifully updated Ilikai Marina listing to see the stunning upgrades you can expect
Registration Number: 260100020150, TA-167-251-7120-01, GE-167-251-7120-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Welcome to my place here in Waikiki with a queen bed in the bedroom and a queen bed sofa couch. There is a solid sliding door between the bedroom and the living room area. (wood door)
The entrance to Ilikai Marina Condo is on Hobron Lane
Registration Number: GE-093-328-7936-01, TA-093-328-7936-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
2/1/25 NOTE: Spalling work on the ground floor restaurant will start the 2nd week of February 2025. Work will start from 8am-4pm Monday-Saturday
Registration Number: 260100020229, STR #2200, GE-191-015-5776-01, TA-191-015-5776-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
180 Ocean Degree Views from this Beautiful End Unit
Small Oceanview Lanai with Sliding Door
Beautiful New Travertine Floors Throughout
Beautiful Koa Furniture, Window Coverings, & Linens
Foldout Queen Sofa with Koa Frame
Generous
Registration Number: 260100020136, GE-170-946-1504-01, TA-170-946-1504-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
UNOBSTRUCTED SUNSET/OCEAN VIEWS! STEPS TO WAIKIKI BEACH. Walk to parks, shopping mall, restaurants, shops, buses, rental cars & Waikiki night life. Parking available. No smoking room. Clean and fresh high floor.
UNOBSTRUCTED SUNSET/OCEAN
Registration Number: 1260100020230, GE-021-620-5312-01, TA-046-192-0768-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. Access to the lanai will be
Registration Number: GE-193-846-8864-01, TA-193-846-8864-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
UNOBSTRUCTED SUNSET/OCEAN VIEWS! STEPS TO WAIKIKI BEACH. Walk to parks, shopping mall, restaurants, shops, buses, rental cars & Waikiki night life. Parking available. No smoking room. Clean and fresh high floor.
UNOBSTRUCTED SUNSET/OCEAN
Registration Number: 1260100020186, GE-046-192-0768-01, TA-046-192-0768-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Ilikai Marina Condo 786 is a fantastic accommodation in a superb location, offering a stunning view of the marina. Guests praise the cleanliness, well-equipped kitchen, and convenient access to beaches, restaurants, and shopping. The condo comes
Registration Number: TA-113-797-5296-01, GE-113-797-5296-01, 260100020049
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Ilikai Marina Condo 992 offers stunning marina and ocean views, a well-stocked kitchen, and a convenient location near Waikiki’s quieter end. Guests appreciated the cleanliness, comfortable accommodations, and proximity to Hilton Hawaiian
Registration Number: TA-111-177-5232-01, GE-111-177-5232-01, 260100020078
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. Access to the lanai will be
Registration Number: TA-048-703-0784-01, GE-048-703-0784-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Updates Scheduled for April 2025 - Lower Rates Before Update Panoramic, 180 degree Ocean, Harbor and Sunset View
Spacious 1 Bedroom, 1 Bath Condo
Fully Equipped Gourmet Kitchen
Beautiful Rattan Upholstered Furniture
Very Comfortable
Registration Number: TA-127-123-0464-01, GE-127-123-0464-01, 260100020115
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Firework! Great Ocean View! Ilikai Marina (IM92)
In the sun
Feel the sun
Walk barefoot on the sand
Feel the Ocean
Relax
Everyday sunny day
Soak up Nature While In Hawaii!!
This modern studio is bright thanks to the top to
Registration Number: 260100020060, GE-073-540-2496-01, TA-073-540-2496-01
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
2/1/25 NOTE: Spalling work on the ground floor restaurant will start the 2nd week of February 2025. Work will start from 8am-4pm Monday-Saturday
Registration Number: TA-067-365-4784-01, GE-067-365-4784-01, STR #1821, 260100020036
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Just installed new flooring and painted the whole unit!
Great property with amazing 180 degrees ocean view! Enjoy couple minute walk to the beach. Restaurants, bars, all the attractions are just within few minutes walk! Next to the bus
Registration Number: TA-102-471-0656-01, GE-102-471-0656-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. Access to the lanai will be
Registration Number: GE-206-695-6288-01, TA-206-695-6288-01
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Welcome to the family-friendly Ilikai Marina Hotel. This 17th-floor unit faces west, offering beautiful views of the ocean and harbors from both your bedroom and living room.
Situated just a few steps away from the inviting sands and
Registration Number: GE-156-537-0368-01, TA-156-537-0368-01, 260100020227
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. The project is expected to be
Registration Number: GE-060-722-3520-01, TA-060-722-3520-01
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. The project is expected to be
Registration Number: TA-016-821-4528-01, GE-016-821-4528-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. The project is expected to be
Registration Number: GE-049-506-8160-01, TA-049-506-8160-01, 1260100020044
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Located within the Ilikai Marina, this condo is located on the 14th floor. You'll love the convenient location in Waikiki, close to the beach, Ala Moana Shopping Center & the Waikiki strip!
KITCHEN
- Refrigerator
- Electric
Registration Number: GE-163-841-2288-01, 1260100020184, TA-163-841-2288-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Construction Notice 4/23/25: Spall repairs are currently underway on the ocean-side units and will commence on the mountain-side units in May. Please be aware that construction noise may occur between 8 AM and 4 PM. The project is expected to be
Registration Number: 260100020053, GE-069-046-4256-01, TA-069-046-4256-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
We would like to inform you that Ilikai Marina Building is having a Lanai Construction starting 04/01/2025.
During construction on each lanai stack, the glass doors will be locked closed "from the outside" for safety. Please make no
Registration Number: GE-125-401-6512-03, 1260100020042, TA-125-401-6512-03
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
UNOBSTRUCTED SUNSET/OCEAN VIEWS! STEPS TO WAIKIKI BEACH. Walk to parks, shopping mall, restaurants, shops, buses, rental cars & Waikiki night life. Parking available. No smoking room. Clean and fresh.
TEMPORARY LANAI WORK - Completing Mid
Registration Number: TA-083-345-6128-01, GE-083-345-6128-01, 1260100020045
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Waikiki
