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 6300, 50
Experience the perfect Hawaiian retreat in Waikoloa Beach Villas C1, an end-unit condo offering ample space for family and friends. This professionally managed property combines comfort and convenience in a gated community located in the heart of Waikoloa Beach Resort on the Kohala Coast of the Big Island.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Beach Resort
Experience a relaxing stay in this beautifully updated 1-bedroom condo at Ekahi, located just a 3-minute walk from the renowned Keawakapu Beach. The unit offers stunning ocean views from the quiet lanai, where you can unwind listening to the waves, watch whales during the season, and enjoy breathtaking sunsets.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Pathway of the Gods, or Kealakekua Bay, holds deep historical significance for ancient Hawaiians. Just 500 feet from this iconic bay, Hale Hanai offers a cozy, family-friendly retreat surrounded by lush palm trees, fragrant plumeria blossoms, bananas, and coconuts.
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Captain Cook
Aloha and welcome to your perfect Maui escape! This beautifully renovated 1-bedroom condo in South Kihei offers a seamless blend of comfort and island charm, ensuring a memorable stay. The unit was completely gutted and remodeled, featuring vibrant "beachy" colors and modern furnishings that instantly make you feel at home.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Serenity, a spacious oceanfront home offering breathtaking views of the sea and sky. Watch whales, dolphins, and sea turtles from your living room in this Hawaiian paradise. The home exudes elegance with an open, light-filled design, modern furnishings, and impeccable details.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
Discover this spacious two-bedroom, two-bath townhome in the tropical complex of Nihilani at Princeville, offering a perfect retreat on Kauai's lush North Shore. The 1,400-square-foot home features an open floor plan with a comfortable living room equipped with a large flat-screen TV and Wi-Fi, a fully equipped gourmet kitchen with granite countertops, and a spacious dining area with an oak table for six.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience tranquility and comfort at Mountain Retreat, a premier one-bedroom condo located in the Waikiki Banyan complex. This well-maintained unit offers exceptional quality, breathtaking Diamond Head ocean views, and a peaceful atmosphere perfect for relaxing after a day exploring Waikiki and Honolulu. Registration Number: TA-077-720-3712-01, GE-077-720-3712-01, 260250050845
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Immerse yourself in paradise with this beautifully remodeled 1-bedroom condo on the 30th floor of Waikiki Banyan. Boasting breathtaking ocean vistas, the largest floorplan in the building, and a wealth of amenities, this unit is perfect for a memorable Hawaiian escape.Registration Number: 1260250050324, GE-109-088-3584-02, TA-109-088-3584-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Keauhou Palena, your perfect vacation retreat located just minutes from the vibrant Kailua-Kona Village. This charming property offers easy access to shopping, dining, and entertainment options, including the Keauhou Shopping Center, which features shops, restaurants, a large supermarket, a farmers market every Saturday, a cinema, banks, a medical clinic, a drugstore, a post office, and gas station.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Discover this charming private unit nestled in a lush open yard filled with fruiting trees and vibrant flowering shrubs. The property offers a dedicated serene outdoor space complete with a picnic table, bench, and grill, ideal for relaxing meals and outdoor gatherings.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Hilo
Aloha! Welcome to Maui Vista 1315, a luxurious and beautifully decorated condo offering a tropical retreat in South Maui. This air-conditioned unit features a comfortable king-sized bed, perfect for couples or small families, and a sofa bed suitable for children 10 and under.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to our spacious 5-bedroom modern retreat located on Oahu's beautiful west shore. This property is a top/bottom duplex with separate entrances, rented as a whole home for your privacy and comfort. The upstairs features three bedrooms and two bathrooms, including a cozy queen with twin bunk, a king bed, and a twin plus twin trundle with a crib. Registration Number: 870170380000, GE-186-733-1854-01, TA-186-733-1854-01
6 bedroom
3 bathroom
Sleeps 14
Hawaii> Waianae
Escape to this charming tropical-themed condo located in Maui Vista Building #3, a favorite among long-term residents for its peaceful setting away from road noise. Designed to comfortably sleep 2-4 guests, it features a cozy queen-sized bed in the bedroom and a convertible double sofa bed in the living room, making it ideal for couples or small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your inviting vacation retreat in Kona, Hawaii, nestled along the scenic Great Ali'i Drive. This charming top-third-floor one-bedroom condo offers a perfect blend of comfort and convenience for your Hawaiian escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
This beautifully maintained ground-floor condo at Waikoloa Beach Villas offers a perfect retreat on the Big Island. Managed professionally by ResorticaHawaiiWaikoloa, this property combines comfort, convenience, and luxury.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Enjoy a memorable Maui vacation at Island Surf 506, just steps from the warm turquoise waters of Kalama Beach. This bright, spacious condo is perfect for couples, small families, or friends seeking a relaxing escape. The interior boasts wood flooring and stylish furnishings, creating a welcoming atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Na Hale O Keauhou F1 is a beautifully maintained townhouse-style condominium nestled in a gated community in Keauhou, on the stunning Big Island of Hawaii. This spacious 2-bedroom unit offers a perfect blend of comfort and convenience for your island getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Enjoy a beautifully remodeled oceanfront studio at Napili Shores Building A 204, offering breathtaking views of Napili Bay. Located just steps from the renowned white sand beach, this unit provides the perfect setting for a relaxing Maui vacation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Napili
Wake up to the tranquil sound of waves crashing on the pristine white sand beach, just steps from your door. This secluded 3-mile stretch offers endless opportunities for leisurely walks, refreshing swims, or simply relaxing in paradise. Located on the top floor of the main building at Kauai Beach Resort, this suite provides the most spectacular views in the entire resort, including stunning pool and ocean vistas that can be enjoyed from both the suite and the spacious lanai or balcony.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lihue
Unit C-15 at Napili Point is a beautifully remodeled, top-floor 2-bedroom, 2-bath oceanfront condo offering stunning views of the Pacific. Newly equipped with air conditioning and a modern kitchen with marble countertops, it provides a luxurious and comfortable Maui retreat.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This premier one-bedroom, one-bath condo at Kihei Surfside offers an exceptional Maui vacation experience. Located just 70 feet from the ocean, this upgraded unit boasts breathtaking views of the sea and seasonal whale sightings from your private lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your ideal Kauai getaway in this spacious 1600 sq ft townhouse nestled within the lush Poipu Kai Resort. Perfectly positioned just a five-minute walk from Brenneke's Beach and a few minutes further to Poipu Beach Park, this beautifully furnished home offers sweeping distant views of the ocean and mountains.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Poipu
Welcome to your tropical escape in South Maui, where comfort meets convenience in this beautifully appointed condo located within the lush Koa Resort. Designed to accommodate up to six guests, this spacious unit offers a perfect blend of luxurious amenities and natural beauty.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover this beautifully renovated 2-bedroom, 2-bathroom oceanfront condo situated on the top floor, offering breathtaking views of whales, dolphins, and stunning sunsets. The spacious lanai provides an ideal spot to unwind and take in the natural beauty of the coast.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Waiohuli Beach Hale B-208, your peaceful Maui escape in an oceanfront paradise! Located upstairs in this charming, low-key complex, B-208 has been beautifully remodeled with granite countertops, tiled floors, modern cabinetry, and a fully equipped kitchen featuring a dishwasher for easy island living.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the natural beauty and tranquility of Kauai's North Shore at Pali Ke Kua 1. This charming, newly renovated 2-bedroom, 2-bath condo offers a perfect blend of comfort and convenience for your island escape.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Experience the perfect Maui getaway in this spacious, beautifully remodeled two-bedroom, two-bath condo at Maui Sunset in Kihei. Renovated in 2015, the unit features granite countertops, custom kitchen and bathroom vanities, and modern appliances, providing a luxurious and comfortable stay.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Keauhou Kona Surf & Racquet Club #6-101, a beautifully remodeled ground-floor corner unit offering 1,188 sq ft of comfort and style. This desirable condo features 2 bedrooms and 2 bathrooms, with excellent cross breezes that keep the space airy and cool throughout the day.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the ultimate Maui getaway in this beautifully renovated oceanfront condominium located at the Kahana Outrigger Resort. Perfectly suited for families or groups of up to eight, this three-bedroom, two-bathroom unit offers 1,490 square feet of comfortable living space plus an additional 100 square feet of lanai, providing ample room to relax and enjoy the stunning ocean views.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
This luxurious resort condominium offers breathtaking views and exclusive access to Waikoloa Beach Golf Course. Located on 30 acres of oceanfront property, this 3-bedroom, 3-bathroom unit combines comfort and elegance for an unforgettable Hawaiian getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Beach Resort
Experience the stunning beauty of Kauai's North Shore from this newly remodeled luxury townhouse located right on Makai Golf Course. This 2-bedroom, 2-bathroom residence offers an elegant and comfortable retreat in the heart of the Princeville Resort, adjacent to the renowned Westin Princeville Ocean Resort Villas.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the laid-back charm of Kailua-Kona at Casa Tropical Kona, a beautifully styled one-bedroom condo nestled in Kailua Bay Resort. This inviting home offers a seamless blend of comfort and Hawaiian elegance, perfect for your island escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the ultimate luxury in this stunning beachfront resort home located right on the beautiful Hawaiian coast. Just steps from the warm turquoise ocean, this spacious property offers a perfect blend of comfort, style, and amenities for a memorable getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapa?a
Immerse yourself in the ultimate Maui vacation experience at Mana Kai 810, a luxurious top-floor oceanfront condo in South Kihei. This beautifully appointed 2-bedroom, 2-bathroom unit combines modern comfort with the warm Aloha spirit, offering stunning ocean views from nearly every room.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This exquisite Kauai vacation home offers breathtaking views of the ocean, mountains, and the Makai golf course, situated on the North Shore near Hanalei Bay. The expansive 1200 sq ft covered lanai is the heart of the property, featuring dining areas, high-top bar tables, a BBQ grill, an intimate conversation pit, and an elevated hot tub that provides a truly unique experience.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
Sweeping views of the ocean and Diamond Head welcome you from this beautifully renovated 1-bedroom condo at Waikiki Sunset Hotel. Perfectly situated just two blocks from Waikiki Beach, the International Marketplace, and top shopping and dining spots, this location offers the ultimate Hawaiian vacation experience.Registration Number: TA-027-765-7600-01, GE-027-765-7600-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hale Mano, also known as The House of Sharks, a charming 3-bedroom, 2-bath home built in 1963 from solid California redwood. Nestled on a quiet residential street, this well-curated retro beach house offers a perfect escape from typical resorts.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Experience the beauty of Mauna Lani Terrace E201, a charming one-bedroom villa offering breathtaking ocean views over peaceful ponds. The interior has been thoughtfully upgraded, featuring elegant granite countertops and sleek stone floors that create a sophisticated yet welcoming atmosphere.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kamuela
Discover the best location at Na Hale O Keauhou, a beautiful gated complex offering spacious luxury 2 and 3-bedroom townhomes with breathtaking ocean views. The community features a resort-style pool, relaxing spa, clubhouse, and a fully equipped fitness facility, ideal for a relaxing getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover this beautifully remodeled first-floor corner unit at Kona Reef, offering direct oceanfront views and one of the largest living spaces in the complex. This exclusive unit features two full bathrooms and abundant windows that flood the space with natural light, creating a bright and inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to your perfect Maui escape in our luxurious oceanfront villa, designed for ultimate relaxation and comfort. This beautifully appointed one-bedroom property offers stunning views of the ocean and islands, making it an ideal retreat for couples seeking a romantic getaway or a peaceful vacation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience the ultimate Kauai vacation at this exceptional oceanfront estate located on the dramatic Poipu Beach coastline. This distinctive five-bedroom residence offers spectacular ocean views, unique architecture, and spacious living areas designed to maximize the breathtaking vistas of the Pacific.
6 bedroom
6 bathroom
Sleeps 12
Hawaii> Koloa
Experience the ultimate Kauai getaway at Turtle Haven, a beautifully situated oceanfront condo just steps from the sea near Poipu. This second-floor unit offers spectacular views of the tropical Pacific Ocean, with floor-to-ceiling windows and a spacious lanai perfect for relaxing while listening to the crashing surf.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Experience the perfect Hawaiian getaway in this spacious, bright 2-bedroom, 2-bathground floor condo at Makanui in Poipu Kai Resort. The unit offers a modern, upgraded kitchen with granite countertops, stainless steel appliances, and beautiful wood cabinetry, making meal prep a pleasure.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Escape to Maui's Sugar Beach Resort 225, a beautifully appointed beachfront condo offering breathtaking oceanfront views and direct access to five miles of pristine white-sand beach. Whether you're seeking a relaxing retreat or an active holiday, this condo provides the perfect home-away-from-home experience.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Kihei Sands Beachfront Condominiums B8, a beautifully situated oceanfront condo perfect for your Maui getaway. This spacious 2-bedroom, townhouse-style unit on the second level offers breathtaking views of Haleakala, West Maui Mountains, and the vast Pacific Ocean.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Maui getaway in this beautifully renovated one-bedroom, two-bath condo nestled in the heart of Wailea. Located on 33 acres of lush terraced grounds, the property offers breathtaking ocean views and a serene atmosphere. Just a short stroll from the pristine Keawakapu Beach, you'll enjoy easy access to white sandy shores and the gentle sounds of the ocean.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a tropical retreat at Beach House 144, a beautifully decorated and furnished 1-bedroom, 1-bath condo offering a home-away-from-home ambiance. As a family-owned property, we pride ourselves on meticulous maintenance and personalized touches that enhance your stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Located in the gated Waikoloa Colony Villas in the heart of Waikoloa Beach Resort, unit 1303 offers a perfect retreat for families or couples seeking a relaxing Hawaii vacation. This townhouse-style condo features a spacious master bedroom with a king bed, a guest bedroom also with a king bed, and a comfortable living room with a sleeper sofa, accommodating up to six guests comfortably.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Beach Resort
Aloha! Welcome to this spacious, second-floor, 2-bedroom, 2-bathroom tropical retreat at White Sands Village Condominiums! be/37QtTgWOpoA (please note, hyperlink not supported due to VRBO glitch). Located in a prime spot just across from White Sands Beach, also known as Magic Sands Beach, this condo offers a perfect blend of privacy, tranquility, and natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
