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 15450, 50
Welcome to your dream Maui retreat at Wailea Grand Champions Villas, a beautifully remodeled 2-bedroom, 2-bathroom condo perched on the second floor with breathtaking ocean vistas. This luxurious residence combines modern elegance with a relaxed island vibe, providing an ideal setting for a memorable vacation.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Wailea-Makena
This exquisite property offers a luxurious escape on the remote islands of Lanikai, blending modern elegance with natural beauty. Spanning over 8,400 square feet, this estate features seven spacious bedrooms, each designed for comfort and style, with the primary suite offering panoramic ocean views, a walk-in closet, and a chic Agape bathroom. Registration Number: 430010090000, GE-053-605-3248-01, TA-053-605-3248-01
7 bedroom
7 bathroom
Sleeps 10
Hawaii> Kailua
Welcome to your perfect Big Island getaway at Kulalani 2106, a beautifully upgraded 2-bedroom, 2-bathroom luxury townhome nestled within the prestigious Mauna Lani Resort. This serene, gated community offers a blend of privacy, comfort, and resort-style amenities, ideal for families, couples, or friends seeking a memorable Hawaiian escape.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimea
Welcome to Ohana Retreat at Koloa Kai, the ultimate destination for large groups seeking a luxurious Hawaiian escape. This expansive property combines two beautiful homes—Lei Ohana and Makanui Estate—along with two guest houses, providing up to 28 guests with ample space, privacy, and top-tier amenities.
13 bedroom
8 bathroom
Sleeps 28
Hawaii> Koloa
Experience the breathtaking beauty of Maui at this spectacular oceanfront condo on Kahana Beach. Located just 20 feet from the ocean, this corner unit offers unparalleled views and a serene, heavenly space where you can relax and unwind. Newly renovated, the condo features 1100 sq ft of spacious living with one bedroom, two full baths, a fully equipped kitchen, and two large lanais that provide perfect spots for enjoying the ocean breeze and stunning sunsets sinking between Lanai and Molokai islands.
1 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
The Westin Kaanapali Ocean Resort Villas offers a luxurious escape on one of the world's most beautiful beaches. Spanning 26 lush acres, this resort provides a variety of water activities including snorkeling, kayaking, surfing, and scuba diving, perfect for adventure seekers and relaxation alike.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
The Hualalai Resort will host an exclusive private event from December 1–6, 2025. Please note that access to certain resort amenities may be limited or restricted during this time for residential rental guests. We recommend inquiring prior to booking if your stay falls within these dates.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua Kona
★
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a relaxing stay in this Garden View Deluxe 2-Bedroom Villa, offering a spacious and well-equipped retreat perfect for families or groups up to six. The villa features a modern kitchen with a stovetop, oven, microwave, refrigerator, dishwasher, coffeemaker, toaster, cookware, and tableware, making meal preparation convenient and enjoyable.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Welcome to Napili Beach House, a stunning Maui luxury vacation residence designed for long-term stays of six months or more. This exceptional property offers breathtaking views of Napili Bay and the neighboring island of Moloka'i, making it the perfect escape for large groups, corporate retreats, destination weddings, or multi-generational families seeking a private island sanctuary.
8 bedroom
10 bathroom
Sleeps 24
Hawaii> Napili
Experience a comfortable stay in this 1-bedroom condo offering partial ocean views and modern features. The condo spans 550-670 sqft (51-62 sqm), making it ideal for up to 4 guests including children and infants. The bedroom features a cozy queen bed, while the living area includes a full-size sofa bed, providing versatile sleeping options. Registration Number: 260030140041, GE-179-440-0256-01, TA-179-440-0256-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Maui getaway at Royal Mauian 310, a luxurious oceanfront condo offering breathtaking views of Maui's turquoise waters. This beautifully appointed 2-bedroom, 2-bath residence combines comfort, style, and convenience to create an unforgettable holiday.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this stunning 4-bedroom deluxe oceanview suite at Honua Kai, combining two neighboring 5th-floor residences for over 2100 sq ft of elegant living space. Each suite features spacious layouts, high-end finishes, and panoramic views of the Pacific Ocean from nearly every room.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Escape to this charming second-floor, two-bedroom, two-bath oceanfront condo nestled on Kauai's scenic Coconut Coast. Accommodating 2-6 guests, this retreat offers breathtaking trade wind breezes and the soothing sounds of ocean surf, creating a perfect tropical ambiance.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapa?a
Experience ultimate relaxation in this newly remodeled penthouse perched on the top floor, offering expansive views of the ocean, mountains, and golf course. The spacious corner unit features high ceilings and abundant natural light, creating a bright and airy atmosphere.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Step into paradise at The Point at Poipu, a unique resort nestled on over twenty acres of lush tropical gardens overlooking Keoneloa Bay. This enchanting destination features flowing waterfalls, a crystal-clear koi pond, and vibrant tropical flora that create a dreamlike atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
This spacious studio offers breathtaking ocean views from a private balcony, perfect for relaxing and enjoying the scenery. Located at the Maui Marriott Ocean Club, it features a comfortable layout and parking in the garage at no extra cost, with optional valet.
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautifully appointed three-bedroom ocean view villa nestled within the luxurious Marriott's Ko Olina Beach Club on Oahu. Offering over 1800 sqft of comfort, this deluxe suite features three bedrooms with a mix of king and twin beds, perfect for families or groups. Registration Number: TA-005-837-4656-01, GE-005-837-4656-01, 910570140000
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Experience a comfortable stay in this spacious 1700 sq ft 2-bedroom, 2-bath condo with partial ocean views. The full kitchen includes a refrigerator, stove/oven, microwave, dishwasher, cookware, and tableware, ideal for preparing meals during your vacation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the comfort and luxury of Sunset Villa at Coconut Plantation, located in a gated resort community on the west side of Oahu. This spacious 4-bedroom, 3-bathroom villa offers a perfect tropical retreat for families or groups of up to 8 guests. Registration Number: TA-097-210-4704-02, GE-097-210-4704-02, 1910560040214
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Experience the vibrant heart of Honolulu from this beautifully renovated condo located on the 20th floor of Island Colony. Perfect for a long-term stay, this property offers a blend of modern comfort and stunning scenery. The condo boasts an enviable corner position with breathtaking peek-a-boo views of the ocean, complemented by sweeping vistas of the city and mountains—ideal for starting your day with a coffee on the shaded lanai or unwinding with a sunset cocktail. Registration Number: TA-136-342-8864-01, GE-136-342-8864-01, 1260210260264
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
escape to Maui in this charming Pikake Studio located in a boutique property just across the street from the beautiful beach. The property features only six units, offering a private and intimate setting for your vacation. Take advantage of the lush tropical landscaping, relax by the pool with lounge chairs, or enjoy outdoor dining at the BBQ area.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
This inviting 1-bedroom condo in Kapa�a offers a comfortable retreat perfect for your Hawaiian vacation. Equipped with WiFi and a washing machine, it provides all the essentials for a hassle-free stay. The pleasant atmosphere and thoughtful amenities create a relaxing environment, making it an ideal choice for couples or solo travelers looking to enjoy the magic of Kapa�a.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapa?a
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay*: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Kaanapali Alii 294, a luxurious and spacious one-bedroom condo offering stunning mountain views on Maui. This well-appointed residence features a comfortable bedroom, a cozy sleeping den, and two full bathrooms, making it perfect for couples or small families seeking a relaxing getaway.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a comfortable and stylish stay in this 12th-floor condo with a private balcony offering stunning views. The condo features tile and vinyl plank flooring throughout, creating a modern and easy-to-maintain space. The fully equipped kitchen includes a fridge, stove/oven, microwave, cookware, and tableware, making it convenient to prepare meals during your stay.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
This spacious ocean-view villa is located in the heart of Hualalai's premier Golf Villas community, offering stunning unobstructed views of the ocean, golf course, and breathtaking sunsets. Designed with comfort and style in mind, this single-level ground floor residence features over 2,500 square feet of elegant living space, ideal for up to six guests.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
Escape to a tropical paradise at Waikomo Streams Villas 121, a spacious and beautifully furnished one-bedroom, one-bath condominium located in the lush gardens of Koloa, Hawaii.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover this charming Lahaina condo, ideal for a peaceful vacation on Maui. The unit offers a comfortable bedroom with a king bed, perfect for restful nights after exploring. The living room features a sofa bed, providing extra sleeping space for guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Come enjoy the stunning island of Maui by staying in this modern, private condo just steps from the beautiful Kahana Beach. Recently renovated with attention to detail, this unit offers a comfortable and stylish retreat for your vacation.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Welcome to your dream getaway on the Garden Isle! This top-floor penthouse in the heart of world famous Poipu Beach offers the perfect blend of luxury, comfort, and island charm. 5 baths, this retreat is designed to host your unforgettable Hawaiian vacation.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
***Please note: This unit is under NEW ownership. The previous owner had a 10/10 rating with 67 reviews!
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
Your own private guest cottage sits on a scenic 3-acre working avocado farm, offering a peaceful escape away from Kona's hustle and bustle. Situated at approximately 1,200 feet elevation, this charming retreat provides a cool, comfortable environment while still being just 12 minutes from major grocery stores and restaurants.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Welcome to our beautifully renovated Kona condo, a perfect tropical retreat. After years of anticipation, we completed a full remodel in September 2024, transforming this hidden gem into a luxurious haven. The unit features modern upgrades including central A/C, a fully updated kitchen with new cabinets, state-of-the-art appliances, and a spacious island, making meal prep and gatherings enjoyable.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Mauna Lani Terrace Penthouse - Newly Remodel - G301
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kamuela
Welcome to your perfect family retreat at Mauna Lani F3! This charming two-story townhome offers a blend of comfort, serenity, and breathtaking golf course vistas. Inside, an open-concept living area connects the living room, dining space, and fully equipped kitchen, creating an inviting environment for family gatherings and shared moments.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Mahana 913 is a beautifully updated studio condo that offers stunning, unobstructed oceanfront views and a serene, relaxing atmosphere. Perfect for a romantic getaway or solo retreat, this bright and airy space accommodates up to three guests and features a plush king-size bed to ensure a restful night's sleep along with a chair that converts to a twin bed.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
This charming one-bedroom suite offers breathtaking views of the Pacific Ocean and is perfect for a relaxing getaway on Kaanapali Beach. The suite features two bathrooms for added convenience, a spacious living area, and a fully equipped kitchen to prepare your favorite meals.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this contemporary, privately owned Waikiki studio condo offering breathtaking views of the city, mountains, and ocean. Recently renovated with stylish granite countertops, a built-in cooktop, and modern furnishings, this 242-square-foot space is perfect for travelers seeking comfort and convenience.Registration Number: TA-211-493-2736-01, GE-211-493-2736-01, 211493273601
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
5-bathroom beachfront home nestled along a quiet street on a secret sandy beach in East Honolulu. This serene property offers breathtaking ocean views, direct access to a tranquil beach, and a perfect blend of relaxation and adventure for your Hawaiian vacation.Registration Number: TA-121-539-7888-01, 380010050000, GE-121-539-7888-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Honolulu
Discover this charming ground floor 2-bedroom, 2-bath oceanfront condo nestled along the stunning Kailua Bay in Kona. Newly listed on the vacation rental market, this beautifully updated unit offers breathtaking views of the bay and lively ocean activity right from your window.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua Kona
Experience the ultimate escape in this luxurious 1-bedroom Ridge Villas Gold Ocean View in Kapalua. Spanning 1120 sq ft, the villa offers breathtaking ocean vistas from your private balcony, a comfortable king or queen bed, and a cozy living area with a queen sofabed.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
5-bath townhome offers a rare opportunity to stay in one of Maui’s most peaceful and private communities. This is a comfortable, casual home—perfect for families or small groups seeking direct ocean access, a relaxed setting, and plenty of space to unwind.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Find tranquility at Marriott's Maui Ocean Club - Molokai, Maui & Lanai Towers. Our beachfront vacation ownership resort offers stunning oceanfront suites with balconies overlooking Ka'anapali Beach in West Maui. Whether you're here for relaxation or adventure, enjoy our "super pool" complex with two pools, a whirlpool spa, and a kid-friendly Discovery Cove that keeps the whole family entertained.
2 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the epitome of townhome living in this airy 3-bedroom residence at Pili Mai, offering high-end furnishings and finishes throughout. The largest, most distinctive floor plan in the complex, the Kalewa, features 1600 square feet of living space complemented by 278 feet of lanai, perfect for outdoor relaxing and entertaining.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Koloa
REGISTRATION/RESORT/PARKING FEES DUE TO THE RESORT AT THE ARRIVAL.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Princeville
This newly renovated luxurious villa is located in the peaceful neighborhood of Ko Olina Kai, offering a perfect retreat for families or groups. With three spacious bedrooms, it comfortably accommodates up to six guests, providing a blend of comfort and elegance. Registration Number: 910560050197, TA-152-736-4096-01, GE-152-736-4096-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to Maui Kai, a charming boutique complex nestled along the famous Kaanapali Beach. Our luxurious penthouse, situated on the 9th floor, offers breathtaking panoramic views of Lanai and Molokai islands, making it an ideal retreat for those seeking tranquility and natural beauty.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Truly comfortable, all amenities, fabulous, newly modernized, 2-bedroom Princeville retreat. This charming condo also features a cozy living room with a sofa bed, Wi-Fi, a fully equipped kitchen, and two lovely bathrooms with a bathtub and hair dryers, all linen and bathroom necessities, laundry/dryer in the condo.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to your dream Maui getaway at Kamaole Sands! This luxurious condo, located in Building #8, Unit #411, offers breathtaking ocean views and a prime location just across from Kamaole Beach Park 3. The property combines comfort, elegance, and convenience, making it an ideal choice for families, couples, or groups seeking a memorable Maui vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
