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 14500, 50
Experience the ultimate Maui getaway at the Westin Ka'anapali Ocean Resort Villas. This luxurious studio offers a king-sized Heavenly Bed, a spa-like bathroom with a whirlpool tub, and a cozy living area with a sofa bed. The well-equipped kitchenette features a glass cooktop, microwave, refrigerator, dishwasher, coffee maker, toaster, and blender, perfect for preparing your favorite meals.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Pualani Island Getaway - Main House- 30-night minimum
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Welcome to Luana Kai #C-203, your perfect Maui retreat! This beautifully updated 1-bedroom, 1-bath condo offers modern comfort and breathtaking ocean views. Situated on the second floor of one of Kihei's most desirable oceanfront complexes, it provides a peaceful escape in a quiet area away from South Kihei Road, ensuring tranquility without sacrificing stunning vistas.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Escape to paradise at Mahina Surf 111, a cozy one-bedroom condo nestled in the tranquil Mahina Surf resort on Maui’s west side. This waterfront retreat offers stunning ocean vistas, the gentle sounds of crashing waves, and direct access to some of the island’s best beaches and outdoor activities.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Konishiki Beachhouse by Gather, a magnificent beachfront estate nestled along the pristine sands of Maili Beach, HI. This iconic property offers an exceptional Hawaiian experience, featuring a spacious four-bedroom main residence and a charming one-bedroom guest house, perfect for up to 15 guests. Registration Number: 870280150000, GE-118-463-0272-01, TA-118-463-0272-01
6 bedroom
5 bathroom
Sleeps 15
Hawaii> Waianae
Discover this beautifully updated 8th-floor condo with a sunny south-facing balcony, ideal for relaxing and enjoying the view. Inside, the modern kitchen boasts granite counters, a stove, oven, stainless steel refrigerator, microwave, coffeemaker, toaster, blender, tea kettle, and a complimentary tea starter kit, making meal prep convenient and enjoyable.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Unit #4D Luana Garden Villa 3 Bedroom 3 Bath Garden Villa
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Enjoy a comfortable stay in this 1-bedroom condo at Valley Isle Resort on Maui. The unit features a queen bed in the master bedroom and a queen Murphy bed in the living area, accommodating up to 4 guests comfortably. Units are assigned on the day of arrival based on availability, ensuring you get the best possible location, though ocean views may vary if booked within 30 days of check-in.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Relax in our beautifully renovated 1-bedroom, 1-bathroom condo located at Wailea Ekahi Village, ideal for up to four guests. The upstairs unit features a comfortable California King bed and a Queen Murphy bed, providing ample sleeping space. The modern, fully stocked kitchen includes stainless steel appliances and minor upgrades made in 2025, perfect for preparing meals during your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Enjoy a memorable Maui vacation with three separate studio villas at Marriott Ocean Club located on the stunning shores of Ka'anapali Beach. Each villa features a comfortable king-size bed, a living area with a sofa bed, dining space, and a kitchenette equipped with essentials like a refrigerator, microwave, toaster, and coffee maker.
0 bedroom
3 bathroom
Sleeps 12
Hawaii> Lahaina
Welcome to Kapalua Golf Villa 27T5, a beautifully furnished island retreat offering panoramic views of the golf course and ocean. This second-level, 1-bedroom, 2-bathroom villa spans approximately 1,100 square feet and comfortably accommodates up to four guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Step into Kolea 2E and be greeted by a spacious, beautifully furnished interior that exudes comfort and style. The large entry doors open to a generous great room, featuring a cozy wrap-around couch facing a wall-mounted big screen TV, perfect for relaxing after a day exploring Kauai.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience your Maui getaway at Kihei Ali‘i Kai B402, a beautifully updated penthouse front-row condo with a fresh, modern ocean vibe. Located in one of the best view spots in the complex, this end unit boasts a spacious lanai with over 200° panoramic views—stretching from the West Maui Mountains to Molokini Crater to Haleakalā.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kihei
Escape to the enchanting Monkeypod Canopy, a charming glamping-style retreat that combines rustic allure with modern comforts. This unique accommodation offers a peaceful sanctuary perfect for relaxation and adventure. Inside, you'll find a comfortable queen-size bed adorned with luxurious bedding, ensuring restful nights after your daytime explorations.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kealakekua
Experience the ultimate Maui getaway at Wailea Point Village, a tranquil, luxurious condominium complex offering breathtaking ocean vistas and top-tier amenities. This spacious first-floor condo features two bedrooms and three bathrooms, providing ample space for families or groups seeking comfort and convenience.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Wailea
Experience the ultimate Hawaiian retreat at Country Club Villas 304, a beautifully appointed top-floor condo featuring breathtaking panoramic views of the ocean and golf course. This spacious unit comfortably sleeps up to six guests, making it ideal for families or friends seeking a relaxing getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
This stunning two-story penthouse offers breathtaking ocean views and luxurious amenities in the heart of Waikiki. The suite features three spacious bedrooms, including a master with a king bed and a second with a king bed, both with full bathrooms equipped with standard shower/tub combos. Registration Number: GE-205-587-5495-02, 497365472971, TA-151-395-6452-05
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Honolulu
Experience a truly relaxing vacation in this stunning ground-floor oceanfront condo, fully remodeled in 2025. The sparkling new kitchen features creamy white quartzite countertops and soft-close cabinets, perfect for preparing meals to enjoy on the lanai or at nearby local restaurants.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this spacious studio with a cozy bedroom loft located in a top-floor condo featuring a private balcony and air conditioning. The unit offers a full kitchen and bathroom, along with laundry facilities, ensuring a comfortable stay. A plush King-size bed guarantees restful nights, while the large bathroom provides ample space.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Discover the perfect Maui escape with Kaanapali Luxury Vacation Rentals at the stunning Honua Kai Resort. This listing includes two neighboring ground-floor villas, Luana Garden Villas 6B and 7A, situated in the final phase of the resort on Kaanapali Beach.
8 bedroom
6 bathroom
Sleeps 16
Hawaii> Lahaina
Summary:
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Discover the perfect extended stay at Island Colony 715, a charming studio nestled in the heart of Waikiki. This private rental by Hawaii Dream Realty LLC offers a cozy retreat with stunning pool views from the lanai. Located at 445 Seaside Ave #715, Honolulu, HI 96815, the property is ideal for those seeking a 30-day minimum stay in paradise.Registration Number: GE-063-187-5072-01, TA-063-187-5072-01, 1260210260019
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
6 acres in South Kihei. This inviting unit features a full kitchen, a cozy king bed, and a queen sofa bed, making it ideal for couples or small families seeking a relaxing escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this elegant 7th-floor condo in Kona, offering 171 sqft of private lanai for relaxing ocean views. The spacious layout includes a modern kitchen equipped with stovetop, oven, refrigerator, microwave, dishwasher, coffeemaker, blender, cookware, and tableware, making meal prep effortless.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience the perfect Hawaiian getaway in this charming 1-bedroom condo located on the 17th floor with breathtaking views of the Ala Wai Yacht Harbor and the Pacific Ocean. Recently partially renovated, the condo offers a spacious, clean living area that welcomes you immediately upon entry. Registration Number: GE-011-232-8704-01, TA-011-232-8704-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Consider this inviting condo your home away from home during your visit to Honolulu. Located in the vibrant Waikiki area, this 9th-floor studio condo offers a perfect blend of comfort and convenience. The property is equipped with essential amenities including a hair dryer, iron, air conditioning, and WiFi, ensuring a seamless stay.Registration Number: GE-001-276-5696-01, TA-001-276-5696-01, 260230450082
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Looking for the perfect island escape? You’re just 30 seconds from the beach and steps away from peace, fun, and everything in between.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to our Hawaiian Garden condo, your perfect spot for a relaxing vacation or extended stay in Waikiki! Recently redesigned by my designer wife, this mountain view apartment combines comfort and style in a boutique setting. The space features curated antiques, designer and upcycled furniture sourced from Hawaii and around the world, creating a cozy yet elegant atmosphere. Registration Number: GE-142-608-6400-01, 260070060133, TA-142-608-6400-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to 3BD Ka'ulu Villa (129B) at Hualalai Resort, a private and luxurious three-bedroom, three-bath townhome situated in Kailua Kona, HI. Overlooking the Nicklaus #4 fairway, this villa offers breathtaking views of the ocean and Maui beyond, providing an idyllic setting for a relaxing Hawaiian getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
This exclusive package combines two adjacent ground floor residences at the renowned Honua Kai Resort, creating a stunning 5-bedroom retreat that offers the space and privacy of a private home with the conveniences of resort living. Located in the highly desirable interior courtyard of the Konea Tower, these adjacent units feature a two-bedroom and a three-bedroom residence, totaling over 3,200 square feet of beautifully appointed interior space.
7 bedroom
5 bathroom
Sleeps 14
Hawaii> Lahaina
Experience the vibrant beauty of Waikiki from this beautifully remodeled 2-bedroom, 2-bathroom condo at the Ilikai Hotel. Spread over 1,240 sqft with a spacious balcony, this unit offers breathtaking views of the ocean, marina, and nightly fireworks display. Registration Number: 260100070032, TA-049-826-0480-01, GE-049-826-0480-01
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Honolulu
This delightful condo at Kona Bali Kai offers a peaceful and comfortable retreat in Kailua-Kona. The inviting living space features a luxurious king bed and a twin sofa sleeper, perfect for relaxing after a day exploring the island. The bathroom is well-equipped with a refreshing shower and a hairdryer, ensuring all your needs are met during your stay.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kailua-Kona
Welcome to our tranquil streamside retreat nestled on the beautiful Hamakua Coast, next to the Waipio Lookout. This peaceful property offers a perfect blend of indoor comfort and outdoor adventure, ideal for up to three guests.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honokaa
Experience the allure of Maui from this beautifully maintained 2-bedroom, 2-bathroom condo at Maui Kamaole in Kihei, Hawaii. Nestled in a tranquil, lush garden setting, this private unit provides a peaceful escape with serene views of greenery and glimpses of the upper tropical pool and spa area through the trees.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate beachfront retreat at this beautifully updated home on Lanikai Beach, renowned as one of the world's top beaches. The property offers three spacious bedrooms and four bathrooms, all designed with high-end finishes to provide comfort and luxury. Registration Number: 43003076, GE-164-517-2736-01, TA-164-517-2736-01
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua
Welcome to Orchid Hale, a sophisticated oceanfront sanctuary nestled near Keauhou Bay on Hawaii’s Big Island. This peaceful retreat offers spacious indoor and outdoor living areas, including four private bedrooms each with en-suite bathrooms, ensuring comfort and privacy for all guests.
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Kailua Kona
Welcome to Penthouse Hale Keani O-1203 at the Beach Villas in Ko Olina, Hawaii—a luxurious escape offering breathtaking ocean vistas and top-tier amenities. This elegant two-bedroom, two-bath penthouse is perched on the twelfth floor of the Ocean Tower, providing spectacular views of the lagoon pool, koi pond, tiki torches, and a sandy-bottom children's pool. Registration Number: 910570090134, GE-059-029-7600-01, TA-059-029-7600-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to Kaanapali Alii 4106, a beautifully appointed two-bedroom ocean view condo located on the tenth floor, offering spectacular views of the Pacific Ocean from a prime corner position. This spacious residence features an open-plan living area with large windows and a curved double sofa that perfectly complements the tranquil blue hues of the ocean, creating a relaxing atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this charming one-bedroom garden view condo ideally located across from Kalepolepo Beach Park in Maui. This well-appointed unit comfortably accommodates up to four guests, featuring a cozy queen bed in the bedroom and a plush pull-out sofa sleeper in the living room, equipped with a new sleeping unit for added comfort.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the charm of Poipu Crater Ohana, a unique and spacious retreat nestled within a lush volcanic crater on Kauai. This property combines two adjoining condos, making it perfect for families or groups up to eight guests seeking comfort, privacy, and easy access to Kauai’s stunning beaches and attractions.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
Escape to the vibrant heart of Waikiki with Sea Shore 35, a charming and spacious studio perfectly situated just a short 75-step walk from the iconic Waikiki Beach. This rental provides an authentic Hawaiian experience, combining comfort, convenience, and stunning ocean vistas in a location that puts the best of Waikiki at your doorstep.Registration Number: GE-040-327-7824-02, TA-040-327-7824-01, 1260230180011000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Make yourself at home in this charming 2-bedroom condo located on Maui's iconic Kaanapali Beach, recently voted the world's top beach by TripAdvisor. This inviting retreat offers a warm and welcoming atmosphere that captivates guests from the moment they step inside.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Maui Resort Rentals proudly presents Hokulani 413, a luxurious one-bedroom villa nestled in the exclusive interior courtyard facing suites at Honua Kai Resort. Located on the fourth floor, this suite offers breathtaking views of the sparkling Pacific Ocean and the neighboring island of Molokai from its spacious private lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your island paradise at Maui Kamaole! This spacious top-floor 2-bedroom, 2-bath condo offers breathtaking ocean and sunset views from the living area and private lanais, perfect for relaxing and enjoying Maui’s beauty.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian escape at Plantation Hale Ohana Retreat, a cozy condo located on Kauai's Royal Coconut Coast. This end-unit offers abundant natural light and ventilation with extra windows, providing a bright, airy atmosphere. Just a short 250-yard stroll takes you to the sandy beaches, perfect for swimming, sunbathing, or surfing.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Summary:Registration Number: GE-654-456-7811-01, TA-321-546-8190-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the perfect blend of urban excitement and natural beauty in these newly renovated studio apartments just two blocks from Waikiki Beach. Each studio features a modern kitchenette and a private balcony offering stunning views of Diamond Head and Kapiolani Park, Honolulu's beloved green space. Registration Number: GE-033-666-0480-01, TA-033-666-0480-01
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience a memorable vacation at Unit #413 in The Whaler resort, located on the famous Ka'anapali Beach, one of the most beautiful beaches in the US. This premium condo offers unobstructed views of white sands, Black Rock, and the West Maui mountains, providing a perfect backdrop for your getaway.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Centrally situated in Waikiki, this high-floor 1-bedroom, 1-bathroom condo offers breathtaking mountain and city views from a spacious 521 sqft layout. The unit features a comfortable Queen Size Bed in the bedroom and a Full Size Sofa Bed in the living room, accommodating up to 4 guests. Registration Number: 260210490172, TA-096-168-4480-01, GE-096-168-4480-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
