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 10950, 50
Escape to Sugar Beach Resort 511, a beautifully appointed condo offering spectacular oceanfront views in Maui. Located right on five miles of pristine white-sand beach, this property provides the perfect setting for a relaxing island vacation. Wake up to the soothing sounds of waves and step outside to enjoy strolling, sunbathing, or swimming just outside your door.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to this beautifully upgraded oceanfront condo at Sugar Beach in Maui. Enjoy stunning front-row views of the Pacific Ocean from the lanai, where breathtaking sunsets create unforgettable moments. The unit features granite countertops and a stylish glass backsplash, offering a modern and inviting atmosphere.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Marine Surf #PH4 at 364 Seaside Ave, a spacious and beautifully appointed one-bedroom, two-bath condo offering a total of 1,312 sqft including a large lanai with outdoor furniture. This centrally located unit is ideal for families of up to 6 or two couples seeking comfort and convenience in Waikiki. Registration Number: 260190010232, TA-001-615-0528-02, GE-001-615-0528-02
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Prince Kuhio 305, a beautifully remodeled third-floor condo offering breathtaking ocean views on the island of Kauai. Just 100 yards from the pristine beach, you can fall asleep to the soothing sounds of the waves and enjoy close encounters with sea turtles and resting monk seals.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Koloa
Experience the charm of this fully remodeled one-bedroom, one-bath condo located in the heart of Poipu at Kiahuna Plantation. Situated on the top floor with high ceilings, it offers a peaceful retreat with a peekaboo ocean view from the spacious lanai, ideal for enjoying colorful sunsets.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to your dream Maui getaway at the Kapalua Ridge Villas. This fully remodeled 2-bedroom condo, renovated in February 2024, combines luxury and comfort with stunning ocean views. Inside, you'll find a fresh, artful decor creating a relaxed yet sophisticated ambiance, perfect for families or friends seeking a memorable island retreat.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate island getaway at Punaluu Paradise Beach House, perfectly situated on Oahu's Windward coast. This charming country home is just feet from the water, offering unobstructed 180-degree views of the Pacific Ocean where you can spot turtles, schools of fish, and enjoy activities like snorkeling, paddleboarding, and fishing. Registration Number: GE-321-654-0987-01, TA-321-654-0987-01, 530080140000
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Hauula
Welcome to Kainani Kona — where oceanfront luxury seamlessly blends with the warm spirit of Aloha. This exquisite two-bedroom, two-bath condo offers breathtaking panoramic views of the Pacific Ocean and Kona’s striking coastline, creating an ideal setting for relaxation and adventure.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to this charming and quiet studio nestled on the corner of a concrete building in beautiful Kihei. The deck offers stunning views of the ocean, perfect for relaxing and enjoying the tropical breeze. The unit has been upgraded with modern air conditioning to keep you comfortable during warmer days.
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your perfect oceanfront retreat at Alii Kai 4-103 in Princeville, Kauai! This beautifully updated ground-floor condo offers spectacular views overlooking the Pacific Ocean. Step out onto the lanai to enjoy breathtaking scenery, feel the refreshing trade winds, and watch whales in winter or go snorkeling in summer at the nearby beach.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover this beautifully appointed 9th-floor studio at Aston Kaanapali Shores, offering a stunning partial ocean view from your private lanai. Perfectly suited for up to four guests, this condo combines comfort with convenience, providing a fully equipped kitchen, a king-sized bed, a sleeper sofa, and modern amenities including central AC and a flat-screen TV.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Napali
Aloha from Midway Vacations! Our team of local hospitality experts is committed to making your Hawaiian getaway unforgettable. When you stay at our property, you’ll enjoy a professionally cleaned, meticulously maintained condo with 24/7 support for any needs.Registration Number: GE-202-106-0608-01, TA-202-106-0608-01, NUC #: 90/TVU-1210/1990/NUC-1367, 260280110084
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Located in the heart of Waikiki, this beautifully remodeled and tropically decorated condo offers a perfect blend of comfort and convenience. The unit features a cozy queen-sized bed and a fully equipped kitchen with standard appliances, making it ideal for a relaxing stay. Registration Number: GE-180-356-3008-01, TA-180-356-3008-01, 260270310086
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the best of Maui at Maui Sunset, a 5-acre beachfront resort in South Kihei. This ground-floor 1-bedroom condo comfortably sleeps up to 4 guests with 2 bathrooms, a fully equipped kitchen, and modern amenities. The spacious layout includes a sofa bed and futon, providing ample space for families or small groups.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Unit 10-314 at Kamaole Sands is a spacious 1200 sq ft top-floor vacation condo in Kihei, Maui, featuring stunning views of Kamaole Beach Park III. Accessible via elevator or stairs, this stylishly decorated unit blends island charm with modern comforts.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> KIHEI
Escape to this stunning designer condo in Maui, offering spectacular views and elegant craftsmanship throughout! Located in North Kihei, just steps from Maui's longest beach, this third-floor unit combines luxury with convenience. The spacious two-bedroom, two-bathroom layout comfortably sleeps six, making it ideal for families or friends.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to this inviting studio offering a peaceful retreat with a private backyard and lanai. The space includes a comfortable queen bed and a convertible couch, perfect for relaxing after a day at the beach. The fully stocked kitchen makes meal prep easy, while the tiled floors and wooden cabinets add a warm touch. Registration Number: TA-007-468-4416-01, W65442690-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Haleiwa
Experience the vibrant heart of Waikiki from this stylish 13th-floor condo at Ilikai Marina. Perfect for families or friends traveling together, this unit offers a fully equipped kitchen, modern living space, and stunning ocean views. The condo features a comfortable queen bed, a sofa, and a dining table for two, making it ideal for relaxing after a day at the beach or exploring Honolulu.Registration Number: GE-006-237-5936-01, TA-006-237-5936-01
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Step into Kamaole Sands unit #1205, a welcoming condo featuring keyless entry and a spacious, luminous living area furnished for comfort and entertainment. The living room offers ample seating, perfect for relaxing, dining, or hosting friends. A queen Murphy bed provides versatile sleeping arrangements, accommodating up to four guests comfortably.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the charm of this upstairs apartment at Hanalei Colony Resort, offering breathtaking views of both the ocean and mountains. Located just steps from a secluded sandy beach, it provides a peaceful retreat for up to four guests, making it ideal for honeymoons, proposals, and anniversaries.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hanalei
Experience the perfect blend of comfort and natural beauty in this light and airy studio located at Napili Bay in West Maui. Just steps from the pristine sandy beach, this property offers breathtaking ocean views and an unbeatable location close to Kapalua.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your happy home in Princeville, Kauai. This spacious townhome in the Nihilani complex offers 1,600 sq ft of comfort and modern amenities. The unit features a welcoming open-concept living area with a water feature view of a peaceful waterfall, creating a tranquil backdrop reminiscent of mountain streams.
4 bedroom
3 bathroom
Sleeps 5
Hawaii> Princeville
Experience the ultimate Hawaiian getaway at The Beach Villas at Kahaluu, a charming vacation rental perfect for families and friends. This beautifully decorated 2-bedroom, 2-bathroom condo offers stunning ocean views from your private lanai, where you can relax with a drink and watch breathtaking sunsets over the Pacific.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience a relaxing stay at The 19th Hole, a charming condo located on the 18th hole of the Fazio Golf Course at Kuilima. This fully furnished unit offers a prime location just steps from the Turtle Bay Hotel, providing easy access to upscale amenities and stunning beaches. Registration Number: 570010270167, GE-023-864-5248-01, TA-023-864-5248-01, STR#2246
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kahuku
Kamaole Sands 7-103 offers a comfortable and beautifully appointed ground-floor condo in South Maui, ideal for up to 4 guests. The unit features a spacious bedroom with a King bed, a living area with a Queen sofa sleeper, and two bathrooms, providing ample space and privacy for couples or families traveling together.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to a luxurious 2-bedroom, 2-bathroom beachfront villa nestled in the heart of Maui's Kaanapali Shores resort. This impeccably maintained retreat offers breathtaking panoramic views of Lanai, Molokai, and the West Maui Mountains from its expansive lanai, where vibrant sunsets paint the sky in shades of orange and pink.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui getaway in Wailea Beach Villas Penthouse 504, a stunning 2-bedroom, 2-bathroom oceanfront condo boasting over 1900 sq. ft. of elegant living space. Located on the fifth floor, this elevated residence offers 180-degree panoramic views of the Pacific Ocean, including the iconic Kaho‘olawe, Molokini crater, and Lanai.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea, Maui
Embark on a memorable Hawaiian adventure with this spacious 2-bedroom, 2-bath condo located in the popular Alii Lani community on Big Island. The condo features a large garden lanai perfect for relaxing and enjoying tropical breezes. Guests have access to two pools, spas, and beautifully landscaped grounds that showcase the island's lush flora.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the perfect stay in this beautifully renovated 575 sqft apartment located in the center of Hilo. Situated on the second floor, it offers two cozy bedrooms, a spacious living room, a modern bathroom, and a lovely balcony to enjoy the tropical breezes.
3 bedroom
1 bathroom
Sleeps 5
Hawaii> Hilo
Welcome to MauiOhana condo! This spacious, fully remodeled top-floor unit offers stunning ocean views and easy access to Kahana Beach, just across the street. Whether you're relaxing on the balcony or exploring nearby attractions, this condo provides the perfect Maui getaway.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui vacation in this beautifully remodeled oceanfront condo at Kihei Sands, just steps from the pristine Sugar Beach. Perfect for couples seeking a peaceful retreat, this condo offers stunning ocean views and modern comforts.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to Paradise on the Big Island of Hawaii! Casa De Emdeko 136 is an oceanfront condominium offering an exceptional vacation experience on the famous Kona Coast. This ground-floor unit provides direct access to a sandy beach, an oceanfront pavilion, BBQ area, and a rare saltwater pool, all with stunning ocean views.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
This beautifully renovated, handicap-accessible condo in Kihei Garden Estates offers a luxurious and comfortable stay. The spacious one-bedroom features granite countertops, tiled floors, and a marble shower, ensuring a high-end feel throughout.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the perfect blend of luxury and privacy at Maui Kamaole I102, a spacious condo located in the heart of South Kihei. Just minutes from the beautiful Kamaole beaches, including the popular Kamaole Beach Park III, this condo offers a tranquil retreat with breathtaking views of the ocean and lush tropical gardens.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the tranquility of Maui at this elegant condo in Maui Kamaole M104. Nestled in a lush 23-acre tropical setting, this quiet retreat offers convenient access to the renowned Kamaole Beach III, just a short walk away. The well-appointed unit features a bright, open layout with modern updates, including WiFi, partial air-conditioning, and a private washer and dryer for your convenience.
2 bedroom
2 bathroom
Sleeps 2
Hawaii> Kihei
Experience the tranquility and beauty of Maui at Wailea Ekolu Village, a peaceful retreat nestled on the Wailea Blue Golf Course. This charming two-story condo offers distant panoramic views of the Pacific Ocean and is set within a lush 18-acre tropical landscape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea, Maui
Welcome to your perfect vacation retreat at The Cliffs in Princeville. This beautifully designed, open floor plan condo offers all you need for a relaxing getaway. Located on the first floor, it provides easy access and convenience for all guests.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover this charming 2nd-floor oceanfront condo at Kapaa Shore Resort on Kauai's Coconut Coast. Perfect for up to 3 guests, including children and infants, this comfortable retreat offers breathtaking views and a range of amenities.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Kapaa
Escape to Hoyochi Nikko 202, a beautifully decorated two-bedroom condo nestled in the heart of West Maui. The master bedroom on the main level features a luxurious king-size bed and is air-conditioned for your comfort. A charming spiral staircase leads to a spacious lofted second bedroom with a cozy queen-size bed, perfect for families or friends seeking a relaxing getaway.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui retreat in this beautifully remodeled top-floor condo located in the prestigious Wailea Ekolu complex. As soon as you step inside, you'll be captivated by breathtaking ocean vistas and stylish upgrades that blend comfort with elegance.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Wailea
Experience a relaxing stay in this privately owned first-floor studio at the Outrigger Kauai Beach Resort and Spa, ideally located on the lush Garden Isle. The unit offers a patio facing the pool and resort area, with stunning ocean views, just a short walk to the sandy beach.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lihue
Escape to Maui in this cozy, home-like condo situated on the seventh floor of a well-equipped complex on Kaanapali Beach. The tastefully decorated interior offers an open layout and breathtaking Pacific Ocean views that set the perfect tone for your vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautifully renovated 2-bedroom, 1-bath condo in Kihei, Maui, perfect for families or friends seeking a tropical retreat. Located just steps from the pristine beach, this sunny and airy condo offers stunning ocean views from the furnished balcony, creating an ideal setting to unwind or enjoy meals outdoors.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to our stunning Balinese-inspired luxury property along Hawaii's serene Hamakua Coast. This oceanfront paradise offers a perfect blend of traditional Balinese architecture and modern amenities, creating a unique tropical escape.
5 bedroom
5 bathroom
Sleeps 12
Hawaii> Ninole
Experience the beauty of Maui from this private oceanfront condo at Hale Hui Kai, offering stunning panoramic views of the ocean and Keawakapu Beach. Located just 25 steps from the soft sands of one of South Maui's most cherished beaches, this second-floor unit provides a front-row seat to breathtaking sunsets and winter whale sightings.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kihei
Aloha! Welcome to this luxurious one-bedroom condo perched on the top 6th floor, offering breathtaking unobstructed views of the ocean bay and West Maui mountains from every room. The spacious floor plan features contemporary touches including granite countertops, stainless steel appliances, elegant tile floors, and a travertine bathroom, creating a perfect blend of comfort and style.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Kahana Sunset B9 offers an upgraded, spacious two-bedroom, two-bathroom condo with breathtaking ocean views from both the living room and the master bedroom lanai. Located on the sandy shores of Keonenui Bay, guests can enjoy swimming, snorkeling, and relaxing with sea turtles in a tranquil setting.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kahana
Escape to this stunning two-bedroom, two-bath condo at Waikoloa Beach Villas, offering breathtaking golf course views and luxurious amenities for a memorable Hawaiian getaway. Inside, you'll find a spacious living area perfect for relaxing after a day of adventure, a fully equipped modern kitchen, and a cozy dining space.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Welcome to Sealodge F9, a charming oceanfront apartment nestled in the desirable Princeville community on Kauai's north shore. Perched on the top floor, this condo offers breathtaking, unobstructed ocean views that you can enjoy from your private lanai or even from the comfort of your bed.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Princeville
Escape to the tranquil shores of Waikoloa Beach at this luxurious condo, offering a perfect blend of comfort and convenience. The spacious main living area is decorated with tropical-themed decor, plush furnishings, and a large TV, creating a welcoming atmosphere.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Waikoloa
