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,
a.`title_rewritten`, a.`custom_url`, a.`ai_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 = CASE
WHEN postalCode IN (96701,96706,96707,96712,6717,96730,96731,96734,96744,96782,96786,96789,96792,96826,96795,96797,96813,96814,96815,96816,96817,96818,96819,96821,96822,96825)
THEN
CASE
WHEN (registration_number like '%TA-%' or registration_number like '%TA %') THEN 1
ELSE 0
END
ELSE 1
END
and postalCode!=''and round(price)>0 and bedrooms>=0 AND LENGTH(href) > 0 AND sleeps>=1 order by rating desc LIMIT 16100, 50
Discover this spacious ocean view 1-bedroom, 2-bath condo offering 898 sqft of comfort and style. The full kitchen features granite countertops, a Wolf cooktop, convection oven, microwave, and a Fisher-Paykel 2-drawer dishwasher, along with a full-size Sub-zero refrigerator, coffeemaker, blender, toaster, cookware, and tableware, making it ideal for preparing meals during your stay.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
This cozy 1-bedroom condo in Waikiki offers stunning harbor views and a prime location just a 5-10 minute walk to Waikiki Beach, Hilton Lagoon, and Kahanamoku Beach. Situated on the 4th floor of a residential building, it provides a comfortable, fully furnished space with a separate living area and bedroom separated by sliding doors. Registration Number: 260100020024, TA-099-018-1888-01, GE-099-018-1888-01
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
This beautifully remodeled two-bedroom, two-bath oceanfront condo at Menehune Shores is the perfect Maui getaway! This corner end-unit offers extra windows for natural light and cooling Hawaiian breezes. Enjoy stunning ocean views, breathtaking sunsets, and relaxed island living just steps from a protected swimming and snorkeling beach.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this charming 2-bedroom, 2-bath golf villa located on the first floor with stunning views of the Kapalua Bay Golf Course's 6th fairway. The villa offers a spacious 1344 sqft layout with a seamless blend of comfort and style.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Hawaiian escape in this modern studio located on the 16th floor of a centrally situated hotel near Ala Moana Mall. The unit features two double beds, a private lanai with stunning ocean and city views, and a handy kitchenette equipped with a mini fridge, microwave, and coffee maker, perfect for quick snacks. Registration Number: GE-014-042-7776-01, TA-014-042-7776-01, 1230380020597
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Maui getaway at Maui Kamaole L107, a beautifully updated ground-floor condo perfect for relaxation and adventure. This stylish one-bedroom, two-bath unit comfortably sleeps four and offers an ideal blend of comfort and convenience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Nohonani 103, your oceanfront escape in West Maui! This inviting ground-floor, 1-bedroom, 1-bath condo offers all the comforts of home with unbeatable access to the beach—just steps from your private lanai. With space to accommodate up to 4 guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the magic of Hawaii at The Grand Islander by Hilton Grand Vacations, ideally located in Waikiki Beach Walk. This resort offers a perfect blend of luxury, convenience, and family-friendly activities. The 1-Bedroom Premier Ocean View Suite features breathtaking views of Waikiki Beach and the Pacific Ocean, a plush king-size bed, a fully equipped kitchen with stainless steel appliances and granite countertops, a spacious living area with a sleeper sofa, and a private balcony ideal for morning coffee or sunset watching. Registration Number: TA-005-837-4656-01, 260090010000, GE-005-837-4656-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
6 acres in Kihei. The unit offers a spacious living area with an open-concept kitchen, dining, and living space. The bedroom features large sliding doors with etched panels that open to the living room, enhancing the sense of space and allowing natural light to fill the room.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
This exceptional 6-bedroom plus den ground-floor condo at the renowned Kaanapali Alii provides an expansive and luxurious Maui vacation experience. Combining an oceanfront 2-bedroom suite with two neighboring 2-bedroom partial ocean view suites, it offers over 5,000 square feet of elegant living space and can comfortably host up to 20 guests.
8 bedroom
6 bathroom
Sleeps 20
Hawaii> Lahaina
The ultimate Diamond Head apartment offers 2000 square feet of beautifully renovated space right on the ocean. Wake up to surf at Tonggs right in front, and enjoy easy access to walking or running around iconic Diamond Head. Located just off Kapiolani Park, this property provides a breezy, relaxed atmosphere perfect for unwinding. Registration Number: TA-198-152-3968-02, GE-198-152-3968-02, 310330110013
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Discover the Ridge Villas, perched high above with breathtaking views of The Kapalua Bay Course's fairway, mountains, and the ocean. This second-floor, two-level townhome offers a spacious layout with a large lanai perfect for enjoying the scenery.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Perched in the uplands of the prestigious Mauna Kea Resort, this stunning villa offers sweeping panoramic views of the Kohala Coast, where breathtaking sunsets paint the sky. Designed for seamless indoor-outdoor living, it features an expansive covered lanai with plush chaise lounges, perfect for soaking in the ocean breeze.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Discover the ultimate Waikiki vacation experience in this luxurious 3-bedroom, 3-bathroom oceanfront condo at the iconic Ilikai Hotel. Spanning over 2,450 sq. , this highly sought-after unit is located in the end stack (units 43 and 44), offering unparalleled views of the Pacific Ocean, Duke Kahanamoku Lagoon, and Ala Wai Yacht Harbor. Registration Number: 260100070473, GE-043-431-1168-01, TA-043-431-1168-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
Experience breathtaking oceanfront living in this spacious 2-bedroom, 2-bath condo located just south of Kailua-Kona town. Situated on the second floor of a sought-after complex along Ali'i Drive, this unit offers panoramic 180-degree views of the Kona coastline and ocean, perfect for whale watching, dolphin sightings, and enjoying vibrant sunsets.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
This beautifully appointed condo is located on the 5th floor of the iconic Ilikai Hotel, offering breathtaking views of the Pacific Ocean and unforgettable sunsets from your private lanai. Wake up to lush tropical landscaping overlooking the courtyard, with the shimmering ocean in the background. Registration Number: GE-030-323-7120-01, TA-030-323-7120-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hale i Lani, your “home in heaven”, at Hanalei Bay 4304 & 4305! Located on a west-facing bluff in Princeville with sweeping ocean & mountain views and A/C in every room, this peaceful top floor retreat is perfect for couples, families, and small groups.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to your perfect Maui getaway at Maui Sunset, a beautiful ground-floor condo offering stunning partial ocean and lush garden views from the living room and lanai. Step outside directly to the resort’s manicured grounds, where you can stroll or unwind by the pool, hot tub, or BBQ areas.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to this spacious and beautifully furnished 3-bedroom home nestled in the Kona Coastview Subdivision of Kailua-Kona. As you step inside, you'll be greeted by a bright, open layout with cathedral ceilings that create an airy and inviting atmosphere, perfect for relaxing or entertaining.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Located just one stack behind the oceanfront in the closest column to the shoreline at Honua Kai, this luxurious 3-bedroom, 3-bath residence offers breathtaking ocean views that span the home's living area and extend into the West Maui Mountains.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to Kapalua Bay Villa 24G3 – your peaceful island escape in beautiful Kapalua. 5-bath ocean view condo comfortably sleeps up to 4 guests. With central A/C throughout, a king-sized bed in the bedroom, and a queen sleeper sofa in the living room, you'll have everything you need to feel right at home.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a relaxing stay in this well-located Waikiki studio apartment, perfect for exploring Hawaii’s most vibrant area. Situated on the 11th floor of the Aqua Palms building, this independently owned unit offers comfort, convenience, and access to a range of amenities.Registration Number: GE-138-913-3824-01, TA-138-913-3824-01, 260120030244
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This beautiful brand new home is overlooking Hanalei Bay & the beautiful tree canopy of Princeville. With a 10 min walk to the 1 Hotel beach, a short walk to Princeville Center and a quick drive to Hanalei Town, it is the ideal central north shore location.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Princeville
Escape to Waikiki with this cozy hotel room offering a private balcony perfect for morning coffee or sunset views. The 320 sq ft space features two double beds, a flat-screen TV, mini fridge, coffee maker, and all linens and essentials. Daily housekeeping ensures a clean and comfortable stay. Registration Number: TA-014-977-6384-01, 260180070002, GE-014-977-6384-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Hawaiian getaway at Hale Malu, a brand new and gorgeous private estate situated within the exclusive Kauna'oa community on the Big Island. Located within walking distance to the island’s two most renowned beaches, Mauna Kea and Hapuna, this luxurious property offers a perfect blend of privacy, comfort, and stunning scenery.
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Kamuela
This stunning three-bedroom, two-bathroom home offers a perfect retreat for those seeking an extended stay in Kona. Located in a desirable and safe neighborhood, this well-maintained residence provides breathtaking ocean views from most rooms, including views of Kona town and docked cruise ships.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Escape to this peaceful Kauai home adorned with vibrant paintings and surrounded by tropical gardens. Inside, vaulted ceilings and skylights create a spacious, airy feel, complemented by plush seating and a screened outdoor bathtub for ultimate relaxation.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to Sunset Pickleball Paradise, your ideal Hawaiian retreat on Oahu’s North Shore. This spacious three-bedroom, three-bathroom home comfortably accommodates up to six guests, offering a blend of modern luxury and family-friendly amenities. Registration Number: GE-127-081-0624-01, TA-127-081-0624-01, 590150190000
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Haleiwa
Experience island tranquility at Poipu Sands 221, a beautifully appointed second-floor condo offering over 1,370 square feet of tropical comfort and stunning ocean vistas. Located in the desirable Poipu area, this property combines modern amenities with a peaceful ambiance, making it an ideal retreat for families, couples, or groups seeking a memorable Kauai vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Experience the ultimate Hawaiian escape at Hale Makana, a luxurious single-level estate located in Hawaii Kai's most sought-after oceanfront neighborhood. This elegant five-bedroom, seven-bathroom property offers a perfect blend of comfort, style, and breathtaking views. Registration Number: GE-019-591-8336-01, TA-019-591-8336-01, 390270140000
5 bedroom
7 bathroom
Sleeps 10
Hawaii> Honolulu
Welcome to Maui Kaanapali Villas #137, a beautifully renovated ground-floor studio offering garden views in a stunning beachfront resort on North Kaanapali Beach. This luxurious condo comfortably accommodates up to two guests with a king-sized bed and a queen sofa bed, perfect for a romantic getaway or a peaceful retreat.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
The Waialua 1-bedroom apartment offers a comfortable and modern retreat perfect for a relaxing getaway or an extended stay. Inside, you'll find a cozy queen bed, ensuring restful nights after your day of exploration. The apartment is equipped with air conditioning to keep you comfortable during warm days and a washer for added convenience. Registration Number: 168000457389, TA-764-264-5689-01, GE-154-455-4554-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waialua
5-bathroom private residence offers a perfect blend of comfort, privacy, and breathtaking views. Located just a 6-minute golf cart ride from the heart of the Four Seasons Resort, this home is ideal for families or groups seeking an upscale island getaway.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
The Hokulani 645 at Honua Kai offers a luxurious escape in the heart of Maui. Located on the 6th floor of the Hokulani Tower, this suite boasts breathtaking ocean vistas visible from the expansive 200+ sq ft lanai. The elevated position ensures unobstructed views of the Pacific, lush resort gardens, and spectacular Maui sunsets, creating an ideal setting for relaxation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the charm of this second-floor condo with a lanai that offers stunning views of the great lawn and ocean beyond. The tropical beach decor and original artwork by Robin McCoy create a welcoming atmosphere throughout. Inside, you'll find tile and wood flooring, an updated kitchen with Corian countertops, stainless steel appliances, microwave, full-size refrigerator, dishwasher, coffee maker, blender, toaster, and ample cookware and tableware.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Spacious 3BR Townhome – Steps to Napili Bay, Pool, Hot Tub & More!
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to your perfect Maui escape at Kahana Village, an oceanfront oasis in scenic West Maui. This beautifully remodeled ground-floor condo offers 1,566 sq ft of comfort and style, with three spacious bedrooms and two modern bathrooms. The interior features tasteful Hawaiian-inspired furnishings and island decor, creating a warm, inviting atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Maui Resort Rentals proudly presents this exceptional 6th-floor Deluxe Oceanview Penthouse Suite at Maui Kaanapali Villas, launching on March 10, 2025, and available for bookings starting March 22nd. This rare 2-bedroom, 2-bathroom top-floor corner suite offers unparalleled panoramic ocean views from every room, thanks to its elevated position and expansive private lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to the Cove Waikiki! This spacious end-unit condo offers 2 bedrooms and 2 bathrooms, comfortably sleeping 4 guests with a queen bed in the primary bedroom and two twin beds in the second. Each room is equipped with an AC unit, ensuring comfort during your stay. Registration Number: 260120670099, TA-036-513-3824-01, GE-036-513-3824-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to this charming condo nestled in the heart of Kapa'a, offering a comfortable and relaxing retreat. The space features a spacious bedroom with a plush king-sized bed and air conditioning, ensuring restful nights after your adventures. Stay connected with reliable WiFi and enjoy modern conveniences like a hair dryer and a washing machine during your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapa?a
Escape to this spacious and unique 2-story yurt nestled on a 2+ acre gated estate on Hawaii's North Shore. 5 blocks from famous beaches and surf breaks, this retreat offers a perfect blend of nature and comfort for up to 11 guests.Registration Number: GE-173-135-0528-02, TA-173-135-0528-01, nuc 90 tvu 0623, 1570010290126
4 bedroom
1 bathroom
Sleeps 11
Hawaii> Waialua
Experience the ultimate vacation in this corner suite at Ilikai Resort, offering two balconies and stunning views of the ocean, lagoon, and marina. This luxury condo is beautifully upgraded and fully furnished, providing a comfortable and stylish retreat.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience a comfortable stay in this modern 14th-floor condo located within the Ilikai Marina, right at the gateway of Waikiki. This fully furnished unit offers convenient access to popular attractions, beaches, and shopping centers like Ala Moana, making it ideal for travelers, snowbirds, and temporary workers. Registration Number: GE-163-841-2288-01, 1260100020184, TA-163-841-2288-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This exceptional 6-bedroom suite at Honua Kai Maui offers the perfect retreat for large groups seeking comfort, luxury, and stunning ocean views.
9 bedroom
6 bathroom
Sleeps 20
Hawaii> Lahaina
Enjoy a relaxing stay at this charming Waikiki boutique hotel, offering worry-free amenities and exceptional service. The 226 sq ft room includes a comfortable king bed, private lanai, mini-fridge, and daily housekeeping. Access a rooftop pool, outdoor terrace, and on-site restaurant with bar, perfect for unwinding after exploring Waikiki's top attractions. Registration Number: TA-115-906-5600-01, GE-115-906-5600-01, 260190090000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Kapalua Ridge Villas 2712 – a bright and breezy 1-bedroom, 2-bathroom ground-floor villa that comfortably sleeps up to 6 guests. Step inside to discover an airy, open-concept living space with A/C, and large windows to showcase serene ocean and garden views.
1 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This inviting two-bedroom, two-bathroom deluxe condo offers breathtaking views of the Pacific Ocean and a generous, open layout. The master suite features a comfortable king bed and an ensuite bathroom for added privacy. The second bedroom can be configured with either a king bed or two twin beds, making it perfect for families or friends, and is conveniently located next to a full bathroom.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Surround yourself with breathtaking ocean views from this beautifully decorated two-bedroom condo situated on Maui's eighth floor. Large windows in every room—including the bedrooms, living area, dining space, and kitchen—offer uninterrupted vistas of rolling waves and white sandy beaches.
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the perfect blend of luxury, style, and sophistication at Wailea Beach Villas F202, a stunning Maui retreat perched just above Wailea Beach.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Wailea
Experience the beauty of Maui from this stunning ocean view condo at Mahina Surf. Wake up to whale and turtle sightings right from your lanai, and unwind in the beautifully maintained lush gardens. The property boasts a pristine pool just steps from your door, perfect for relaxing after a day of exploration.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
