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 6700, 50
Welcome to your perfect Maui getaway! This beautifully renovated oceanfront condo offers breathtaking water views right from your lanai, making it an ideal spot to unwind and soak in the island’s natural beauty. Located on the second floor, the condo provides direct ocean vistas where you can watch turtles, whales (seasonal), boats, and vibrant sunsets.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
This charming ground floor condo at Kiahuna Plantation offers a prime location in beautiful Koloa, Kauai. The unit features a stunning garden view, overlooking twin bridges with lush landscaping filled with tropical flowers like birds of paradise, plumeria, and azaleas.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Kona Bubbles is a beautiful, custom-built home situated in the secure gates of Meilani View Estates with breathtaking ocean vistas just above Kailua Kona. 5-bath residence offers nearly 3,000 sq ft of elegant living space, including a detached guest suite and garage.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua Kona
Experience the ultimate beach getaway in this spacious sixth-floor, 2-bedroom, 2-bath oceanfront condo. The unit features an extra-large lanai with a patio dining table, four chairs, and two loungers—perfect for relaxing while soaking in panoramic ocean views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
The Palms at Wailea #1305 offers a stunning retreat on Maui's sunny southwest coast. This spacious 2-bedroom, 2-bath condo is air-conditioned and thoughtfully furnished for comfort. From the expansive lanai, guests are treated to breathtaking views of the ocean to the west and the majestic Haleakala volcano to the east, all surrounded by vibrant, colorful gardens that create a serene atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea-Makena
Welcome to Davidson Point Beach House, a serene oceanfront retreat on Kauai's west side. Nestled on a spacious estate-sized lot, this luxurious property offers breathtaking views of Ni'ihau and stunning sunsets. The property features covered lanais with tranquil ocean vistas, leading to a beautifully landscaped backyard adorned with fruit trees and a private heated saltwater pool measuring 14x28 feet, perfect for relaxing or entertaining.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kekaha
Discover the ultimate Hawaiian escape in this stunning top-floor penthouse at Waipouli Beach Resort. Unit D406 offers unparalleled ocean views from every room, perched centrally over the resort’s pool and lazy river. Only two other penthouses boast direct ocean vistas, making this property a rare find.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Experience the ultimate Kauai vacation at Hanalei Bay House, a stunning 5-bedroom residence located just steps from the world-famous Hanalei Bay and a short stroll to charming village shops and eateries. This expansive home offers a perfect blend of comfort, style, and natural beauty, ideal for families or groups seeking a memorable island escape.
5 bedroom
5 bathroom
Sleeps 11
Hawaii> Hanalei
Our privately-owned, 1-bedroom, 2-bath second-level condo in Wailea Ekahi has been recently updated with custom-built-ins, new electronics, appliances, and air conditioning. Located in building 13 on a tranquil green belt, it offers easy access to the main pool and stunning Keawakapu Beach, just a 3-4 minute walk away.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Feel at home in this stunning beachfront condo located directly on the beautiful shores of Hawaii. This ground-floor unit offers easy access to the beach, with the sound of the surf and breathtaking sunrise and sunset views right from the lanai.
2 bedroom
2 bathroom
Sleeps 2
Hawaii> Kapa?a
Welcome to Hale Kahakai in Ko Olina, a charming tropical retreat nestled amidst lush gardens and tranquil koi ponds, offering an ideal escape for vacationers. This beautifully furnished unit combines modern comfort with island charm, featuring a sleek kitchen, plush lounge areas, and vibrant beach decor that creates a bright, airy ambiance perfect for relaxing or entertaining. Registration Number: 1910570090015, GE-069-771-4176-01, TA-069-771-4176-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Immerse yourself in the beauty of Maui with this luxurious condo offering a unique double master suites layout, each with its own ensuite bathroom for maximum privacy and comfort. The tastefully decorated interiors blend modern elegance with island-inspired decor, creating a tranquil and inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Located in the prestigious heart of Wailea, this stunning 3-bedroom villa offers unparalleled ocean views and luxurious comfort. Just a short walk to some of Maui’s most beautiful beaches, this home is perfect for families, friends, or couples seeking a relaxing getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to the charming Captain's Quarters studio cottage in Kamuela, situated on a hillside above the town. This unique architectural retreat offers a cozy space with a deck that seamlessly blends indoor and outdoor living. Located in the heart of Parker Ranch, a historic and lush cattle country, the cottage provides stunning views and a tranquil atmosphere.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Waimea
Discover the perfect Hawaii vacation at Waikoloa Colony Villas 2705, a spacious townhouse-style condo nestled in a gated community on the Kohala Coast. This rental offers a relaxing retreat with modern amenities and access to fantastic resort facilities.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Beach Resort
Hale Keaukaha is a stunning and elegant home built in 1937, ideally located across from Richardson's Beach Park in Hilo. This black sand beach is perfect for snorkeling, surfing, swimming, kayaking, and sunbathing. Just minutes from downtown Hilo and the airport, the location offers both convenience and picturesque views, making it an ideal spot for an authentic Hawaiian getaway.
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Hilo
Escape to Konea 415, a luxurious and peaceful studio nestled in the heart of Honua Kai on Maui's pristine shores. Perched on the 4th floor, this elegant suite offers breathtaking ocean vistas and a sheltered retreat from the trade winds, making it the ideal spot for your next island escape.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience ultimate island luxury at Luana Garden Villas, part of the prestigious Honua Kai Resort on Maui. This spacious 3-bedroom villa offers 2,047 sq ft of elegant living space, located on the upper floor of building 3 in Enclave One. Step onto the expansive 521 sq ft lanai, complete with a private outdoor kitchen, perfect for enjoying tropical drinks and outdoor dining while overlooking the secluded pool area.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kaanapali
Welcome to Hale Alana, your tropical getaway in Wailea, Maui. Nestled on a lush half-acre, this Polynesian-inspired home combines island charm with modern luxury. The property features three inviting bedrooms, including two tranquil primary suites, offering ample space for up to six guests.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate Maui vacation in this beautifully remodeled 1-bedroom condo located in Kihei, just a short walk to the famous Kamaole Beach Park II. This unit is one of only 14 with vaulted ceilings, offering a spacious and airy atmosphere.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Wailua Bay View Unit 306 offers a luxurious oceanfront escape on Kauai's east coast. This top-floor condo features breathtaking views of the ocean and Wailua Beach from its private lanai, perfect for relaxing or dining al fresco. Recently renovated, the unit is limited to two adults, ensuring a peaceful and intimate getaway without children or pets.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapa?a
This beautifully managed vacation rental at Mauna Kea offers an ideal Hawaii escape with breathtaking ocean views from the hillside location in the Hapuna side of the resort. The ground-floor condo in the D Building features a comfortable master bedroom with a king bed, a guest bedroom with a queen bed, and a versatile den equipped with a fold-out queen Murphy bed, accommodating families or groups comfortably.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Mauna Kea Resort
Discover this beautiful ocean-view condo nestled in a private gated community on the Kohala Coast. Featuring two bedrooms and two bathrooms, this home offers everything needed for a relaxing getaway. The fully equipped kitchen makes meal prep effortless, while resort-style beds and plush furniture in the living area and on the private wrap-around lanai provide comfort and space to unwind.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Experience a charming and spacious Garden Hale Apartment nestled on two lush acres on the sunny side of Waimea (Kamuela). This peaceful retreat offers a perfect base for exploring the Big Island's numerous points of interest while providing a private sanctuary to relax and enjoy 'Hawaiian Time'.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kamuela
Welcome to Hololani! In the Hawaiian language, it means 'gone to heaven,' and that's exactly how you'll feel during your Kauai vacation in this charming and cozy hideaway. This fabulous one-bedroom unit is located on the ground floor of a newer home in Poipu and offers convenient access to two lanais and a private tropical garden, perfect for relaxing and enjoying the lush surroundings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Poipu Kai
Experience the perfect Maui getaway at Hale Kamaole #318, a spacious and beautifully appointed 2-bedroom, 2-bath condo on the third floor offering partial ocean views. This unit is ideal for families or couples seeking comfort and convenience, with a fully-equipped kitchen, washer and dryer, free Wi-Fi, and phone service including long-distance calls to the US and Canada.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this cozy, recently renovated studio apartment perfect for 2-3 guests, located just a short 5-8 minute walk from the beautiful Hukilau Beach. The space offers a comfortable environment with a cook-top and essential kitchen appliances, making it ideal for those who enjoy preparing meals at home. Registration Number: TA-018-462-4128-01, GE-018-462-4128-01, 550160390001
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Laie
Welcome to Kamilo 321, the epitome of island luxury and comfort at Mauna Lani. This spectacular single-story home features 2,100 sq. ft of beautifully upgraded living space complemented by a 550 sq. ft covered lanai, perfect for outdoor entertaining and relaxation.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kamuela
Welcome to Hale Kalani, a thoughtfully curated oasis on Kauai's lush North Shore. This charming 3-bedroom condo offers a perfect blend of comfort, style, and sustainability, making it an ideal retreat for families, friends, or couples seeking a tropical escape.
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Aloha and welcome to Maui! Konea #325 is among the finest 3-bedroom condos at the prestigious Honua Kai Resort. Situated on the third floor, this condo offers breathtaking panoramic views over lush resort grounds, sparkling pools, and the iconic Ka'anapali Beach.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Konea 614 offers a luxurious and spacious one-bedroom suite nestled within the beautiful Honua Kai Resort on Maui's North Beach. This elegant residence boasts expansive views of the West Maui Mountains, providing breathtaking sunrises and occasional afternoon rainbows that paint the sky in vibrant colors.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Elegant Escape at the Residences of Laule'a, part of our Hawaiian Luxury Collection, brought to you by Paradise in Hawaii Luxury Homes. This exquisite property features a private pool and spa with breathtaking views of the ocean, golf courses, and Mauna Kea.
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Kamuela
Immerse yourself in tropical serenity at Pili Mai 15K, a stunning condo nestled in the heart of Poipu. Set within the scenic Pili Mai community, this property offers breathtaking views of the Kiahuna Golf Course, lush mountains, and the vibrant South Shore landscape.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Experience the comfort of our spacious Wailea Ekahi 1-bedroom, 2-bath condominium, thoughtfully appointed for your vacation. This top-floor unit features tropical decor, two private lanais, and breathtaking views of the ocean and West Maui mountains.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Maui escape in this luxurious oceanfront penthouse at Sands of Kahana. Boasting two bedrooms, two bathrooms, and two lanais, this spacious condo offers breathtaking year-round sunsets and spectacular ocean views. The property is fully remodeled and comes fully furnished, including two king-sized beds, two sleeper sofas, a fully equipped kitchen, washer and dryer, and air conditioning for your comfort.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the best of Kauai at Kiahuna 17 Beach House, one of the few units offering stunning panoramic ocean views, prime location in building 4, and direct access to a pristine white sand beach. This condominium is set on 35 acres of meticulously maintained lawns and tropical gardens, creating a lush, fragrant environment that delights the senses.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Aloha! Hale Mana Kai offers a warm, inviting atmosphere designed for relaxation amid the beauty of our island. The spacious Master Suite features a king bed, ocean views, cable TV, a work desk, comfortable chairs, a walk-in closet, double sinks, an open shower, and a large tub, providing a luxurious retreat.
4 bedroom
7 bathroom
Sleeps 8
Hawaii> Koloa/ Poipu
Discover this beautifully updated 2-bedroom condominium at the Ilikai, offering breathtaking views of the Pacific Ocean, Duke Kahanamoku Lagoon, and Ala Wai Yacht Harbor from every room. , features a tropical decor theme with koa wood plank flooring, Shoji-Hawaii room divider, and modern furnishings added in 2021. Registration Number: TA-121-213-9520-01, GE-121-213-9520-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Welcome to Surfbreak at Maui Banyan Resort, your ideal tropical retreat perfect for couples or small families. This charming 1-bedroom, 1-bath garden-view condo comfortably accommodates 2 to 4 guests, offering a blend of comfort and convenience in a prime location.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Immerse yourself in a beautifully renovated second-floor condo in Wailea Elua Village, offering stunning ocean views and contemporary island-style decor. This two-bedroom, two-bath unit spans 1,336 square feet and is designed for comfort and relaxation.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Wailea, Maui
Discover this charming first-floor corner condo in the heart of Maui, just a short stroll to the iconic Ka'anapali Beach and Whalers Village. The unit offers easy access to a serene garden area, separating you from the pool and jacuzzi, perfect for relaxing after a day at the beach.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Lahaina
This spacious two-level condo is a true tropical paradise located on Kauai's sunny south shore in Poipu. Unique among local condos, it features air conditioning both upstairs and downstairs, ensuring comfort in every season. As the only resort in Poipu directly on a sandy beach, guests enjoy unparalleled access to the beach without the hassle of rocky shores or distant parking areas.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Welcome to our charming one-bedroom condo located in the popular south Maui area at Kihei Akahi Resort! The kitchen is thoughtfully stocked with pots, pans, utensils, silverware, and dishes, making meal preparation a breeze. For your beach days, we provide beach chairs, umbrellas, towels, boogie boards, and snorkeling gear, ensuring you're ready for fun in the sun.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Maui getaway in this newly remodeled 2-bedroom, 2-bath condo located at Maui Kamaole. Spanning approximately 1200 sq. , this condo offers a luxurious and comfortable stay with beautiful garden views and a large private lanai.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate Hawaiian retreat in this luxurious oceanfront studio at Napili Shores. Located right on the popular Napili Bay Beach, this second-floor unit offers breathtaking views of Lanai and Molokai islands, with only a lush lawn separating you from the sparkling ocean.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience the ultimate island getaway in this beautifully decorated second-floor condo in Sealodge, Princeville, Kauai. Featuring handcrafted Hawaiian hardwood accents and vibrant beach-inspired decor, this condo offers stunning views from a cliff overlooking the largest barrier reef in Hawaii, just minutes from Hanalei Bay.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Princeville
Discover this bright and airy garden unit in Maui Kamaole, offering a spacious and comfortable retreat in South Kihei. This large, well-maintained 1-bedroom, 2-bath condo is fully furnished with thoughtful touches to ensure a relaxing stay. The fully equipped kitchen features many nonstandard items, perfect for preparing meals.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
D102 is a beautifully decorated 1-bedroom, 1-bathroom condo located in the heart of Kihei. The unit features a king-size bed and a walk-out lanai that opens directly onto lush parklands, tennis courts, and the ocean, offering stunning views and easy access to outdoor activities.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Konea 306 offers a spacious two-bedroom, one-bathroom suite set amidst the stunning scenery of West Maui, with breathtaking views of the Pacific Ocean and lush mountains. The suite's versatile layout includes a den converted into a second bedroom, providing privacy and comfort for up to six guests.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui getaway in this beautifully renovated 1750 sq ft oceanfront townhouse at Kapalua Bay Villa 33B3. Every room offers breathtaking ocean vistas, creating a perfect backdrop for your vacation.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
