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 10300, 50
Welcome to the Palm Villas at Mauna Lani Unit G-22 nestled in the heart of the world-renowned Mauna Lani Resort. This beautifully appointed second-floor condo offers a perfect blend of privacy and accessibility, situated between the scenic north golf course and an archaeological preserve.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimea
Experience the charm of our Kailua Village Condo, situated in the heart of Kailua-Kona, Hawaii. This elegant 6th-floor unit offers breathtaking full ocean views where you can watch whales and dolphins in Kailua Bay, and catch stunning sunsets from your private lanai.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Hale Naniloa offers a peaceful retreat nestled at the end of a quiet cul-de-sac just two blocks above the ocean in Kailua-Kona. This charming, fully remodeled Hawaiiana-style home features a private saltwater pool surrounded by ample lounging space and mature landscaping, perfect for relaxing in paradise.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Enjoy a memorable Maui vacation at Sugar Beach Resort 109, a beautifully appointed beachfront condo offering spectacular ocean views and direct access to five miles of pristine white-sand beach. This spacious one-bedroom unit is perfect for couples or small families seeking a relaxing escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the perfect Hawaiian getaway at Waikiki Sunset #2413, a beautifully updated 24th-floor condo with stunning Diamond Head and partial ocean views. This 2-bedroom, 1-bath unit offers modern comforts including central air conditioning, free covered parking, high-speed Wi-Fi, and a full kitchen, making it ideal for families or small groups up to 6 guests.Registration Number: TA-100-358-5536-01, 260280110245, GE-100-358-5536-01
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Experience the ultimate Maui retreat in this beautifully remodeled top-floor condo nestled in Wailea Ekolu Village. The spacious unit features high beam ceilings and tasteful Hawaiiana furnishings that create a warm, inviting atmosphere. Fully air-conditioned and equipped with top-of-the-line appliances, exquisite cabinetry, upgraded bathrooms, and granite countertops, this condo offers both comfort and style.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
A luxurious top-floor hotel suite offering breathtaking views of Hanalei Bay and surrounding mountains. This elegant room features a full bath, a comfortable king bed, and a convenient butler's pantry with a bar fridge and coffee maker, perfect for relaxing after a day of exploring Kauai's scenic North Shore.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Immerse yourself in a modern, beach-inspired retreat on Kauai's tranquil ocean side. 5-bath end-unit offers a perfect blend of comfort and convenience. The unit features a sleek, uncluttered design with high-gloss cabinets, an Acacia wood countertop, and a full resort-style kitchen, making it ideal for preparing delicious meals.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Experience the perfect island retreat at Paki Maui 217, a charming 1-bedroom, 1-bathroom condo nestled along the shores of Honokowai in West Maui. This thoughtfully furnished unit accommodates up to four guests, making it ideal for couples, small families, or solo travelers seeking a blend of relaxation and adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to the beautiful east coast of Kauai with this charming 1-bedroom, 1-bathroom condo at Pono Kai Resort in Kapaa. Perfect for couples, small families, or remote workers seeking a peaceful island retreat, this fully furnished unit offers comfort and convenience in a lush tropical setting.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Experience the laid-back charm of Hawaiian island living in Islander on the Beach unit #322, a cozy 3rd-floor studio designed with comfort and style. The bright space features floor-to-ceiling sliding glass doors that flood the room with natural sunlight and lead to a private lanai offering stunning ocean views.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa'a
This unit is part of the renowned Worldmark resort managed by Wyndham, the third-largest hotel group in the USA, known for exceptional service and quality. Located in Kihei, the resort boasts 200 well-maintained units, with a dedicated team ensuring cleanliness and upkeep.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kihei
Experience the perfect Maui getaway in Papakea G105, a charming 2-bedroom, 2-bath condo on the ground floor offering partial ocean views and easy access to the pool and grassy lawn. This spacious unit comfortably sleeps up to 7 guests, making it ideal for families or groups seeking a relaxing island escape.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Lahaina
Discover your perfect Maui getaway at this charming Kihei condo located in the secure and well-maintained Pacific Shores complex. The unit comfortably sleeps four, featuring a cozy queen bed in the bedroom and a plush full-size memory foam sleeper sofa in the living area.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
The Maui Banyan Resort offers a welcoming 1-bedroom condo in South Kihei, perfect for your Maui getaway. Located in the highly desirable G building, the closest to the ocean, this condo features a charming peek-a-boo view of the sea and the soothing sounds of the ocean when you open the lanai doors.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Makani Kai, an expansive oceanfront retreat offering breathtaking views and luxurious amenities. Perfectly situated on Lyman's surf break, this stunning home provides a true island getaway experience.
5 bedroom
7 bathroom
Sleeps 14
Hawaii> Kailua-Kona
Immerse yourself in the beauty of the Kona Coast with this inviting 2-bedroom, 2-bathroom condo designed for comfort and relaxation. Located just a half-block from Kahalu'u Beach Park, it offers easy access to world-renowned snorkeling, swimming, and sunbathing spots.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Sit back and relax at Kana'i A Nalu 312, a beautiful oceanfront condo nestled along five miles of pristine white-sand beaches in Maalaea. With its prime location along Maui's southern coast, this property provides easy access to popular destinations like Lahaina, Kihei, and the North Shore.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Experience the beauty of Maui in this beautifully remodeled oceanfront condo. Featuring two bedrooms and two baths, it offers stunning views of the Pacific Ocean, perfect for whale watching and enjoying Hawaii’s natural wonders.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Welcome to Hale O' Naia, a beautifully appointed 2-bedroom, 2-bath oceanfront condo located directly on Ali'i Drive in Kailua-Kona. This end-unit offers breathtaking views of the ocean, where you can watch dolphins, whales during the season, and stunning sunsets from your private spacious lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience a perfect island getaway at Kona Reef B33, a spacious 758 sq. ft. condo ideally located next to Honl�s Beach, one of Kona's few pristine white sand beaches. This inviting unit comfortably sleeps up to four guests with a plush king bed and a cozy sofa bed, making it ideal for couples or small families seeking a relaxing escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
This charming three-bedroom oceanfront condo offers unparalleled views of the Pacific, making it an ideal getaway for those who prioritize location and coastal beauty. Situated in the quiet, low-density Puamana community, the unit provides a serene retreat with direct access to a half-mile shoreline and popular surf spots.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
The Owners of Casa#134 have recently completed a stunning remodel, transforming this condo into a luxurious retreat. Featuring a top-of-the-line kitchen and bathroom with a walk-in shower, this spacious 736 sq ft one-bedroom unit offers comfort and style.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Welcome to Hanalei Colony Resort F3, a unique and charming upstairs corner condo located directly on the beautiful north shore of Kauai. Recognized as one of Travel + Leisure's top 20 favorite resorts, this beachfront retreat offers an authentic Hawaiian experience with stunning ocean vistas and modern comforts.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hanalei
This beautifully remodeled and spacious two-bedroom, two-bathroom condo offers a peekaboo ocean view from the lanai, perfect for your Maui getaway. Located in the heart of Kaanapali, Maui Eldorado F104 is just a short stroll from Whalers Village, where you can enjoy shopping, dining, and beaches.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Aloha! Welcome to Kona Ohana Hale, a beautifully renovated condo nestled in the heart of Kailua-Kona. This inviting space offers a perfect blend of modern comfort and local charm, making it an ideal home base for exploring the Big Island. From the moment you arrive, you'll notice the tasteful decor featuring artwork by local artists, creating a vibrant yet relaxing atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
This charming one-bedroom condo offers spectacular oceanfront living with 180-degree views of the North Shore, showcasing colorful sunrises and unforgettable sunsets over Bali Hai. The spacious first-floor unit is beautifully decorated with rattan and island-inspired decor, providing a relaxed and inviting atmosphere perfect for a tropical escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to your perfect Maui getaway! This stunning oceanfront condo in Kihei offers breathtaking views of the ocean and beach from your private lanai. Recently remodeled in October 2023, the unit combines modern comfort with island charm. It features one bedroom, a bathroom, a sleeper sofa, and a Murphy bed, comfortably accommodating up to six guests.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kihei
Located just steps away from the stunning Kamaole Beach III—arguably the best beach on the island—Maui Kamaole C-208 offers a perfect blend of luxury, comfort, and convenience. Whether you're planning a family getaway or a retreat with friends, this two-bedroom, two-bathroom condo accommodates up to six guests, providing a welcoming space for everyone to relax and connect.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the perfect blend of luxury and comfort in this recently renovated condo located in the sought-after Wailea Ekolu community. Boasting expansive panoramic ocean views, this second-floor unit offers a bright and airy atmosphere with natural lighting and stylish decor.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the charm of Koa Dream at Waikoloa Village, a cozy retreat nestled in a lively community known for its stunning views and tropical breezes. This inviting 2-bedroom, 2-bathroom condo offers a perfect blend of resort luxury and residential comfort, making it ideal for families or couples seeking a relaxing Hawaiian escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience the beauty of Maui from this stunning Eldorado L102 condo, offering a perfect blend of comfort and adventure. Enjoy complimentary daily activities valued at up to $570, including whale watching (seasonal), sunset dinners, snorkel sails, and farm tours.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Enjoy breathtaking ocean views from this top-floor Maui Vista condo, ideally located just across the street from Charlie Young Beach. The property offers easy access to the beautiful Kamaole Beaches and the Cove, perfect for swimming, snorkeling, and sunbathing.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the charm of this Kona village cottage, ideally located within walking distance to historic Kailua-Kona Village. A short stroll takes you to the renowned Kahalu'u snorkeling beach and Magic Sands Beach, offering pristine sands and crystal-clear waters.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the ultimate beachfront lifestyle in this beautifully maintained 1-bedroom condo just 20 meters from the ocean in West Maui. Situated on the second floor of a private, four-story complex, this corner unit offers spectacular, unobstructed wrap-around views of the ocean and unforgettable sunsets.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Napili-Honokowai
Welcome to Makaha Beach Cabanas, a hidden gem on the northwest shores of Oahu, just 45 minutes from Honolulu International Airport. This location offers an authentic Hawaiian experience, away from the busy crowds, yet close enough to enjoy the island’s top attractions. Registration Number: GE-098-620-0576-01, 840040140000, TA-098-620-0576-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waianae
Your dream vacation begins the moment you step into this beautiful beachfront condo in Waikiki. Located on the 9th floor of the Ilikai Hotel, this unit offers spectacular panoramic views of the Pacific Ocean and the Duke Kahanamoku Lagoon. Recently renovated, the bathroom was updated in September 2024, and the living area features fresh furniture including a new sofa sleeper, accent chair, and entertainment center, creating a comfortable and stylish space for your stay. Registration Number: GE-004-043-5712-01, 1260100070165, TA-004-043-5712-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Maui Vista 3421, a beautifully located condo in the heart of Kihei offering breathtaking ocean views. This second-level 2-bedroom, 2-bath unit is ideal for small families or couples seeking a relaxing Maui escape. Just one block from the popular surf spot, The Cove, and within walking distance to some of Maui's most famous beaches including Kamaole Beach I, II, and III, as well as Kalama Park, this condo provides easy access to sun, sand, and surf.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover this beautifully renovated one-bedroom, two-bathroom condo located on the 5th floor in Kihei, just steps from Kamaole Beach Park II. The spacious living area features a large sofa that easily converts into a bed, perfect for relaxing with family or enjoying a good book in the natural sunlight.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Located in the prestigious Kiahuna Golf Village in Poipu, Kiahuna Paradise offers a luxurious escape just 5 minutes from some of the world's most stunning beaches and golf courses. Nestled within a serene and private residential community, this elegant, custom-built home provides the perfect setting for your Hawaiian getaway.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Stay in this beautifully remodeled island-style one-bedroom condo located on the iconic Ali'i Drive in Kailua-Kona. The unit features a luxurious King-size bed, a fully equipped kitchen with granite counters, and a modern bathroom. Decorated with tropical touches, the condo provides a relaxing retreat for up to two guests.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Discover the renewed charm of Ko Olina Beach Villas B204 by Gather, a stunning three-bedroom, three-bathroom Hawaii vacation rental for up to eight guests on the second floor. Fully furnished and meticulously prepared for your stay, this villa offers an unparalleled experience of luxury and tranquility in paradise. Registration Number: GE-102-526-3104-01, TA-102-526-3104-01, 910570090173
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Experience the comfort and convenience of this beautifully renovated 1-bedroom condo located on the 10th floor, offering breathtaking views of Ala Wai Boat Harbor and nearby Ala Moana Beach. The unit has been thoughtfully updated from top to bottom, featuring all-new furniture and appliances to ensure a modern and welcoming stay.Registration Number: TA-104-784-2816-01, GE-104-784-2816-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience a fully renovated, stylish two-bedroom, two-bath condo in Kihei designed for comfort and convenience. The master bedroom features a queen bed, TV, and ceiling fan, while the second bedroom offers two twin beds—perfect for families or small groups.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to our spacious Wailea Ekolu Condominium, a fully equipped 2,000-square-foot townhouse offering comfort and style for your Maui getaway. The condo features modern amenities including multiple flat-screen TVs, WiFi, cable TV, a CD/DVD player, and a telephone, ensuring entertainment and connectivity throughout your stay.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kihei
Looking for the perfect Hawaii getaway? This stunning new 2-bedroom, 2-bath condo in Hilo offers unparalleled views of Wailoa River and sparkling waters, making it an ideal home base for your Big Island adventures. Located in the heart of Hilo, the condo provides easy access to local beaches, eateries, and attractions.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hilo
Named by Condé Nast Traveler as one of the top tropical resorts worldwide, Hanalei Bay Resort offers a true Hawaiian paradise. This scenic retreat is nestled amidst cascading waterfalls and lush foliage, filling the air with sweet tropical scents that are unforgettable.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Experience the vibrant charm of this bright and colorful Maui condo ideally located across from Charley Young Beach, offering easy access to South Kihei's best dining, shopping, and outdoor activities. This fully furnished two-bedroom, two-bath residence is situated on the north side of the building, providing a shady, cool environment with lush garden views that create a peaceful retreat.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Oceanfront Beachfront Retro Style Condo in Maui – Fully Furnished with Stunning Views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Welcome to your perfect oceanfront retreat at Hali'i Kai, a premier complex within Waikoloa Beach Resort on the Big Island of Hawaii. This vibrant 2-bedroom, 2-bathroom vacation home comfortably accommodates up to 5 guests, making it ideal for families or friends seeking a luxurious Hawaiian getaway.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Beach Resort
