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 3350, 50
Experience the ultimate Maui getaway at Lokelani A201, a spacious 2-bedroom, 2-bath corner condo spanning 1130 sq. ft. with unobstructed panoramic ocean views. Located just 30 feet from a private sandy beach and surf break, the condo offers direct access to boogie boarding, paddleboarding, surfing, and snorkeling right outside your door.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to our charming Ekahi Maui condo, a perfect retreat for those seeking tranquility and convenience. This beautifully renovated one-bedroom unit features a comfortable sleeper sofa and two bathrooms, offering ample space for relaxation. Located on the ground floor, it boasts a generously sized lanai that extends onto lush grass, creating an ideal setting for outdoor lounging and dining.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Kauai Bay House at Koloa Kai offers a luxurious oceanfront experience on Kauai’s sunny South Shore. This beautiful three-story home features two bedrooms, two bathrooms, and spectacular views of Kukuiula Bay from nearly every room. Perfect for families or groups, the house boasts vintage hardwood floors, multiple seating areas, and expansive lanais ideal for outdoor dining and relaxing while enjoying the ocean breeze.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Cozy yet spacious. Intimate yet extensive. This one-bedroom features luxurious comfort for memories together and space to escape.Registration Number: TA-005-837-4656-01, GE-005-837-4656-01, 910570140000
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
Escape to paradise with this spacious 900 sq ft condo at Kiahuna Plantation, the only beachfront resort in Poipu. Just a one-minute walk to Kiahuna Beach, renowned as "the world's most beautiful beach" by The Travel Channel, this condo offers direct access to white sands and crystal-clear waters perfect for sunbathing, swimming, surfing, and snorkeling.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Koloa
5BA home offers a perfect blend of comfort and tropical charm. The property boasts lush landscaping and a thoughtfully designed layout that caters to large groups and extended families.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Princeville
Experience the ultimate Maui escape in Hokulani 530, a luxurious 1-bedroom suite at Honua Kai Resort. Newly launched on 1/27/2025 with reservations starting 2/6/2025, this suite offers breathtaking views of the West Maui Mountains. Perfect for small families or couples, the suite comfortably sleeps up to four guests, featuring a plush king-sized bed in the bedroom and a queen sofa sleeper in the living area.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Sugar Beach 311, a beautifully renovated and spacious one-bedroom condo ideally situated on the third floor with stunning front and side views of the ocean and beach. This non-smoking, professionally cleaned unit offers a relaxing Hawaiian escape with the soothing sounds of crashing waves and gentle ocean breezes from your private lanai.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
The Jungle Queen offers a distinctive escape for solo travelers or couples seeking tranquility and adventure. Nestled on a landlocked boat in the heart of a vibrant jungle, this retreat combines natural beauty with modern comforts.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kealakekua
Discover the ultimate Kauai getaway with three separate condos located in the prestigious Regency at Poipu Kai Resort. Situated on the 2nd and 3rd floors of a charming three-story building, these elegant units offer a combined capacity of up to 12 adults, making them perfect for family reunions, group retreats, or multi-family vacations.
13 bedroom
9 bathroom
Sleeps 28
Hawaii> Koloa
Welcome to one of Hawaii’s most secluded and luxurious oceanfront estates! Nestled on a 16-acre heart of palm plantation along the Scenic Route, this family vacation home offers unmatched privacy and breathtaking views. As you arrive through a gated entrance, you'll feel the stress melt away, surrounded by open agricultural lands and panoramic ocean vistas.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Papaikou
Maui County Rental permit - STPH 2013/0016
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Haiku-Pauwela
Discover the charm of Plumeria Cottage, a cozy yet spacious retreat designed in classic beach style. The cottage offers a sunny, roomy lanai perfect for relaxing outdoors and a large shaded grassy yard ideal for outdoor activities. Inside, the space is thoughtfully arranged to maximize comfort and convenience, featuring air conditioning to keep you cool during warm days.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kamuela
Escape to the charming Anini Reef Cottage on Kauai, a perfect blend of old Hawaii charm and modern comfort. This romantic one-bedroom beach hale offers a true island experience with stunning ocean views and lush tropical gardens. Inside, enjoy cool air conditioning in the bedroom after a day of exploring.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kilauea
Experience the ultimate Maui getaway in our exclusive Deluxe Ocean View villa at Ho'olei, situated in the top-row 'A' building. Perched above the rooftops of the Grand Wailea and Four Seasons resorts, this villa offers breathtaking, unobstructed panoramic ocean views that are truly unmatched in the area.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Wailea-Makena
Discover this beautifully appointed 1-bedroom studio nestled in the heart of Waikoloa Beach Resort on the Big Island. Designed for comfort and privacy, this spacious unit is part of the upscale Ainamalu subdivision, offering guests a serene retreat just minutes from the island's renowned beaches and attractions.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
The Kona Hideaway offers a luxurious and private oceanfront experience within the gated Kona Onenalo community. This beautifully renovated 3-bedroom, 3-bath villa combines mid-century modern and Polynesian-inspired design, providing a sophisticated yet relaxed Hawaiian ambiance.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience the beauty of Hawaii at Island Glow in Kailua Village. This beautifully remodeled 1-bedroom, 1-bath condo offers stunning ocean and mountain views from every room. Wake up on a new king bed and enjoy your mornings or evenings on the private lanai, where you can watch whales breach, dolphins play, and boats cruise by.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Kaanapali Alii 234 offers a beautifully updated one-bedroom residence with additional enclosed sleeping den and three bathrooms, providing ample space for families or friends traveling together. The primary bedroom features a king-sized bed, wall-mounted smart TV, and direct access to a large garden view lanai that overlooks lush tropical landscaping and offers peekaboo ocean views, perfect for relaxing outdoors.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this modern, fully renovated two-bedroom corner condo at Regency on Beachwalk, ideally located in the heart of Waikiki just steps from the beach, shopping, dining, and entertainment. Spanning 693 sqft plus two lanais totaling 910 sqft, this stylish condo offers comfort and convenience for up to six guests. Registration Number: GE-129-051-1872-02, 260030140027, TA-129-051-1872-02
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to the Hyatt Vacation Club, a luxurious oceanfront resort located on the southern end of Kaanapali Beach in Maui. This stunning villa offers a perfect blend of hotel luxury and condo comfort, ideal for families, couples, or groups seeking an upscale island retreat.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Relax in this beautifully remodeled large studio located in the Noelani Condominium Resort, just 6 miles north of the recent fire zone. Despite recent events, our beaches, sunsets, and ocean remain stunning and inviting. This legal short-term rental offers a unique Hawaiian retreat with direct ocean access, panoramic views, and a peaceful boutique setting with only 50 units.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Aloha! Welcome to our charming beachfront condo at Kiahuna Plantation Resort in Poipu, Kauai. Nestled on a lush 35-acre property, our first-story walk-out unit offers easy access to beautiful Hale O I'A Bay/Kiahuna Beach, just a short stroll away.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the ultimate Maui vacation in this beautifully updated and well-equipped condo, ideally located just across from the renowned Kamaole Beaches in Kihei. Situated on the south side of the building, away from busy streets, it offers a peaceful retreat with abundant sunshine from dawn to dusk.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Kaanapali Shores 905 offers a luxurious oceanview penthouse experience in Maui. This top-floor, one-bedroom condo comfortably accommodates up to four guests, featuring a king-size bed in the bedroom and a queen sleeper sofa with a twin sleeper in the living area.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui getaway in this beautifully remodeled and professionally decorated 1-bedroom, 1-bath condo at Kihei Akahi. Located on the 4th floor of the C building, just 100 yards from the soft sands and warm waters of Kamaole Beach II, this condo offers stunning ocean views and a Hawaiian-inspired interior adorned with artwork from local artists.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Kona Beach Estate offers a luxurious oceanfront escape along the beautiful Kona coast, situated on Aliʻi Drive in a secure gated setting. Designed with a blend of modern elegance and coastal charm, this private estate provides a tranquil and stylish environment for unforgettable Hawaiian vacations.
5 bedroom
7 bathroom
Sleeps 12
Hawaii> Holualoa
The Heart of Kolea offers an exceptional vacation experience with uninterrupted ocean views and luxurious amenities. Nestled in a prime location, this property provides a perfect retreat for those seeking tranquility and natural beauty. Wake up to the soothing sounds of the waves and enjoy stunning sunsets from your private balcony.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Waikoloa
Welcome to Hale Mahina B105, a beautifully upgraded ground-floor condo nestled in West Maui. This one-bedroom, one-bath unit comfortably sleeps four guests and is perfect for a relaxing beach retreat. Although it lacks air conditioning, ceiling fans and new floors keep the space cool and inviting.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience breathtaking ocean views from this beautiful Hanalei Bay Resort condo, featuring three private lanais overlooking Hanalei Bay and Bali Hai. Wake up to stunning sunrises with your morning coffee on the lanai and unwind in the evenings as you watch the vibrant Bali Hai sunsets, painting the sky with fire orange hues.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
This stunning luxury condo offers sweeping ocean views through expansive floor-to-ceiling windows, providing a truly spectacular living experience. Located in the prestigious One Ala Moana building, this residence combines comfort, convenience, and elegance. Registration Number: 230400220081, TA-041-821-6448-01, GE-041-821-6448-01
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Honolulu
Introducing Nihilani at Princeville 34C, a luxurious condo available through Makana Kauai Properties. Set on the beautiful north shore of Kauai, Nihilani showcases charming plantation-style architecture with spacious lanais, enclosed garages, and central air conditioning.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Discover this beautifully renovated second-floor condo at Lae Nani on Kauai's Coconut Coast. Featuring two bedrooms and two bathrooms, this unit offers stunning ocean views from the living room and master bedroom, making it an ideal retreat for families or couples.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
Welcome to Ohana Beach Retreat, a beautifully remodeled 2-bedroom, 2-bath townhouse located within the Keauhou Kona Surf & Racquet Club. This oceanfront complex offers a peaceful, family-friendly environment with gated access and lush 14-acre gardens.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the perfect blend of comfort and convenience in this beautifully remodeled studio located at Ilikai Marina, just minutes from Waikiki Beach. Spanning 427 square feet with a small 28-square-foot lanai, this condo offers stunning city and mountain views. Registration Number: 260100020141, TA-210-525-0304-01, GE-210-525-0304-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to this brand new contemporary single-story luxury estate nestled in the exclusive One Ocean community at Mauna Lani Resort. Designed for comfort and elegance, this expansive 5-bedroom, 5-bath home comfortably sleeps 10 guests, making it perfect for family vacations or accommodating business groups needing private workspaces alongside open areas for meetings and entertaining.
5 bedroom
6 bathroom
Sleeps 12
Hawaii> Kamuela
Situated just 7 miles north of Lahaina, this stunning condo at Royal Kahana offers breathtaking panoramic ocean views and spectacular sunsets from the lanai. Recently renovated, the unit features new window treatments, modern appliances, and elegant dark cherry cabinetry, creating a luxurious yet comfortable atmosphere.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this luxurious island-inspired home nestled in lush Princeville, just minutes from Hanalei Bay Resort and local beaches. The spacious 2800 sq ft interior and 1500 sq ft covered decks provide ample space for relaxation and entertainment.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
Escape to this exquisite beachfront paradise nestled in the heart of Hanalei Bay. This expansive property boasts a beautifully remodeled main house with four bedrooms and a spacious guest house with one bedroom, offering the ultimate Hawaiian retreat.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Hanalei
Experience a relaxing stay at our charming Kapalua Golf Villa, perfectly situated on the 4th hole of the Bay Course. The property offers street-level access with just three steps down to the entrance, creating a welcoming and convenient entry point.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Hale Leilani, a stunning Hawaiian-style home nestled on Kauai's north shore. This 3-bedroom retreat offers soaring ceilings and a lush, private tropical garden, creating an airy, peaceful atmosphere perfect for couples or families. Designed with a love for natural materials and beautiful artwork, the home immerses you in the island's natural beauty.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Discover the perfect family getaway at this charming ground-floor condo in the exclusive Ko Olina resort community. With unobstructed ocean views from the lanai, this property offers a serene setting just minutes from the beach, making it ideal for relaxing mornings with coffee and spectacular sunsets to end your day. Registration Number: TA-014-866-9952-01, GE-014-866-9952-01, 910560020062
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Bamboo Retreat is a serene getaway nestled in the lush foothills of Hilo, Hawaii, perched at 1400' elevation on a sprawling 3-acre property filled with bamboo, coffee, avocado, cacao, bananas, guava, and passion fruit trees. This peaceful haven is just 15 minutes from downtown Hilo, offering a perfect blend of privacy, natural beauty, and comfort.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hilo
Experience the perfect blend of accessibility, comfort, and stunning ocean views in this modern ground-floor Kona Town Condo. Designed for ease of access, the unit features a step-less entry with only a small step from the parking spot directly in front, making it ideal for all guests.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua-Kona
This beautifully maintained second-floor villa is located in the prestigious Ko Olina Kai Golf Estates and Villas, a gated community offering top-tier amenities and easy access to Hawaii's stunning beaches. Managed professionally by Ali'i Beach Rentals, this property requires a minimum stay of 30 days, making it ideal for extended getaways or seasonal stays.Registration Number: GE-113-732-4032-01, TA-113-732-4032-01, 910560050083
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Experience the ultimate Hawaiian getaway in this luxurious 2-bedroom, 2-bath condo located at Allure Waikiki on Kalakaua Avenue. Built in 2010, this residence offers a sophisticated blend of comfort and style, with high-end touches throughout. Floor-to-ceiling windows provide stunning panoramic views of Waikiki’s vibrant cityscape, which can be enjoyed from your private lanai while enjoying the gentle trade winds. Registration Number: TA-205-078-6816-01, GE-205-078-6816-01, 260130220025
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
The Magic Sands Retreat is a charming 3-bedroom, 2-bath cottage located just around the corner from the renowned Magic Sands Beach and the vibrant Ali'i Drive beach corridor. Within a 10-minute walk, guests can reach Kona’s most popular beach, making this home an ideal base for beach lovers and explorers alike.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Immerse yourself in the ultimate Wailea vacation at Penthouse H-101, a ground-floor, corner unit offering 2200 sq. ft. of luxurious living space and a 475 sq. ft. lanai perfect for entertaining or relaxing while watching breathtaking sunsets. Just a 2-minute walk to Wailea Beach, shops, and renowned restaurants, this property combines convenience with elegance.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kihei
Concierge Realty proudly offers luxurious villas at Marriott Maui Ocean Club Towers, available for 7-night weekly rentals with check-in on Saturdays. Experience the ultimate Hawaiian escape in this tropical paradise, located on the pristine shores of Kaanapali Beach.
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Lahaina
Welcome to your perfect Hawaii retreat in the heart of Kona! This beautifully remodeled condo offers a cozy, air-conditioned space with modern decor and stunning ocean views from the screened-in lanai. Located just a block from Kailua pier, beaches, popular restaurants, and shopping centers like Kona Coast and Lanihau, everything you need is within walking distance.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
