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 11600, 50
This tandem package features two exceptional 5th-floor suites at Honua Kai Resort, offering breathtaking partial ocean views and scenic West Maui Mountains. The two-bedroom suite is situated in a prime corner location, boasting a massive over 500 sq ft balcony perfect for relaxing or entertaining, while the one-bedroom suite provides an additional 145 sq ft balcony with uninterrupted ocean vistas that extend into the master bedroom.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Lahaina
Escape to this charming 1-bedroom, 1-bath Maui condo offering spectacular partial ocean views from a private lanai. Located on the fourth floor, this updated unit provides 686 square feet of comfort and scenic living space.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover your perfect vacation retreat in Waikiki with this spacious one-bedroom condo located on the 18th floor of the iconic Ilikai Hotel. Spanning 500 sq ft, the condo offers a comfortable and stylish living space complemented by a large 120 sq ft lanai where you can unwind and enjoy tropical mountain breezes. Registration Number: GE-048-344-2688-01, TA-048-344-2688-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Villas on the Prince offers a luxurious and private retreat featuring a pool, spa, BBQ, and clubhouse, nestled in a serene setting just minutes from Princeville Resort and local amenities. The property is ideally located within an 11-minute drive to the stunning Hanalei Bay and a 25-minute drive to the Na Pali Coast State Park, making it an excellent base for exploring the island's natural beauty.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Princeville
Experience a relaxing vacation at Wailea Elua Village, a prestigious beachfront property nestled in the heart of Wailea. This low-density community offers two-story buildings set amidst twenty-four acres of lush tropical landscaping. The property boasts stunning Ulua Beach, renowned for snorkeling, breathtaking sunsets, swimming, sunbathing, and whale watching during winter.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the pinnacle of Maui luxury in Hokulani 841, a premier two-bedroom suite at Honua Kai. Located on the 8th floor, this upgraded residence offers breathtaking direct ocean views from almost every room, including the kitchen, dining area, living room, and a expansive 340 sq.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this beautifully renovated 2-bedroom ground-floor condo in Keauhou's Country Club Villas, offering stunning ocean views across the Keauhou Country Club Golf Course from the spacious living area, master bedroom, and covered lanai with a private garden.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the perfect island retreat at Kuleana 713, a beautifully maintained oceanfront condo nestled between Kaanapali and Kapalua on Maui. This 1-bedroom, 1-bath unit offers stunning views of the Pacific Ocean and vibrant West Maui sunsets from your private lanai, providing an ideal setting for relaxation and enjoyment.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect Kauai getaway at Wailua Bay View 103, a cozy beachfront condo ideally situated on the Coconut Coast. This inviting one-bedroom, one-bathroom unit comfortably accommodates up to four guests, making it an excellent choice for families, friends, or solo travelers seeking a tropical retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Experience the beauty of Maui in this beautifully renovated ground-floor one-bedroom, two-bath condo with stunning ocean views. Designed with comfort and convenience in mind, the unit features a plush queen-size bed, a sofa sleeper, and an extra daybed, accommodating up to four guests comfortably.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a peaceful retreat at Maui Sands Seaside 713, a charming second-floor condo just steps from the ocean in West Maui. The unit features one bedroom with a king-size bed, a sleeper sofa in the living room, and modern upgrades including a fully equipped kitchen, newly renovated bathroom with walk-in shower, and in-unit washer/dryer.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Napili Shores B211, a cozy 1-bedroom, 1-bathroom condo situated on the second floor, offering stunning ocean views and Hawaiian-inspired decor. This comfortable retreat accommodates up to 4 guests with a luxurious California king-size bed in the bedroom and a queen-size sofa bed in the living area.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
The Outrigger Spa and Resort offers an ideal central location on Kauai Island, making it easy to explore all parts of this beautiful Garden Isle. This top-end unit features partial ocean views from the balcony and full mountain views inside, providing a serene backdrop for your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lihue
Discover the perfect Maui getaway at this ground-floor, oceanfront condo located at Sugar Beach Resort in North Kihei. Just steps from the sandy beach, this one-bedroom unit offers a relaxing and convenient island retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to this charming two-bedroom, two-bath upper-end unit at Kamahana, offering breathtaking ocean, mountain, and golf course views from every room. As you settle into the luxurious king bed in the master bedroom, you'll drift asleep to the soothing sounds of the sea.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to Eco Kō Mai Retreat, a tranquil haven nestled in the peaceful town of Volcano, Hawaii. This retreat offers a perfect blend of serenity, spirituality, and modern convenience, making it an ideal escape for those seeking relaxation and connection with nature.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Volcano
Experience a luxurious island retreat at Wailea Ekahi 11A on Maui. Located in the sought-after Wailea resort community, this spacious condo combines upscale comfort with serene tropical ambiance. Step inside to an open-concept living area featuring a cozy couch, dual reclining chairs, and a dining table perfect for planning your Maui adventures.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to Happy Honu Oasis, a thoughtfully designed top-floor studio perfect for couples seeking a relaxing island escape. This cozy apartment offers a private bath, air conditioning, and a spacious dressing area, ensuring comfort during your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hilo
Escape to the lush north shore of Kauai at the Wainiha Beach House, a charming Polynesian-style home built in 1963 by sailing pioneer Peggy Slater. Nestled amidst dense jungle and fragrant flowers, this unique property offers an authentic Hawaiian experience with breathtaking ocean views and a stream flowing in the backyard.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Wainiha
Experience the best of Waikiki in this legally permitted, spacious 1-bedroom condo located on the 12th floor of the Diamond Head Beach hotel. The suite features a large living and dining area, comfortably accommodating up to four guests, making it ideal for families or groups. Registration Number: GE-028-685-7216-01, TA-028-685-7216-01, 310320290054
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Kihei Ali'i Kai D207, a beautifully updated ocean view condo offering a perfect Maui getaway. Nestled in a prime South Kihei location, this 1-bedroom, 1-bath unit provides comfort and convenience just steps away from Kamaole I and II Beach Parks.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Waikiki getaway in this legally permitted studio suite at The Palms Waikiki. With two queen beds, it comfortably hosts up to four guests. Enjoy stunning views of the Diamond Head crater in the distance and sweeping ocean vistas to the south, all from a prime location just west of Fort DeRussy park. Registration Number: GE-198-248-1920-01, 260120030256, TA-198-248-1920-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the beauty of South Kihei at Kihei Surfside Resort, a distinctive six-story oceanfront property offering breathtaking 180-degree views of the Pacific. The resort's prime location next to Keawakapu Beach makes it ideal for enjoying vibrant sunsets, snorkeling, swimming, romantic beach strolls, and seasonal whale watching.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei, Maui
Experience the serene beauty of Kauai at Pono Kai Resort, nestled on 12 lush acres in the charming town of Kapaa. This mini suite condo in building K offers a cozy, fully equipped space perfect for couples or small families. Inside, you'll find a comfortable king-size bed in the master bedroom and a queen sleeper sofa in the living area, along with a full bathroom stocked with essentials like shampoo, conditioner, and a hair dryer.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Enjoy a comfortable stay in this recently renovated large studio located on the 17th floor in the heart of Waikiki. Spanning approximately 450 square feet, this unit offers a perfect blend of comfort and convenience. The space features beautiful hardwood floors throughout, elegant granite countertops in the kitchen and bathroom, and a separate powder area, making it ideal for both relaxing and entertaining. Registration Number: GE-023-061-7088-01, 260190010164, TA-023-061-7088-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This delightful one-bedroom, one-bathroom condo is nestled on the second floor of a beautiful Maui resort, offering a perfect tropical retreat for your getaway. The spacious living area comfortably hosts up to four guests, making it ideal for couples or small families seeking a serene escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover four beautifully furnished, hotel-zoned studio apartments in the heart of Waikiki, each offering a spacious lanai, fully equipped kitchen, and modern amenities. Located just a 4-minute walk from Waikiki Beach, these apartments provide the perfect base for exploring the island. Registration Number: 260190010000, GE-193-411-2768-01, TA-193-411-2768-01
4 bedroom
4 bathroom
Sleeps 16
Hawaii> Honolulu
Enjoy a luxurious stay at Hanalei Bay Resort nestled on 28 lush acres overlooking the breathtaking Hanalei Bay and Bali Hai. This spacious one-bedroom condo offers spectacular views of Kauai's natural beauty, making it an ideal retreat for families, couples, or groups seeking a tropical paradise.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Discover the perfect 30-day escape at Paradise Adventure Getaway, a beautifully renovated home nestled just minutes from the stunning North Shore of Oahu. This inviting residence features three comfortable bedrooms, a modern full bathroom, and a fully equipped kitchen designed for both relaxation and culinary adventures.Registration Number: 540050150000, GE-053-056-1536-01, TA-053-056-1536-01
3 bedroom
1 bathroom
Sleeps 7
Hawaii> Hauula
Experience a slice of paradise in this beautifully remodeled 1-bedroom condo located on the 19th floor of the iconic Ilikai Hotel in Waikiki. This well-maintained unit offers the comforts of home with a contemporary touch, making it an ideal retreat for travelers seeking convenience and relaxation.Registration Number: TA-069-034-1888-01, GE-069-034-1888-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience a comfortable stay at our beautifully furnished condo on the 13th floor of Ilikai Marina, ideally located at the entrance to Waikiki. The condo features a modern kitchen with a refrigerator, cooktop, microwave, and all necessary cookware and dinnerware, perfect for preparing meals during your stay. Registration Number: GE-063-365-9392-01, TA-063-365-9392-01
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Step into Valley Isle Resort 1106 and be captivated by the breathtaking panoramic views that greet you from the moment you open the door. This spacious studio offers a comfortable queen-size Murphy bed and a full-size sofa bed, comfortably accommodating up to four guests.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Aloha and welcome to your perfect Hawaiian getaway at Waikiki Banyan! This stunning 29th-floor corner unit offers enchanting views of the Koolau mountains and the Ala Wai golf course, providing a tranquil retreat in the heart of Waikiki.Registration Number: 260250050305, GE-011-246-6432-01, TA-011-246-6432-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This exquisite residence is nestled within the exclusive Kiahuna Golf Village on Kaua'i's sunny south shore, offering a perfect blend of old Hawaii charm and modern luxury. Surrounded by ancient lava rock walls, the home provides a unique connection to Hawaii's rich history while enjoying contemporary comforts.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Experience the beauty of Maui at Grand Champions 136, a beautifully redesigned 1-bedroom, 2-bathroom condo nestled in lush tropical surroundings. Located on the top floor, this unit offers spectacular views of Haleakala, creating a serene and picturesque setting for your Hawaiian vacation.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Discover this true oceanfront condo at Hale Kona Kai, where your lanai nearly hangs over the ocean, offering breathtaking tide pool views filled with turtles and eels. The soothing sounds of the sea create a perfect ambiance to unwind after a day of sightseeing or relaxing.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kailua Kona
Nihi Kai 510 offers a charming and modern retreat in the heart of Poipu, Kauai. This beautifully updated two-bedroom, two-bathroom condo provides a perfect blend of comfort and convenience for your island escape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Discover this stylish oceanfront one-bedroom condo situated on the 18th floor in the iconic Ilikai Hotel at the entrance to Waikiki. The property offers breathtaking views of the ocean, twinkling city lights, and majestic mountains, making it an ideal retreat for travelers seeking both comfort and scenery.Registration Number: TA-141-480-1920-01, GE-141-480-1920-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate in comfort and luxury with this spacious 1-bedroom, 2-bath corner unit at The Whaler on Maui. Floor-to-ceiling glass sliding doors and a wrap-around lanai provide breathtaking panoramic views of the Pacific Ocean, Molokai, and the West Maui Mountains.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect Maui getaway with these two beautifully renovated adjacent 7th-floor suites at Kaanapali Alii. Each suite offers partial views of the Blue Pacific Ocean and the golden sands of renowned Kaanapali Beach, along with stunning vistas of the lush West Maui Mountains.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Discover Whaler 123, a spacious ground floor condo nestled in the heart of Kaanapali Beach. This beautifully decorated unit features a King bed, two doubles, and a sofa sleeper, making it perfect for families or couples seeking a peaceful retreat after a day of water activities, sightseeing, or golfing.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This beautifully appointed deluxe studio offers breathtaking views of Waikiki Beach and the Pacific Ocean. The bright, airy space features a spacious kitchenette equipped for cooking your favorite meals, perfect for a relaxing stay. Step out onto the furnished lanai to enjoy your morning coffee or an evening glass of wine while taking in the stunning scenery. Registration Number: GE-009-524-0192-01, TA-009-524-0192-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> HONOLULU
Experience the ultimate Maui getaway at Papakea Resort B407, a beautifully upgraded condo offering stunning ocean views and close proximity to the beach. This luxurious villa is perfect for relaxing and enjoying all that Maui has to offer, with a balcony overlooking the sea, a fully equipped kitchen, and modern comforts like a king-size bed, DVD player, and free WiFi.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience stunning ocean and lagoon vistas from this beautifully remodeled Waikiki condo, just steps from the iconic Waikiki Beach. Located at the gateway to Waikiki, the Ilikai Suites offer a perfect blend of comfort and convenience. Walk to the beach in under 5 minutes without crossing streets, and enjoy easy access to shopping, restaurants, parks, and Waikiki’s vibrant nightlife. Registration Number: GE-121-684-9920-01, TA-121-684-9920-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to the Kona Billfisher, a warm and inviting vacation retreat in Kailua Kona on the Big Island of Hawaii. This charming third-floor condo offers vaulted ceilings and a spacious lanai overlooking a lush garden with distant harbor views, perfect for relaxing after a day of exploring.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Escape to Hanalei Colony Resort J3, a serene retreat nestled on Kauai's untouched North Shore. This charming vintage-style condo offers two bedrooms, with flexible space to accommodate a third guest or serve as a remote office. The fully equipped kitchen and light-filled living and dining area open to stunning ocean vistas, creating a perfect setting for relaxation.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Hanalei
Discover Mahina Surf 212, a charming oceanfront loft perfect for family stays in Maui. This well-equipped condo features a full kitchen, a comfortable living area, and a furnished balcony ideal for relaxing after beach days. The lush landscaped grounds include shared amenities like a pool and gas grills, offering a resort-like atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Kona Makai 3102 by Gather! This beautifully renovated condo offers an authentic Hawaiian experience with breathtaking panoramic views of the Pacific Ocean. Step inside to discover a fresh, modern space featuring air conditioning and ceiling fans throughout, ensuring your comfort in the tropical climate.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Escape to this charming oceanfront duplex cottage located just 150 yards from a pristine, secluded beach. Perfect for those seeking tranquility and natural beauty, this one-bedroom unit offers a cozy and inviting retreat. The bedroom features a luxurious king-size bed, while the living room includes a convertible bed, making it ideal for couples or small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Maunaloa
Discover Unit #407 at Hale Kona Kai, an elegant oceanfront retreat perfect for travelers seeking comfort and style. This top-floor condo boasts breathtaking Pacific Ocean views from both the bedroom and living room, creating a serene and picturesque setting for your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
