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 9000, 50
Experience the perfect Maui getaway at Ma'alaea Surf, Unit H2. Located just five steps from the pristine white sandy beach, this beautifully appointed ground-floor condo offers both comfort and convenience. Inside, you'll find a contemporary kitchen and a spacious living area that opens to stunning ocean vistas.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience a luxurious escape at Ko Olina Beach Villas B403 by Gather. This elegant three-bedroom, three-bath vacation condo on the fourth floor offers spacious comfort for up to eight guests. Nestled in the scenic Ko Olina community, the villa features stunning ocean views visible from your private lanai, where you can relax with a drink and enjoy the serene surroundings. Registration Number: 910570090192, GE-155-189-6576-01, TA-155-189-6576-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Discover this exquisite 3-bedroom, 3-bath townhome located in one of Keauhou's newest complexes, offering the perfect blend of luxury and convenience for your Hawaiian vacation. ft of spacious living, this home comfortably accommodates families or groups seeking a relaxing retreat.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
The Ilikai Marina offers a perfect blend of comfort and scenic views, ideally situated within walking distance of local restaurants and shops. Sliding glass doors open to a spacious lanai that provides unobstructed vistas of the yacht harbor and the Pacific Ocean, making it a prime spot to enjoy the breathtaking scenery. Registration Number: TA-004-847-0016-01, GE-004-847-0016-01, 260100020168
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Discover the perfect vacation retreat in this spacious two-bedroom condo located in Kailua-Kona on the Big Island of Hawaii. This end-unit, third-floor condo offers breathtaking ocean views and a peaceful setting. Inside, you'll find a comfortable layout with a king-size bed and two full-size beds, comfortably sleeping four guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover this ideal North Shore Kauai condo, perfectly suited for a romantic escape or a peaceful retreat. Featuring brand-new flooring, stylish bedroom furniture, and crisp white bedding, this spacious condo offers a comfortable and inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to Hale Kai #120, your ideal Maui getaway. This beautifully decorated ground-floor unit offers tropical Hawaiian furniture and artwork, creating a warm and inviting atmosphere. The full kitchen allows for convenient meal preparations, making it perfect for families or couples seeking comfort and convenience.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to our breathtaking vacation rental on the Big Island of Hawaii! Nestled amidst stunning natural beauty, this magnificent property features two panoramic homes, ideal for larger groups, family vacations, retreats, and special events. With a total capacity to comfortably sleep 16 guests, our rental offers ample space and luxurious comfort.
6 bedroom
5 bathroom
Sleeps 16
Hawaii> Waimea
Welcome to Royal Mauian #218, a beautifully remodeled oceanfront condo in Kihei offering a blend of modern comfort and stunning views. This stylish unit features a master bedroom with a king bed, a guestroom also with a king bed, and a second guestroom with two twin beds, making it ideal for couples, families, or friends traveling together.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei, Maui
Discover this beautifully renovated penthouse condo in the heart of Waikiki, Hawaii's top tourist destination. The property offers stunning ocean views and is just steps from the world-famous Waikiki Beach. From the expansive wrap-around lanai, you can enjoy breathtaking sunsets and partial views of the iconic Diamond Head Crater, creating a perfect setting for relaxation and memorable moments.Registration Number: 260030130043, 2600301030043, TA-180-592-2304-01, GE-180-592-2304-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Kihei Garden Estates, arguably the best kept secret in Kihei. Located just a 20-minute drive from Kahului Airport, this end-unit condo offers the perfect blend of comfort and convenience. Situated in the heart of Kihei, the condo benefits from gentle Hawaiian breezes flowing through, thanks to its prime location and design.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
A lush tropical paradise awaits you at this gated estate just steps from Kailua Beach, offering a luxurious mini resort experience. 5M upgrade, Palione Papalani (Hawaiian for "Heavenly Sands") is an impressive 6-bedroom estate set amid exotic gardens and a serene lagoon-style pool with a hot tub. Registration Number: 1990/NUC-1787, 430170580000, TA-206-682-7264-01, GE-206-682-7264-01
6 bedroom
6 bathroom
Sleeps 16
Hawaii> Kailua
Experience the ultimate beach getaway in this stunning Maui penthouse, perched six floors high with sweeping ocean vistas. This 2-bedroom, 2-bath unit offers a luxurious and comfortable retreat with spectacular sunsets from your private lanai. Fall asleep to the soothing sounds of the ocean and wake up to the sight of whales breaching nearby.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Enjoy a comfortable and convenient stay at this charming Waikiki condo located on the quiet Lemon Road, just one block from the beach, Kapiolani Park, the zoo, and the aquarium. The secure building offers peace of mind with an entry fob system, allowing only residents access to their floor, which has only six apartments, enhancing privacy and safety. Registration Number: 260270200067, STR# 2245, TA-023-864-5248-01, GE-023-864-5248-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the charm of Maui in Lokelani B205, a family-owned condo since 1976 that offers a blend of comfort and modern updates. Completely remodeled in January 2017, this spacious unit features over 1100 square feet of living space with two bedrooms upstairs, including a master with a king bed, sitting area, TV, and private balcony overlooking the ocean and neighboring islands of Molokai and Lanai.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this beautifully remodeled (June 2022) 1-bedroom condo at Waikiki Banyan, offering a stunning partial ocean view and Hawaiian coastal decor. Perfect for couples or small families, it features a full kitchen with stainless steel appliances, quartz countertops, and a private lanai with ocean vistas. Registration Number: TA-067-595-5712-01, GE-067-595-5712-01, 260250050678
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the perfect Maui escape at Maui Banyan P106, a charming 1-bedroom, 2-bath apartment ideally located just steps from the beautiful Kamaole Beach Park II. Situated on the convenient first floor, this inviting unit comfortably accommodates up to 4 guests, making it ideal for families or couples seeking a relaxing island retreat.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to Hale Pau'ole, a bright and inviting vacation home nestled in the heart of Volcano Village on the Big Island. This charming property offers a perfect base for exploring Hawaii Volcanoes National Park, with the active Kilauea volcano just minutes away.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Volcano
Discover this luxurious two-bedroom, two-bath oceanfront condo at The Whaler on Kaanapali Beach, Maui. Located on the 11th floor as a corner unit, it offers breathtaking panoramic ocean views and has been recently remodeled to provide a modern, comfortable retreat.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Kiahuna Plantation 322, a beautifully upgraded first-floor condo situated in the heart of Poipu Beach on Kauai. This luxurious unit offers a perfect blend of comfort, style, and accessibility. Inside, you'll find elegant finishes, including quartz countertops and hardwood floors, complemented by a spacious lanai that opens directly onto lush lawns.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover this charming Hawaiian-style beach cottage nestled in the heart of Hanalei Bay. Just a short stroll to the beautiful crescent-shaped beach and only two blocks from the vibrant Hanalei Village, this classic pole house offers a perfect blend of comfort and local charm.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hanalei
Escape to paradise in this stunning 2-bedroom, 2-bathroom condo located at Polo Beach Club in Wailea. Wake up to breathtaking ocean views from your private lanai, perfect for enjoying your morning coffee or watching the sunset. The condo boasts tasteful furnishings, a fully equipped gourmet kitchen with stainless steel appliances, and tile floors throughout, creating a bright and inviting atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Come stay and relax at this stunning quartzite stone-sided custom home nestled on a 10-acre private estate in historic Hawi on the Big Island of Hawaii. Built with love and attention to detail by the owners, this home is perfect for family gatherings and friends' reunions.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Hawi
Welcome to Hale Wailele, your ideal vacation retreat on Kauai's North Shore. This beautiful home offers breathtaking views of the Makai Golf Course, set against dramatic cliff sides, providing a serene and picturesque backdrop for your getaway.
5 bedroom
4 bathroom
Sleeps 12
Hawaii> Princeville
Welcome to your dream Maui escape! This beautifully updated 2-bedroom, 2-bath condo boasts 1,100 sq. ft. of comfortable living space and is ideally located in Kihei, just under 300 steps from the renowned Kamaole Park III Beach. Known for its gentle surf, stunning vistas, and excellent lifeguard services, this beach is a favorite among travelers and locals alike, offering perfect conditions for swimming, snorkeling, or simply relaxing on the soft sands under the Hawaiian sun.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Immerse yourself in this immaculate, newly renovated oceanfront penthouse condo, offering a tranquil and luxurious Hawaii escape. Designed and furnished by Archipelago Hawaii, renowned for elegant interiors, every detail ensures modern comfort and style. Registration Number: GE-166-650-6752-01, TA-166-650-6752-01, 005003008002
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Hauula
Discover this charming 2-bedroom duplex in Hawaii, perfect for families or groups up to five. Unique Craftsman details include stained glass windows and an outdoor shower, ideal after a day at the beach.Registration Number: 290150360000, TA-179-607-3472-01, GE-179-607-3472-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the best oceanfront value in Kihei at Kealia Resort with this beautifully renovated 5th-floor studio unit. Wake up to stunning views of the ocean, West Maui mountains, and Kealia Pond National Wildlife Refuge, all from a comfortable and stylish space.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover this beautifully remodeled condo nestled on the bluffs of Kauai's North Shore at the Cliffs Resort. Perfect for up to four guests, the condo features a spacious master bedroom with a new king-size bed and an ensuite bathroom, providing a private retreat with breathtaking mountain views from the master lanai.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover this highly sought-after unit nestled in the heart of world-famous Kaanapali Beach. The spacious private lanai offers breathtaking ocean views, perfect for Maui sunsets and seasonal whale watching. Inside, you'll find a fully stocked kitchen with stainless appliances, central AC, in-unit washer and dryer, and modern entertainment options including flat-screen TVs, cable, DVD, and CD players, plus free WiFi.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect island escape in this spacious Regency 311 condo located just a short stroll from Poipu Beach. This corner unit offers privacy and lush tropical views, making it ideal for relaxation and adventure.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Koloa
Experience the ultimate Maui vacation in our luxury, spacious one-bedroom condo located in the prestigious Grand Champions resort in Wailea. Situated on the top floor with vaulted ceilings, Unit #22 offers an airy, open feel and stunning ocean views from the bedroom, creating a perfect retreat for relaxation and adventure.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailea
Discover this beautifully renovated condo at Kiahuna Plantation, the only beachfront property on Poipu Beach. Completed in Fall 2023, the unit features a modern kitchen with new appliances, fresh furniture, and stylish upgrades throughout. As an end unit, 176 offers excellent cross breezes and a bright, airy atmosphere, with a partial ocean view from the lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Konea 712 offers an exceptional value at Honua Kai, featuring a spacious two-bedroom, one-bath suite that combines comfort and privacy without the higher price of a standard two-bedroom, two-bath setup. Located on the 7th floor, the suite boasts delightful views of the lush West Maui Mountains, providing a serene backdrop for your vacation.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
The Palms at Wailea 705 offers a luxurious and comfortable retreat on Maui's beautiful south shore. This ground-level, 2-bedroom, 2-bath condo boasts over 1,200 square feet of elegant living space, perfect for families or couples seeking a relaxing getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
The majestic design and high-end features of this stunning Kauai residence can't be fully appreciated without seeing it firsthand. The home's architecture, influenced by Frank Lloyd Wright, showcases a huge skylight, abundant windows, and massive pillars that highlight its grandeur.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Princeville
Escape to the oceanfront Ma'alaea Kai #202, a beautifully decorated condo offering breathtaking ocean views and tropical charm. From May to June 2023, enjoy special summer rates in this serene retreat. The condo features a tranquil bedroom with a plush king bed and a comfortable living area with a sleeper sofa, ideal for relaxing after a day of adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuku
Discover the practical and budget-friendly Maui Vista 1223, a spacious second-floor condo ideally located just across the street from the beautiful Maui beaches. This comfortable one-bedroom, one-bathroom unit sleeps two and offers convenient access to the pool, making it an ideal choice for couples or solo travelers seeking a relaxed Maui getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Escape to Honokeana Cove, a serene oceanfront complex in Napili, West Maui. Unit #207 offers breathtaking views from the lanai, with a king-sized bed and a spacious living area enhanced by a large mirror that creates a sense of openness. Located just a quarter mile from Napili Bay's sandy shores, this private gated property surrounds a secluded cove renowned for its vibrant coral reefs, tropical fish, and green sea turtles, making it an ideal spot for snorkeling enthusiasts.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
Knutson & Associates proudly presents Kona Mansions #124, a beautifully updated condo perfect for your Hawaiian getaway. Recently refreshed in November 2023, this spacious residence features new furniture, fresh paint, and a warm, inviting atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience the ultimate Maui getaway at Kihei Beach #204, a beautifully appointed one-bedroom, two-bath condo situated on the second floor with direct access to the pristine Sugar Beach in North Kihei. This charming unit boasts stunning tile flooring throughout, a fully equipped granite and stainless-steel kitchen perfect for preparing delicious meals, and central air conditioning to keep you comfortable during your stay.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover the epitome of upscale island living in Wailea Maui with this beautifully remodeled 2-bedroom, 2-bath condo in the prestigious Palms of Wailea complex. Spanning approximately 1,200 square feet, Unit 1406 offers breathtaking ocean vistas that immediately set a serene and luxurious tone for your stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Discover this stylish 1-bedroom deluxe condo on the 7th floor of the iconic Ilikai Hotel in Waikiki. Decorated with tropical touches, this spacious 500 sq ft unit features a large 120 sq ft lanai, perfect for enjoying mountain and city views.Registration Number: GE-131-398-0416-01, TA-131-398-0416-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to paradise at Seascape Condo, nestled within the oceanfront Maui Sunset complex in sunny Kihei. This beautifully appointed second-floor end unit offers stunning garden views with a peek of the ocean, just steps from Maui’s warm sands and clear waters.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kihei
Welcome to Makena Surf B305! This exceptional condo offers unparalleled LOCATION and VIEWS, situated in one of South Maui’s most sought-after resorts. Just a five-minute drive from Wailea's vibrant restaurants and shops, Makena Surf provides the perfect balance of seclusion and accessibility, away from resort traffic but close to all the best attractions.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Flanked by the iconic Aliʻi Drive and the rocky shores of the Pacific Ocean, Kona Makai Condominiums offer an idyllic setting for your Hawaiian holiday. Unit 2103 is a beautifully upgraded one-bedroom, one-bath condo featuring a private outdoor lanai with stunning ocean views.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
The Majesty Palm Suite at Palm Hale offers a spacious and comfortable studio experience in the heart of Kona. This unit features two distinct rooms—a kitchenette dining area and a cozy bedroom—making it ideal for relaxation and convenience.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the perfect Maui getaway at Polynesian Shores 112, a spacious ground floor 1-bedroom condo designed for comfort and convenience. This charming unit comfortably hosts up to 2 guests and offers a true island ambiance with tropical foliage and ocean views.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to this spacious 2000-square-foot standalone house in Poipu, Kauai, perfect for families seeking privacy and relaxation. Unlike condos, this home offers complete independence with no shared walls, railings, or communal spaces, ensuring a peaceful retreat for your entire family.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Experience the perfect Maui escape in this upgraded corner studio condo located at Honua Kai, perched above a stunning courtyard filled with tropical flowers, lush trees, and koi ponds. From the lanai, enjoy peek-a-boo ocean views framed by vibrant foliage, creating a serene and picturesque setting.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
