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 3950, 50
Discover this stunning 3-bedroom, 3-bath townhouse situated in the desirable Nihilani complex in Princeville. The spacious and inviting layout offers all the comforts you need for a memorable family vacation. Throughout the home, you'll find air conditioning to keep you cool, a fully equipped kitchen perfect for preparing meals, and a large living area designed for relaxation and quality time.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
This stunning Hawaii Kai residence offers a perfect blend of comfort and convenience for your Hawaiian escape. Nestled in a peaceful neighborhood with direct access to the marina, this home provides a luxurious haven just minutes from top attractions.Registration Number: TA-042-034-1248-01, GE-042-034-1248-01, 390320580000
4 bedroom
2 bathroom
Sleeps 8
Hawaii> honolulu
Welcome to G110, a highly sought-after, beautifully renovated ground-floor studio in the Maui Eldorado resort. This unit offers a perfect blend of privacy, comfort, and stunning views of the golf course and ocean from your lanai. Being in the only single-level building on the property, the G building, ensures a quiet and peaceful atmosphere, similar to a single-family home, with no upstairs neighbors.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
5-bathroom townhome, ideally located in the lush, tropical oasis of South Puamana—untouched by the 2023 Lahaina fire. This peaceful location offers quick access to the nearby beach, ocean, and Puamana’s popular dolphin pool.
3 bedroom
3 bathroom
Sleeps 4
Hawaii> LAHAINA
Aloha and welcome to this enchanting Hawai'i Island cottage, set on a lush 1-acre rainforest estate. Accessible through a private electric gate, the property offers a peaceful retreat with a beautifully manicured garden and tropical surroundings.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Discover this stunning nearly 3,000 sq ft luxury home in Nohea at Mauna Lani, Abita Luxury Rentals. This beautifully appointed property offers a perfect blend of comfort, style, and high-end amenities for an unforgettable Hawaiian getaway. The spacious layout includes four bedrooms, each with king beds, ensuring restful nights for all guests.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kamuela
Located in the heart of central Kihei, this charming beachfront 1-bedroom, 1-bath condo offers a tranquil escape in the northern part of Halama Street. As a family-owned and operated property, it provides a genuine Hawaiian experience with all the comforts of home.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Nestled on over an acre in the prestigious Bluffs enclave along the Kohala Coast, The Beach House offers an unparalleled sanctuary of luxury and tranquility where indoor-outdoor living takes center stage. This $15 million, 8,141-square-foot estate redefines Hawaiian elegance with its seamless blend of award-winning green design, breathtaking ocean views, and bespoke craftsmanship.
4 bedroom
7 bathroom
Sleeps 10
Hawaii> Kamuela
Escape to the luxurious Waipouli Beach Resort, where villa F202 offers a perfect blend of elegance, comfort, and Hawaiian charm. 5-bath condo is ideal for families, couples, or friends seeking an unforgettable Kauai retreat. Inside, you'll find a spacious, beautifully designed living space with tasteful furnishings, a flat-screen TV, and high-speed Wi-Fi.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Experience the ultimate Maui getaway at Kanai A Nalu Unit #319, a beautifully remodeled 2-bedroom, 2-bath oceanfront condo offering breathtaking views and modern comforts. Wake up to spectacular sunrises from your private lanai, where you can listen to the soothing sound of the ocean surf and watch seasonal whales play right from your window.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Discover the ultimate Hawaii vacation experience in this stunning 2-bedroom, 2-bath resort villa at Westin Nanea. Spanning 1260 sq ft, this luxurious condo comfortably sleeps up to 6 guests, making it perfect for families or friends. The master bedroom features a plush king-size Westin Heavenly Bed and a HDTV, complemented by a spacious Westin Heavenly Bath with a large shower, vessel tub, and double vanity.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to your personal sanctuary at the Ali'i Kai condominium in the Princeville at Hanalei Resort Community. This second-floor, 2-bedroom, 2-bathroom luxury condo offers nearly 1050 sq/ft of comfortable indoor living space, tastefully furnished to make you feel at home.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the perfect Maui getaway at Maalaea Kai 416, a beautifully remodeled contemporary condo offering breathtaking panoramic ocean views from the lanai and floor-to-ceiling windows on the second floor. The condo's prime location provides easy access to a variety of ocean adventures—snorkeling, scuba diving, SNUBA, bottom fishing, sailing, and sunset cruises—just steps from Maalaea Harbor.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Escape to this cozy studio nestled in the picturesque Manini Beach subdivision, just a two-minute walk from the renowned Kealakekua Bay. Perfect for both adventure seekers and those seeking relaxation, this private retreat offers everything needed for an unforgettable Hawaiian vacation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
Welcome to Kulalani 904, a stunning and spacious 2673 square foot townhome situated within a premier resort in Hawaii. This two-story retreat offers a perfect blend of modern comfort and natural beauty, making it an ideal choice for families, couples, or groups seeking a luxurious island getaway.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waimea
Experience the magic of the Big Island in this luxurious 2-bedroom, 2-bath Mauna Kea Resort property, nestled above the Pacific in the gated Kumulani community.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kamuela
Experience the breathtaking panoramic views of the ocean and mountains from this beautifully furnished condo in Kihei, Maui. Located just a three-minute walk to Kamaole Beach 1, you'll have easy access to snorkeling, boogie boarding, paddleboarding, or relaxing on the sun-kissed shores.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
This beautiful oceanfront corner condominium offers a luxurious and relaxing getaway just steps from the water's edge. With its prime location less than 30 feet from the ocean, every room boasts stunning views of the beach and sea. The spacious, fully air-conditioned unit features a large lanai perfect for watching spectacular sunsets, surfers catching waves, and playful dolphins, turtles, and whales swimming nearby.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Make your Maui dreams come true at Wailea Ekahi 35D! This top-floor, ocean view condo offers a private entrance surrounded by lush lawns, vibrant bougainvillea, and fragrant plumeria trees. Recently remodeled, it seamlessly blends indoor comfort with outdoor island living, featuring a spacious lanai perfect for morning coffee or sunset cocktails while enjoying views of Lanai, Kahoolawe, and Molokini.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailea
Discover the premier Kanaloa at Kona, a luxurious vacation condo nestled in a peaceful residential neighborhood away from traffic, offering abundant tropical landscaping and spacious surroundings. This exceptional property features a large 1300 sqft layout with stunning ocean views, especially from the bedrooms, where you can fall asleep to the soothing sounds of crashing waves.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover our beautifully remodeled and spacious one-bedroom condo at the renowned Kanaloa at Kona Resort, nestled on 18 acres of lush Hawaiian landscape. This ground-floor unit offers nearly 1200 sq ft of comfort, featuring a large master bedroom, a new queen-size Murphy bed, and a queen-size sleeper sofa, accommodating up to six guests.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Aloha, welcome to your beautifully renovated top-floor 2-bedroom, 2-bath condo at Maui Vista. This unit boasts vaulted ceilings and faces Maalaea Bay and the West Maui Mountains, offering spectacular views and natural light.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Step into this beautifully furnished island-style town home at the Villages at Mauna Lani, perfect for discerning travelers seeking luxury and comfort. With 2,785 sq ft of split-level living space, this home comfortably accommodates 6 guests across two spacious master suites and an additional large bedroom featuring two queen beds and a bathroom.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kamuela
The perfect Maui getaway awaits you in this beautifully upgraded condo on Kaanapali Beach. From the tenth floor of Tower 1, just two units back from the oceanfront, you’ll enjoy breathtaking ocean views from almost every room except the bathrooms.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this stunning 2-bedroom, 2-bath condo on the 9th floor of Kaanapali Alii, offering breathtaking partial ocean views and lush West Maui Mountain vistas. The fully renovated corner suite boasts upgraded tile flooring, modern finishings, and designer furniture, creating a sophisticated and comfortable space for up to four guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Makena Surf C-201, your exclusive Maui retreat. This elegant 1600 sq. ft. condo offers breathtaking panoramic ocean views and is just steps from stunning beaches, pools, and spas. Inside, you'll find two bedrooms with ensuite bathrooms, each with king-size beds and refined Hawaiian furnishings that create a relaxing atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Makahuena 2102, your perfect oceanfront retreat on Kauai's southernmost point. Located on a dramatic bluff, this spacious condo offers breathtaking coastal views and a luxurious, comfortable stay.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Discover the stunning beauty of Maui from Hale Mahina A205, a charming second-floor condo featuring one bedroom and one bathroom, perfect for a memorable island escape. Designed to comfortably sleep up to four guests, this spacious open-concept unit offers a seamless indoor-outdoor living experience.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Aloha! Welcome to your perfect Maui getaway at the Maui Eldorado Kaanapali H105. This beautifully remodeled 1-bedroom condo combines California coastal charm with modern luxury, making it an ideal retreat for couples or families of up to four. Newly decorated and thoughtfully designed, the condo features fresh flooring, a spacious walk-in shower, an open kitchen with new GE appliances, and chic furnishings that create a relaxed yet stylish atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Escape to Maui with exclusive villa options at The Westin Kaanapali Ocean Resort Villas, available now for summer 2025 and beyond. Whether you're seeking a cozy studio or a spacious two-bedroom unit, I can help you secure your perfect Hawaiian getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Maui
Discover this beautifully updated cottage nestled in the peaceful and upscale Kahala neighborhood, offering a serene retreat just minutes from Waikiki and the East Honolulu coast's stunning beaches. With a minimum stay of 30 nights, this property is ideal for couples or families seeking a relaxing Hawaiian getaway. Registration Number: TA-108-852-8384-01, GE-108-852-8384-01, 13526023
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
Discover this charming 29th-floor studio in the heart of Waikiki, perfect for couples seeking breathtaking ocean and city vistas. The unit comfortably accommodates two guests with a king-size bed, a sofa, and modern amenities designed for a relaxing stay. Registration Number: 260230560191, GE-170-980-7104-01, TA-170-980-7104-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the perfect Hawaiian getaway at our Hali'i Kai villa, offering two private suites within a beautifully manicured gated community. Wake up to morning birds and gentle breezes on your secluded lanai, free from roads and parking lots, with stunning partial ocean and full golf course views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience the perfect Hawaiian retreat in this beautifully situated villa overlooking the Pacific Ocean, Mauna Kea, and Hapuna Resorts. 25-bathroom residence offers a blend of elegance, privacy, and natural beauty. As you arrive through the gated community and park in the driveway, you'll step into a tranquil oasis designed for relaxation and enjoyment.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Discover the ultimate beachfront living experience at Ko Olina Beach Villas B602 by Gather. This luxurious two-bedroom, two-bathroom villa, located on the sixth floor, offers breathtaking ocean views and space for up to six guests. Situated on the peaceful west side of Oahu, it embodies the perfect blend of comfort, elegance, and island charm. Registration Number: 910570090211, GE-118-968-4224-01, TA-118-968-4224-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Experience the perfect Maui vacation in this spacious, newly renovated 2-bedroom, 2-bathroom condo at Royal Kahana Resort. With an open floor plan, separate sleeping areas, and modern comforts, it offers both privacy and social spaces for your family or friends.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui retreat in this beautifully renovated ground floor condo in Maalaea. Enjoy cool island breezes and stunning ocean vistas from your private space. The open-layout design features one bedroom and one bath, creating a spacious and inviting atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuku
Escape to paradise in this beautifully renovated condominium nestled within the exclusive Kapalua Resort on west Maui. This first-class residence offers unparalleled views of the lush landscape and ocean, making it the perfect retreat for discerning travelers.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Hale Lani, a breathtaking hillside retreat perched atop the lush northeastern coast of Oahu, Hawaii. This luxurious 3-bedroom, 4-bathroom home offers a serene escape with stunning views of Lanikai Beach and the Mokulua Islands. Perfectly situated just outside the charming town of Kailua, Hale Lani combines natural beauty with modern comfort, making it an ideal destination for families, friends, or couples seeking a memorable Hawaiian vacation.Registration Number: TA-168-604-3136-01, 430060650000, GE-168-604-3136-01
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Welcome to Kona Alii, your ideal vacation spot nestled in the vibrant heart of Kona town. This freshly furnished 1-bedroom, 2-bathroom corner unit on the 4th floor combines comfort and convenience, perfect for both relaxing and exploring the island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
The Maui Beach House offers a luxurious and peaceful retreat on a calm, private bay, perfect for relaxing and enjoying Maui's natural beauty. This stunning two-story property boasts over 4,000 square feet of interior and deck space, all designed to maximize ocean views and comfort.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Lahaina
Discover Tutu House - Lanikai 222, a charming beachfront condo nestled in the lush surroundings of Lanikai. This spacious retreat offers stunning ocean views, a shared beach-view pool, and direct access to soft sands, making it an ideal spot for relaxation and fun.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Welcome to Hale Kai Uli, the House of the Deep Blue Sea, nestled in sunny Kihei, Maui. Just 500 feet from lanai to toes-in-the-ocean, this charming 2-bedroom, 1-bath condo offers a perfect blend of comfort and tropical beauty. Recently renovated in October 2016, the condo features all new furniture and upgraded amenities, including windows and split AC units installed in November 2021 for optimal comfort.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Banyan Tree #204, a beautifully situated oceanfront condo offering uninterrupted views of the Pacific Ocean. Spanning 1200 sq ft, this unit features floor-to-ceiling glass walls that invite in stunning vistas of the ocean and allow easy access to a covered lanai where you can enjoy spectacular sunsets, whale watching, and dolphin sightings.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Puu Poa 307 offers a perfect blend of tranquility and luxury with breathtaking panoramic ocean and mountain views, just steps from the beach and amenities. Inside, the condo has been thoughtfully renovated, featuring an expansive gourmet kitchen with gorgeous granite countertops, modern appliances, and all necessary cookware and amenities to prepare memorable meals or cocktails.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the perfect blend of comfort and convenience in this charming 1-bedroom condo located at Kiahuna Plantation Resort. This second-floor unit offers a serene retreat with a private lanai that overlooks lush plantation grounds, providing a peaceful setting just a five-minute walk from the pristine sands of Kiahuna Beach.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Aloha! Escape to this stunning beachfront condo in Kihei, Maui, offering breathtaking ocean views and a peaceful retreat just minutes from vibrant local spots. Located on the 4th floor, this owner-hosted property ensures a seamless, personal experience without dealing with unresponsive management.
4 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
This charming end unit condo is situated on the first floor at the back of a lush Hawaiian resort, offering serene garden views and tropical breezes from a spacious lanai. Recently renovated, the condo features a comfortable master bedroom with a king-size bed and a main living area equipped with a wall bed featuring a queen-size Tuft and Needle mattress, making it ideal for both couples and small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your perfect Maui getaway at the Honua Kai Resort and Spa, nestled in West Maui's North Ka'anapali Beach, one of the world's top ten beaches. This spacious 915 sq. ft. condo features a versatile bedroom and den setup that comfortably sleeps six, making it ideal for families or groups seeking luxury and comfort.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kaanapali
Aloha! Thank you for your inquiry. This is a timeshare property with multiple units available, so if the calendar shows blocked dates, please inquire—there may be other options! These resorts are exceptional, featuring Heavenly Beds with luxurious comforters and bath products.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
