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 9850, 50
Experience the perfect Maui getaway at Polynesian Shores 225, a beautifully renovated oceanfront condo designed for comfort and relaxation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Stay at Emmalani Court, a peaceful Princeville condo complex next to Makai golf course, offering stunning ocean and golf views. The large air-conditioned units include modern amenities, making it ideal for relaxing getaways on Kauai's north shore.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
This charming second-floor condo at Wailea Ekahi offers stunning views from your lanai, inviting you to unwind and soak in the beautiful surroundings. Located just a short walk across the property to the pristine beach, this one-bedroom, one-bath unit is perfect for a relaxing Maui vacation.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Wailea
Enhanced COVID cleaning and disinfecting procedures are implemented for every check-in.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Princeville
Experience a luxurious getaway at Westin Kaanapali Ocean Resort Villas with savings of up to 70% on rates compared to direct bookings. Managed and maintained by the resort, all units include full access to amenities and services. Upon arrival, guests check in at the front desk using their resort confirmation, just as if booked directly through the resort, ensuring a seamless check-in process.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This charming ground-floor condo offers a perfect Kauai getaway with stunning ocean views. Recently refurbished in 2005, the unit features a comfortable queen-size bed in a cozy studio layout. The resort’s tropical plantation style creates an authentic Hawaiian ambiance, complemented by amenities like a tiki bar, pool, spa, and BBQ area overlooking the Pacific Ocean.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Discover this beautifully refurbished condo featuring brand new flooring, cabinets, and granite counters, completed in June 2020. The bathroom has been fully upgraded with new tiles, a new shower, and modern fixtures. The condo comfortably sleeps four, with a plush California king in the bedroom and a queen sleeper sofa in the living room.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the perfect Maui getaway at Hale Kamaole, just a short stroll from the popular Kamaole Beach III, renowned for its gentle tides, excellent snorkeling, and stunning sunsets. This condo offers a comfortable king-sized bed in the bedroom and a queen sleeper in the living room, making it ideal for families or groups.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your Hawaiian paradise at Keauhou! This stunning ground-floor condo, unit 2-103 at the Surf & Racquet Club, offers direct ocean views and space for up to six guests. Recently remodeled in 2017, the kitchen boasts modern amenities and a welcoming ambiance, perfect for preparing delicious meals after a day exploring the island.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to Puu Poa 405, a spacious top-floor condo nestled in the upscale Puu Poa complex on the stunning north shore of Kauai. This vacation retreat offers enhanced COVID cleaning procedures for your safety and peace of mind. Located right next to the luxurious Princeville St Regis Hotel, guests have access to a beautifully renovated oceanfront pool, perfect for relaxing after a day of adventures.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Discover this spacious one-bedroom apartment in the heart of Waikiki, featuring stunning views of the ocean and Diamond Head from the balcony, living room, and bedroom. The unit comfortably sleeps four with two queen beds, all linens provided, and a private bathroom stocked with towels and essentials. Registration Number: TA-195-797-0944-01, 260250050291, GE-195-797-0944-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your dream getaway at Country Club Villas #325, a premier top-floor corner residence offering 1,271 square feet of luxurious living space with breathtaking ocean and golf course views.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Discover this beautifully maintained second-story condo in Napili, Maui, offering a perfect blend of local charm and modern comforts. Located just a five-minute walk from Napili Bay, this property provides easy access to some of the island's best beaches, snorkeling spots, and scenic golf courses.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Spread out in this expansive 2500 square foot condo located in the lush gardens of the Regency at Poipu Kai Resort on Kauai's southern coast. Featuring five bedrooms and four full bathrooms, this top-floor home offers ample space for family and friends to relax and enjoy their vacation without feeling crowded.
5 bedroom
4 bathroom
Sleeps 11
Hawaii> Poipu
Welcome to our beautiful Wailea Condo at the Palms of Wailea, a spacious 1200 sq ft newly renovated two-bedroom, two-bathroom retreat just minutes from some of the world's best beaches. The condo offers all the comforts of home, including high-speed wireless Internet (25 Mbps), two TVs with DVD players, a fully equipped modern kitchen, and a lanai with a propane barbecue for outdoor dining.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Welcome to your Hawaiian oasis at Wailua Bay View, a beautifully renovated condo featuring handcrafted Monkeypod wood accents and stunning ocean vistas. Every detail of this $100,000 remodel, completed in March 2025, was meticulously crafted by hand, using sustainable Monkeypod trees harvested on Kauai.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Welcome to Manualoha 901, a spacious 3-bedroom, 3-bathroom condo ideally located on the ground floor in the sunny heart of Poipu on Kauai's South Shore. This tropical retreat is perfect for families or groups seeking comfort, convenience, and easy access to some of Kauai's most beautiful beaches, shopping districts, restaurants, and golf courses.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Make your Maui memories at our beautifully remodeled second-floor studio located at Kihei Bay Surf. This condo offers a relaxed coastal vibe with a partial ocean view, perfect for a tranquil getaway. Inside, you'll find a fully equipped kitchen, a spacious living area with high ceilings, and a comfortable king-size bed.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Experience the beauty of Maui at Honokeana Cove 209, a spacious top-floor condo nestled in a lush, secluded cove just a quarter mile south of Napili Bay's pristine sandy beach. This 3-bedroom, 2-bath unit offers a perfect blend of comfort, modern amenities, and breathtaking ocean views, making it an ideal retreat for families, couples, or groups seeking a tropical escape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Konea 436 offers a luxurious and tranquil Maui retreat with stunning partial ocean views and lush resort grounds. This spacious one-bedroom suite features a king-sized bed in the primary bedroom, with a sofa bed in the living area to accommodate additional guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Aloha and welcome to Kohola Hale, affectionately known as Whale House—a charming retreat inspired by the ocean and whale motifs. This cozy home comfortably sleeps four, featuring a luxurious king-sized bed in the master bedroom and a double bed in the second bedroom, both with private bathrooms for added privacy.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimea
Escape to this charming, smoke-free condo just a short stroll from the stunning Kahana and Ka'opala Beaches. Covering 640 square feet, the property offers a comfortable living space with modern amenities. Relax by the communal pool or unwind in the garden with outdoor furniture, enjoying the warm Hawaiian breeze.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Escape to Honu Kai's centrally located condo B-16, offering a peaceful retreat in Maui. This renovated unit features a serene bedroom with A/C and a queen-sized bed on the second floor, providing restful nights after your adventures. The living area offers privacy with a shoji door, a queen-sized sofa sleeper, and a cozy dining set, making it ideal for families or couples.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuku
Welcome to your perfect Maui retreat! This beautifully renovated oceanfront studio condo offers stunning views and spacious comfort for a memorable vacation. Located on the 2nd floor at Maui Kai, unit #203 boasts an unrestricted ocean view from a large lanai, ideal for morning coffee or evening drinks while soaking in the scenery.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover this spacious oceanfront condo on Maui's sunny south shore in Kihei. Located on the fourth floor, it offers spectacular panoramic views of the grounds and the Pacific Ocean from a private 180 sq foot deck. One of the best spots on the property, this rare 2-bedroom unit boasts over 1100 square feet of living space, perfect for families or groups.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the ultimate Maui vacation in this luxurious 2-bedroom, 2-bathroom corner unit located on the fifth floor of the Hokulani Building at Honua Kai Resort & Spa. This remodeled condo features a spacious L-shaped balcony offering breathtaking views of the Maui Mountains and the Pacific Ocean, perfect for enjoying breakfast, evening meals, or your morning coffee.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate getaway in this spacious top-floor condo located in The Cliffs of Princeville. Soaring ceilings and expansive walls of windows create an airy, bright atmosphere that feels even larger. From the living room lanai, indulge in stunning long ocean views and lush tropical landscapes, perfect for relaxing or dining alfresco while watching passing ships.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Discover this charming 2-bedroom, 1-bath vacation home in Hilo, perfect for exploring Hawaii's natural beauty. The property offers a quiet outdoor patio, a well-equipped kitchen, and comfortable furnishings for a relaxing stay. Whether you're hiking at Wailuku River State Park, relaxing at Liliuokalani Park, or visiting the Hawaii Volcanoes National Park, this home provides a convenient base for your adventures.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Hilo
Escape to paradise in this beautifully updated ground-floor one-bedroom, one-bathroom condo at Papakea Resort in West Maui. The condo features a private outdoor lanai with ocean views, perfect for relaxing after a day of island adventures. Just steps away, guests can enjoy resort amenities including two heated oceanfront pools, hot tubs, lush tropical gardens, and BBQ areas ideal for outdoor dining.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautifully updated beachfront condo at Sugar Beach Resort, nestled on Maui's pristine Maalaea Bay. This elegant unit offers breathtaking ocean views, modern comforts, and a prime location for exploring the island's natural beauty.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
This charming 2-bedroom, 2-bathroom townhouse offers a perfect oceanfront retreat in the upscale Keauhou Kona Surf & Racquet Club. The gated community features four lighted pickleball and three tennis courts, a swimming pool, and communal BBQ and picnic areas, making it ideal for relaxation and recreation.
2 bedroom
2 bathroom
Sleeps 3
Hawaii> Kailua-Kona
Immerse yourself in the stunning beauty of Kona Coast with this ground-floor oceanfront condo located just a mile south of downtown Kailua-Kona. Perfectly positioned twenty minutes from Kona International Airport, the property offers quick access to Magic Sands Beach—a favorite spot for body surfing—and Kahaluu Beach Park, renowned for sea turtle sightings and snorkeling adventures.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to Kaanapali Royal Q302, a spacious and beautifully appointed 2-bedroom, 2-bathroom condo situated in the heart of Kaanapali. This coastal-style retreat features central air conditioning, three private lanais, and all the comforts needed for a memorable Maui vacation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kaanapali
This spacious 5-bedroom condo in Regency Villas at Poipu Kai offers a comfortable and luxurious retreat for families or groups. With over 2200 sq ft of living space, the villa features modern appliances, central air conditioning, and stylish furnishings throughout.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Koloa
Welcome to Hale Mahina B403, a beautifully remodeled top-floor beachfront condo in Maui offering stunning ocean vistas and classic Hawaiiana decor. The condo features a spacious open-plan layout with a fully upgraded kitchen, complete with stainless steel appliances, and a cozy office desk area perfect for catching up on emails or planning your day.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Napili-Honokowai
Perched on a lush green bluff, this charming island retreat offers breathtaking ocean views and the soothing sounds of the sea. Unobstructed vistas fill the living room and both bedrooms, providing a serene ambiance. Observe tropical seabirds and, during season, majestic humpback whales from the comfort of your accommodations.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover this beautifully remodeled second-floor condo at Honokeana Cove, offering a luxurious tropical getaway in West Maui. The unit features one bedroom plus a mezzanine bedroom and two bathrooms, perfect for couples, families, or groups seeking a peaceful retreat.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Lahaina
Come and enjoy our stunning ocean view condo, recently upgraded with new luxury vinyl plank flooring in both bedrooms. Building C offers the only ocean views in the complex. This top-floor, two-bedroom, two-bath condo features a bright, cheerful atmosphere with Hawaiian decor and the Aloha spirit throughout.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover the perfect island escape at our charming cottage located just steps from the pristine sands of Kepuhi Beach on Molokai's west end. This spacious, well-appointed retreat offers stunning ocean and sunset views right from the comfort of your living space, making every moment memorable.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Maunaloa
Step into paradise at Polynesian Shores Condo #108, your perfect oceanfront retreat on Maui. This newly renovated ground-floor unit provides a peaceful escape with a lush plumeria tree shading your lanai, just steps from the sparkling pool and the tranquil ocean.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience breathtaking oceanfront living in this beautifully remodeled top-floor condo in Maui. The unit features new split-system A/C in both the living room and bedroom, ensuring comfort during your stay. For those who prefer the natural breeze, open the windows and sliding doors to let the trade winds flow through, creating a relaxing atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect island getaway in this stylish 1-bedroom suite at Waikiki Banyan, offering stunning views of Diamond Head and Wa'ahila Mountain from a high floor. Recently renovated with great attention to detail, this modern apartment provides a comfortable and relaxing retreat in the heart of Waikiki.Registration Number: TA-076-860-7744-01, GE-076-860-7744-01, 260250050237
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Breathtaking, magnificent, superlative, sublime: there’s simply no way to overstate the extraordinary appeal of this uniquely situated, strikingly beautiful oceanfront estate. Hale O Ka Nalu Po'i (named for the waves crashing against lava-rock cliffs) offers all the irresistible features you’d expect in a tropical paradise, and more.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Koloa
Experience breathtaking 180-degree ocean views from this stunning end-unit condo in Waikiki. The property features a small oceanview lanai with a sliding door, perfect for enjoying sunsets and ocean breezes. Throughout the home, you'll find beautiful new travertine floors complemented by elegant Koa furniture, window coverings, and linens, creating a luxurious and comfortable atmosphere. Registration Number: 260100020136, GE-170-946-1504-01, TA-170-946-1504-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the perfect Kauai getaway in this ocean and garden view 2-bedroom, 2-bath condo located directly across from Poipu Beach. Unit #809 in the #8 building offers stunning ocean vistas and easy access to some of the island's best swimming, snorkeling, surfing, and boogie boarding spots.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to Manualoha 302, a delightful ground-floor condo nestled just a short stroll from the golden sands of Brennecke's Beach in Poipu. This inviting one-bedroom unit offers an ideal retreat for ocean lovers, sunbathers, golfers, and anyone seeking to soak in the beauty of Kauai's sunny south shore.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Poipu
Experience the laid-back island vibe at Hale Kahanalu #4, a cozy 720 sq ft, 2-bedroom apartment located just a short walk from Poipu Beach Park and Brennecke's Beach. Ideal for families or couples seeking a budget-friendly yet comfortable retreat, this second-floor unit offers tropical furnishings, bamboo flooring, and tasteful artwork that create a welcoming atmosphere.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover this inviting one-bedroom, one-bath garden view condo at Kihei Bay Vista, ideally situated in central Kihei. The condo features a fully equipped kitchen, perfect for preparing meals and enjoying a relaxing stay without dining out every night.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> KIHEI
Discover Hale Hui Kai, a beautiful oceanfront condo ideally located just steps from the renowned Keawakapu Beach on South Maui. This third-floor unit offers stunning garden views and a spacious, airy interior with vaulted ceilings in the master bedroom that features a King-sized bed and sunrise views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Lokelani A101 is decorated with the colors of the Pacific Ocean. Great location on the north end of the property for a quiet, cozy vacation. All the amenities of home. King bed with a sofa sleeper in the living room. Large lanai (porch) for lazing your days away with the lull of the ocean, just 27 feet away.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
