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 10200, 50
Experience the beauty of Maui from Valley Isle 401, an oceanfront condo offering breathtaking views and modern comforts. This 4th-floor unit features a new living and dining area with summer 2021 updates, creating a welcoming space for up to four guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Mahana 804, a beautifully appointed 1-bedroom, 1-bathroom condo offering breathtaking ocean views from the 8th floor. This modern retreat comfortably accommodates up to 4 guests with a king-size premium mattress in the bedroom, which features direct access to a private lanai and stunning ocean vistas.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this stunning ocean-view studio in Kauai designed for maximum tropical enjoyment. The all-glass exterior wall with a large sliding door opens onto a spacious lanai, where you can sip your morning coffee as the sun rises over Prince Kuhio Park, casting warm light on the lush surroundings.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Poipu
Experience comfort in this charming one-bedroom condo featuring stunning ocean and garden views. The living room offers a cozy space with a large flat-screen TV, perfect for unwinding after a day in the sun. The bedroom boasts a king-sized bed, dresser, and two nightstands, with new sliding windows for fresh air.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect island retreat in this beautifully renovated oceanfront condo at Molokai Shores. A106 offers a bright, clean, and comfortable space with tile floors throughout, new appliances including a dishwasher, and stylish furnishings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaunakakai
Step into this beautifully renovated 754-square-foot corner condo, ideally located just minutes from Kihei’s famous beaches. The unit offers breathtaking ocean views from a large, private lanai, perfect for relaxing and enjoying the tropical surroundings.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to Papakea D401, a beautifully renovated 2-bedroom, 1-bath condo nestled on the fourth floor of the lush Papakea Resort in Maui. This spacious unit comfortably accommodates up to 7 guests, making it ideal for families or groups seeking a relaxing island escape.
3 bedroom
1 bathroom
Sleeps 7
Hawaii> Lahaina
Come and enjoy the Maui sun and laid-back lifestyle in this beautifully decorated 1-bedroom condo at Maui Vista. The unit is completely furnished, clean, and designed with an island style that creates a relaxing atmosphere. Surrounded by 10 acres of lush, tropical gardens, the complex offers a peaceful retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Hale A Ka La (House of the Sun), a beautifully appointed, private town-home nestled on the 9th hole of Waikoloa Beach Golf Course. Managed professionally by Kona Coast Vacations, this spacious residence offers a perfect blend of comfort, privacy, and tropical elegance.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Escape to this beautifully appointed 3-bedroom condo in Mauna Lani, offering a perfect blend of comfort and luxury. The open layout features a fully equipped kitchen, dining area, and ample seating for your family or friends. Each bedroom is thoughtfully furnished with king beds or twin beds, providing restful nights after days filled with adventure and relaxation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimea
Experience the ultimate Waikiki getaway in this charming and well-equipped condo, just a short two-block walk to the world-renowned beach. Wake up to breathtaking ocean views and gentle tropical breezes from your eat-in kitchen or spacious lanai, perfect for relaxing mornings or sunset dinners. Registration Number: TA-029-703-5776-01, 260230450095, GE-029-703-5776-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Bring your family or friends to Ocean Gem, a beautifully appointed single-level condo located in the prestigious Mauna Lani Point community. This spacious 2-bedroom residence offers breathtaking views of the ocean coastline, the renowned Mauna Lani South golf course, and the famous Black Sand Beach.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Experience the ultimate beachfront getaway at this stunning ground-floor condo on Ka'anapali Beach. Just steps from the sand, unit 161 offers unobstructed ocean views through floor-to-ceiling windows, creating a seamless connection to the outdoors.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the perfect Hawaii getaway in this beautiful top-floor condo located in desirable South Kihei. Enjoy breathtaking ocean vistas and the chance to see whales breaching during the winter months right from your private lanai. The spacious outdoor area is perfect for sunbathing on lawn chairs or sharing drinks and appetizers while soaking in the stunning scenery.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Maui getaway in this spacious ground floor suite at Honua Kai's Konea Tower. Covering 1315 sq ft, this two-bedroom condo offers easy access to pools, beach, and BBQ areas, making it ideal for families or groups seeking convenience and comfort.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to our charming Kona Pacific Condominium, the perfect home base for exploring the Big Island. This 829-square-foot, fully renovated unit offers comfort and convenience just a short stroll from Kona's best shopping, dining, and attractions.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to Lokelani C-201, a beautifully upgraded oceanfront condo nestled in the peaceful Honokowai area of Maui. This two-bedroom, two-bathroom residence offers a perfect blend of comfort, style, and breathtaking views, making it an ideal retreat for your island getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to paradise in this exquisite ocean view penthouse condo at Kaanapali Shores. Located on the top floor with no neighbors above, this 1-bedroom unit offers sweeping views of the sparkling Pacific Ocean from a spacious 104 sq ft lanai, perfect for morning coffees or sunset cocktails.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Welcome to Hanalei Bay Resort unit 4323/4324, a luxurious top-floor condo offering sweeping views of Hanalei Bay and the Bali Hai Mountains. This spacious retreat features 2 bedrooms, each with a comfortable King-sized bed, and a loft with an additional King bed, accommodating families or groups comfortably.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Imagine waking up in a beautiful condo on Maui, offering breathtaking ocean views that extend as far as the eye can see. Located on the third floor of unit 617 at Kuleana, this one-bedroom, one-bathroom retreat is perfect for two guests seeking a relaxing island escape.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience the ultimate Maui getaway in this highly sought-after Royal Mauian unit, ideally situated right on the beach in Kihei. This spacious condo offers breathtaking ocean vistas, a large private lanai, and access to upscale amenities, making it perfect for relaxation and entertainment.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Welcome to Maui Parkshore 305, a prime Kihei location just steps from the beach! This beautiful 2-bedroom, 2-bath condo offers stunning ocean views from your private lanai, where you can watch whales during the season or enjoy breathtaking sunsets.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to this beautifully crafted eco-friendly home built from cargo containers, nestled on an old lava flow in Hawaii. As of January 2021, the solar system has been upgraded, enhancing its off-grid capabilities. The residence features three bedrooms and one bathroom, designed with quality and functionality in mind.
4 bedroom
1 bathroom
Sleeps 8
Hawaii> Kalapana
Escape to Hale Honu, a stunning oceanfront retreat managed by Kona Coast Vacations. This spacious 4-bedroom, 4-bathroom home offers breathtaking panoramic ocean views and luxurious amenities. Each bedroom boasts a private balcony overlooking the ocean, creating a peaceful sanctuary to unwind.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua Kona
Escape to the serene beauty of Princeville, Hawaii, with this charming 2-bedroom, 2-bathroom condo at Pali Ke Kua. Nestled amidst lush tropical foliage, the property offers a private, tranquil setting with stunning views of mountains and waterfalls from the covered patio.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
5-bath villa offering spectacular ocean views and just steps from the beach. This unique two-story Terrace villa features high ceilings, expansive floor-to-ceiling windows, and panoramic ocean vistas, creating an airy and bright living space. The lower level boasts outdoor dining on a generous lanai complete with a wet bar, perfect for sunset cocktails and alfresco meals.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kamuela
Welcome to this inviting guest suite on the beautiful island of Kauai, offering a perfect blend of comfort and convenience.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Step into Grand Champions 108, a beautifully remodeled one-bedroom, two-bathroom villa in Wailea, Maui, offering a perfect blend of contemporary elegance and island charm.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
Welcome to your perfect Hawaiian escape at the Outrigger Kauai Beach Resort and Spa, where your private unit offers breathtaking ocean views and ultimate comfort. This end-unit hotel-style room features two comfortable Queen beds, making it ideal for couples or small families seeking a relaxing island retreat.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lihue
Enjoy the privacy and natural light of this spacious end-unit condo, offering more space than interior units. The large master bedroom includes a sitting area, full bath, king bed, desk, and an additional queen bed, all with their own lanai for outdoor relaxation.
1 bedroom
2 bathroom
Sleeps 5
Hawaii> Princeville
This charming beach cottage offers a one-of-a-kind oceanfront experience in Hauula, Hawaii. Imagine relaxing on the porch watching the tranquil ocean just steps away, with frequent turtle sightings adding to the magic. The property provides all the amenities needed for a comfortable stay, making it an ideal vacation spot. Registration Number: 530080020128, TA-007-640-8832-01, GE-007-640-8832-01
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Offering the finest in ground-floor resort living, this Honua Kai crown jewel boasts a stunning interior oceanfront corner location in the closest column to the ocean at all of Honua Kai. Because of the raised vantage point, your group will enjoy the best of both worlds with all the extra space and convenience of a large adjacent lawn area, along with stunning oceanfront views that extend deep into the living room, dining area, and kitchen.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to White Sands Village, #221! This beautifully maintained, partly renovated 2-bedroom, 2-bathroom condo offers a perfect Hawaiian getaway just across the street from Magic Sands Beach along the famous Alii Drive. Inside, you'll find a thoughtfully designed space with modern comforts, including a dedicated workspace for zoom calls, central air conditioning, a smart TV, and high-speed internet.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to Surfrider Hilo Vacation Rental Home, your perfect oceanfront retreat on Hawaii's Big Island. This spacious 2-bedroom, 2-bath house offers stunning views across South Hilo Bay and direct access to Richardson's Beach Park, renowned for some of East Hawaii's best snorkeling.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Hilo
This bright and inviting Maui condo offers a relaxing retreat with stunning views of both the ocean and mountains from your private lanai. Located just across the street from one of Maui's top swimming beaches, the property provides easy access to a variety of outdoor activities including snorkeling, whale watching, and windsurfing.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover this magnificent Hawaii condo, situated just 30 feet from the ocean with unobstructed 180-degree panoramic views. The deluxe unit features exquisite furnishings in a serene setting, offering a true heaven on earth. Enjoy the soothing ocean breezes in a beautifully remodeled space with modern amenities including wireless internet, flat-screen TVs, dishwasher, washer/dryer, microwave, CD player, and white wood shutters on all windows.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover a stunning oceanfront retreat in Kona with this beautifully renovated one-bedroom, two-bath condo, completed in 2023. Located within the well-maintained Kanaloa complex, it offers a perfect blend of comfort, style, and tropical charm.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua-Kona
5-bath condo nestled in the heart of Lihue, Kauai. Perfectly positioned near Kalapaki Beach, local dining spots, and top attractions, it offers a relaxing island getaway. The second-floor unit features mountain and ocean vistas from two furnished lanais, creating a serene setting to unwind after a day exploring Kauai's natural beauty.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lihue
Escape to the stunning shores of Maui at Kihei Sands, a spacious oceanfront condo perfect for families or couples seeking relaxation and adventure. Priced at just $249 per night until May 15, this well-appointed unit offers a blend of comfort, convenience, and breathtaking views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to this charming and private Maui garden home, featuring two bedrooms and two bathrooms, situated in a peaceful setting just a short walk from the stunning Kamaole Beach #1. The residence offers a cozy and well-furnished interior, perfect for relaxing after a day exploring the island.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Paniolo Hale offers a serene retreat on Molokai's sunset-rich west end, providing guests with a lush tropical garden setting and stunning views of the ocean. This two-bedroom, two-bath condo spans 1900 square feet and is perfect for families or couples seeking privacy and comfort.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Maunaloa
Discover our charming ground-floor condo at Kamaole Sands, perfectly positioned just steps from pools, hot tubs, and a short walk to Kamaole III Beach. Spanning 818 sq ft, this cozy retreat comfortably hosts up to four guests. The bedroom features a plush king-sized bed, while the living area is equipped with two twin Murphy beds, offering superior comfort compared to traditional sofabeds and easy to set up for both kids and adults.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Hale ‘Oli, a peaceful island escape nestled on Kauai’s North Shore. Surrounded by lush mountains and just a short walk to Wainiha Ku’au Beach, this spacious island-style home offers a perfect blend of privacy, comfort, and natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haena
Experience breathtaking oceanfront living at Royal Kahana 511, a beautifully appointed condo offering panoramic 240-degree views of the deep blue Pacific. Located on the 5th floor, this corner residence boasts abundant natural sunlight that fills every room, creating a warm and inviting atmosphere.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to our charming Kauai Ocean View Condo, offering a budget-friendly and comfortable retreat just 2 miles from Lihue Airport. This 810 sq ft unit comfortably sleeps 3 guests, with a king-sized bed in the master bedroom and a versatile sleeping arrangement in the living room, including a recliner sofa and an available blow-up mattress in the closet—ideal for small families or couples traveling with kids.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
Welcome to your perfect island retreat in Princeville, Kauai! This spacious, recently upgraded condo offers a comfortable and light-filled space ideal for relaxing after exploring the Garden Island. The condo features two bedrooms, each with cozy king-size beds, and a versatile pull-out queen sofa in the living room that can sleep 1-2 additional guests.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to Haleakala Shores B509, a spacious top-floor condo offering breathtaking views of Maui's waters, West Maui mountains, and Haleakala. This inviting unit combines comfort and natural beauty, making it an ideal retreat in Kihei.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Kahle Ohana, a beautifully appointed 5,000 sq ft tropical retreat managed by Kona Coast Vacations. This inviting home features lush garden views and a stunning 1,000 sq ft lanai, perfect for relaxing and enjoying Hawaii’s natural beauty.
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Kailua Kona
The Westin Kannapali Resort Villas offer a luxurious and convenient Maui getaway. Each unit features a washer and dryer, fully equipped kitchen or kitchenette, and is attended by the resort for your comfort.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to this stunning direct oceanfront first-floor unit at the highly desirable Keauhou Kona Surf & Racquet Club, Building 3. The location offers unobstructed, breathtaking views of the Pacific Ocean, thanks to its elevated position on the land and minimal lava rock obstruction.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
