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 3850, 50
Welcome to this completely renovated second-story home nestled in the scenic hills of Holualoa, known for its world-famous Kona coffee. Everything inside is brand new, including beds, appliances, cabinets, and flooring, ensuring a comfortable and modern stay.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Holualoa
Discover the essence of island living at Unit 230 of the Country Club Villas in Kona, Hawaii. This charming 2-bedroom, 2-bathroom retreat offers an idyllic getaway in the desirable Keauhou area, perfect for experiencing the tranquility and beauty of Kona's paradise.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Immerse yourself in the spectacular oceanfront setting of Poipu Shores 306A, where stunning views of the crashing waves and cove below create an unforgettable vacation experience.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Experience the ultimate Maui getaway in Hokulani 246, an elegant two-bedroom suite offering breathtaking partial views of the Pacific Ocean and the majestic West Maui Mountains. Situated as the second back from the oceanfront, this suite provides an ideal vantage point to enjoy Maui's renowned natural beauty.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This newly built, split-level residence offers a perfect blend of luxury and comfort with an open floor plan, top-tier finishes, and private amenities. Inside, central air conditioning, a Bose sound system, warm hardwood floors, vaulted ceilings, and abundant natural light create a welcoming atmosphere.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Welcome to BlueTideVillas! This expansive (over 1650 sq ft), top-floor corner penthouse offers 3 bedrooms and 3 bathrooms, featuring two private lanais perfect for relaxing with tropical drinks. Its prime location in the coveted Regency Resort in Poipu makes it one of the most desirable units, thanks to central air conditioning and proximity to the beach.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Experience your own slice of paradise at this luxurious oceanfront retreat in Puako. The property offers a lush yard with a hammock where you can unwind while listening to the soothing waves. Prime snorkeling and diving are just steps from the yard, making it perfect for water enthusiasts.
5 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Welcome to Poipu Crater 20, a unique and secluded two-bedroom condo nestled within a dormant volcanic crater on the beautiful island of Kauai. Surrounded by lush tropical gardens and vibrant birdlife, this retreat combines the authentic charm of old Hawaii with modern comforts.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Discover this spacious 1-bedroom apartment in the iconic Ilikai Hotel, offering stunning ocean, lagoon, and beach views from a large 120 sqft balcony. Located on the 6th floor, this 500 sqft unit is situated in the quieter side of Waikiki, providing a peaceful retreat while remaining within walking distance to beaches, shopping centers like Ala Moana, restaurants, nightlife, and public transportation. Registration Number: GE-175-896-8832-01, TA-175-896-8832-01, 2601000700000
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
Experience the beauty of Maui in this spectacular oceanfront condo at Mana Kai Resort. Bright and modern, unit 705 offers breathtaking views, recent upscale renovations, and a thoughtfully designed open-concept layout. The primary suite, with its ocean-facing king bed and shoji doors, provides a tranquil retreat where you can listen to the waves as you drift to sleep.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Looking for the ultimate tropical escape? Maui Turtle Cove offers a spacious 5-bedroom home nestled in lush tropical landscaping with stunning ocean views, including the majestic Molokai Island. Just a short walk down a private path leads you to a tranquil cove where Hawaiian green sea turtles frequently visit, perfect for swimming and relaxing.
5 bedroom
4 bathroom
Sleeps 14
Hawaii> Lahaina
Welcome to Papala Place at Kahakai Estates by Gather, a stunning four-bedroom, three-and-a-half-bathroom home nestled in the heart of Kailua Kona. This beautifully appointed residence offers a perfect blend of elegance, comfort, and tropical charm, making it an ideal retreat for families or groups seeking a luxurious Hawaiian escape.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua Kona
Unit 321 at Honua Kai in Konea Tower offers a relaxing ocean view experience on Maui's beautiful coast. Located on the third floor, this suite overlooks the peaceful adult pool and is just steps from Dukes Restaurant and Bar, making it ideal for a luxurious and convenient stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to your dream vacation in Poipu, Kauai! Nestled within the prestigious Regency @ Poipu Kai Resort, this newly furnished and air-conditioned 5-bedroom luxury condo offers an unparalleled blend of comfort, style, and breathtaking scenery.
5 bedroom
3 bathroom
Sleeps 12
Hawaii> Koloa
Experience the magic of Maui on your own terms with our camper vans. Perfect for exploring secluded beaches, waking up to breathtaking sunrises on Haleakala, and customizing your Hawaiian journey. Whether you're seeking adventure or relaxation, our camper vans offer the freedom to discover Maui’s hidden gems and create unforgettable memories.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover the enchanting Ginger Studio at Hawaii Sheffield House, a charming and legal bed and breakfast in Kailua with over 30 years of hospitality. Located at 131 Kuulei Road, this ground-floor unit offers a private entrance and a lush 'Secret Garden' setting with night lighting and a bubbling fountain in a Chinese steamer pot, creating a magical tropical ambiance.Registration Number: TA-137-360-9984-01, GE-137-360-9984-01, 430720040000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
5M home on 15,000 sq ft lot in one of the most desired locations on Oahu's south shore. Nestled on the slopes of Diamond Head just outside of Waikiki, this exquisite estate is a 5-MINUTE WALK TO A SECLUDED WHITE SAND BEACH with excellent surfing. Registration Number: 350020180000, GE-047-203-9424-01, TA-047-203-9424-01
4 bedroom
6 bathroom
Sleeps 9
Hawaii> Honolulu
Welcome to Paradise Found! This stunning top-floor Kealia Resort condominium offers breathtaking panoramic ocean views from every room, nestled directly on Sugar Beach, one of Maui's most beautiful beaches.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Legal Vacation Rental: Permit Number STKM2020/0002
6 bedroom
6 bathroom
Sleeps 12
Hawaii> Kihei
Welcome to your ideal island getaway on the North Shore of Hawaii! This stunning 4-bedroom, 4-bathroom home offers panoramic ocean views from three bedrooms and a modern, fully equipped kitchen. Located on one of the best beaches in the area, you’ll have direct access to world-famous surf breaks like Pipeline, Backdoor, Rocky Point, and Sunset Beach, all within a short walk or bike ride. Registration Number: TA-044-900-1984-01, GE-044-900-1984-01, 1590190490000
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Haleiwa
D301 is a beautifully located corner unit offering breathtaking full ocean views from a 2-bedroom, 2-bath condo with optional air conditioning for added comfort. Located on the third floor, guests should be prepared to walk up three flights of stairs, as there are no elevators.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kihei
Discover your perfect Hawaiian getaway at this oceanfront hotel on the Kona Coast of the Big Island. The 420 sq ft room features two double beds, daily housekeeping, a coffee maker, mini-fridge, and all linens and essentials provided, making your stay comfortable and convenient.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to this exquisite 2-bedroom, 2-bath oceanfront residence at Polo Beach Club, offering a luxurious Maui vacation experience. Newly launched on 10/25/24, this stunning property is available for reservations starting 1/6/25. Located in the highly sought-after Wailea area, the residence boasts panoramic Pacific Ocean views from nearly every room, creating an awe-inspiring backdrop for your getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Built in 2022, this spacious luxury 1-bedroom suite is part of a beautifully designed single-family house with its own private entrance, nestled on a one-acre property in Hawaii Paradise Park, Keaau. The retreat offers a perfect escape from the hustle and bustle, providing a serene environment with modern amenities and easy access to some of Hawaii's most famous attractions.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Welcome to your perfect Kauai getaway at Poipu Sands, a beautifully renovated ground floor condo nestled in the Poipu Kai Resort. This spacious one-bedroom, one-and-a-half-bath unit offers approximately 1000 sq. ft. of interior living space with high ceilings and an open floor plan that creates a bright, airy atmosphere.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Koloa
Experience the perfect Hawaiian getaway at Hanalei Bay Villas #36, a spacious and beautifully remodeled three-bedroom, two-and-a-half-bath stand-alone condominium in Princeville. This inviting home offers breathtaking views of the Hanalei Mountains and the expansive Pacific Ocean, providing an ideal setting for relaxation and adventure.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Discover the ultimate Maui vacation experience at Southwest Sunsets, a beautifully decorated beachfront condo located in the Sugar Beach Resort in Kihei. Sitting just 20 feet from the ocean, this unit offers breathtaking southwest sunsets right from your lanai, where you can enjoy seating for four and panoramic views of the ocean.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover Noelani 117, a cozy ground-floor studio condo nestled in the heart of Maui’s stunning coast. This oceanfront unit comfortably accommodates up to 3 guests, offering a serene retreat with breathtaking panoramic ocean views from the lanai and large sliding doors that fill the space with natural light.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Building B at Makena Surf stands out as the premier beachfront location, nestled amid lush tropical gardens. From our condo (B202), you can enjoy endless ocean vistas, witness seasonal whale breaches, and experience breathtaking sunsets. Step directly onto the long, sandy beach where you can swim, snorkel with turtles, or have fun riding the waves on a boogie board.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this beautifully renovated condo unit at the Ilikai Hotel, offering a perfect blend of comfort and convenience. With 2 bedrooms, 2 full baths, and spacious 1,100 sq ft, this unit comfortably accommodates up to 8 guests. Inside, you'll find all-new appliances, stylish furniture, flat-screen TVs, a leather pull-out sofa, and modern decor that create a welcoming atmosphere. Registration Number: TA-152-348-8256-01, GE-152-348-8256-01, 260100070009
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience the ultimate Maui getaway at Honua Kai's Luana Garden Villas, a luxurious beachfront retreat on Kaanapali Beach. Spanning over 2,000 square feet, these modern villas are designed for comfort and style, offering a perfect blend of island elegance and family-friendly amenities.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Kaanapali Alii 246 offers a luxurious and spacious retreat in the heart of Maui's Kaanapali Beach area. This 1,807 sq ft corner unit is designed for both relaxation and entertaining, featuring a seamless open floor plan that combines the kitchen, dining, and living areas into one expansive space.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the beauty of Maui at Wailea Ekahi 3E, a charming second-floor one-bedroom condo nestled within the lush Wailea Ekahi Village. This inviting retreat offers a perfect blend of comfort and scenic views, ideal for a relaxing vacation.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to Maui Eldorado A206, a beautifully remodeled top-floor condo in the heart of Kaanapali. This spacious 1200 sq ft one-bedroom offers stunning ocean, Molokai, and Lanai views with western exposure, perched above the renowned Royal Kaanapali Golf Course.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Experience the relaxed luxury and authentic island lifestyle at Kahala Breeze, a spacious four-bedroom home nestled in the lush surroundings of Oahu. This elegant residence combines natural beauty with modern comfort, making it an ideal retreat for families or groups seeking a memorable Hawaiian getaway.Registration Number: 350030310000, TA-065-534-9760-01, GE-065-534-9760-01
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
Discover this luxurious three-bedroom villa nestled within the prestigious Ko Olina Resort on the west coast of Oahu. Designed for comfort and elegance, the villa offers a spacious and inviting atmosphere perfect for families, couples, or groups seeking a memorable Hawaiian escape. Registration Number: GE-108-964-9152-01, 910560050113, TA-108-964-9152-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to our newly renovated, stunning seaside single-story 2-bedroom, 2-bath condo at the highly-rated Kamaole Sands resort. Located just yards from the picturesque Kamaole III Beach, known as Maui's "postcard beach," this spot offers fantastic opportunities for snorkeling, swimming, boogie boarding, and family fun.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover this charming and exclusive 1-bedroom, 1-bathroom condo at Waikiki Banyan Makai Tower, ideally located just a short stroll from Waikiki's famous sandy beaches. The unit comfortably sleeps up to four guests, featuring a plush king-sized bed in the bedroom and a cozy queen sofa bed in the living room, perfect for families or couples seeking a relaxing getaway.Registration Number: GE-011-768-6272-01, 260250050189, TA-011-768-6272-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to this charming Princeville home on Kauai, offering a bright and breezy atmosphere with soaring ceilings and exposed beams that create an inviting space. The fully equipped kitchen with full-size appliances makes meal prep a breeze, while the wraparound lanai provides ample room for outdoor dining, grilling, and soaking up the tropical sunshine.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Princeville
Alihi Lani is a beautifully remodeled oceanfront property nestled in a small, exclusive complex of six units, celebrated as a 'Real Gem' by the Ultimate Kauai Guidebook. The second-floor location offers direct, unobstructed views of the Pacific Ocean, just less than 50 yards from the pounding surf, making it perfect for whale watching, turtle sightings, and dolphin viewing year-round.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to your dream getaway in the exclusive gated community of Hali'i Kai! This exquisite top-floor, oceanfront condo offers sweeping 180-degree views of the Pacific, making it a perfect retreat for those seeking luxury, tranquility, and authentic Hawaiian charm.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to your dream vacation at Holualoa Bay Villas, located on the iconic Ali'i Drive in Kona. This newly listed, fully remodeled 2-bedroom, 2-bath corner condo offers breathtaking ocean views and direct beach access just across the street. The spacious wrap-around lanai invites you to relax and enjoy the stunning scenery, whether dining indoors or outside.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to your stylish and modern oceanfront retreat in Kihei, Maui. Recently remodeled in December 2021, this spacious one-bedroom, one-bath condo offers luxurious touches throughout, paired with breathtaking ocean views. Located in Kauhale Makai, affectionately known as "Village by the Sea," the property is nestled against a charming beach ideal for catching spectacular sunsets.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Pursue your dream vacation in this picturesque Hawaiian estate located on the stunning Kohala Coast. The property boasts multiple bedroom suites with outdoor garden showers, offering a perfect blend of luxury and natural beauty. The residence features an impressive indoor and outdoor chef's kitchen, ideal for gourmet cooking and entertaining while enjoying breathtaking ocean views from the expansive lanai.
4 bedroom
6 bathroom
Sleeps 10
Hawaii> Kailua-Kona
Welcome to Kamaole Sands 7-405, a luxurious top-floor condo nestled in the heart of South Kihei, Maui. This stunning 2-bedroom, 2-bathroom unit offers a perfect blend of comfort and tropical elegance, ideal for up to six guests. Inside, you'll find upscale decor, high-end furnishings, and artwork that create an inviting, sophisticated atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to this charming island retreat at Quam Properties 14B1, a beautifully remodeled one-bedroom condo offering breathtaking ocean views and vibrant tropical decor. The spacious living area features oversized windows that flood the space with natural light and showcase stunning seascapes, creating a serene and inviting atmosphere.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to our stunning oceanfront condo in Maui, an ideal retreat for families and couples seeking a luxurious and convenient vacation. Located at the renowned Sands of Kahana Resort, this spacious 2-bedroom, 2-bath unit offers breathtaking views of Lana'i and Molokai from every room, including the living room, master bedroom, kitchen, and dining area.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Come unwind and indulge in this spectacular ocean-view luxury villa situated directly across from the Grand Wailea Hotel & Spa on the beautiful island of Maui. Located on the third row at Ho'olei, this elegant two-story residence offers breathtaking Pacific Ocean views from both upper and lower levels.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kihei
Enjoy a beautifully remodeled and airy 1-bedroom condo at Kiahuna Plantation and Beach Bungalows, perfect for a relaxing Kauai getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> KOLOA
2-acre lot with breathtaking ocean views. This spacious single-level home offers 2000 sq/ft of luxurious living space, perfect for families or groups seeking comfort and style. Inside, you'll find two main bedrooms each equipped with king-sized beds and attached bathrooms featuring Italian Poggesi fixtures, ensuring a spa-like experience.
5 bedroom
3 bathroom
Sleeps 11
Hawaii> Waikoloa Village
