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 sleeps>=1 order by rating desc LIMIT 0, 50
Punahele Penthouse Condo offers a luxurious top-floor experience in the heart of Kailua-Kona. Located in the Kona Ali'i complex, this condo provides easy access to local attractions, shops, and restaurants within walking distance. The unit features a spacious lanai where you can relax with your favorite drink, listen to ocean waves, and watch dolphins and whales in the bay during sunset.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Welcome to your perfect Kauai getaway in this beautiful ground-floor one-bedroom, two-bath Lae Nani Condo, Unit #317, nestled on the stunning Garden Isle of Kauai! This condo offers a blend of comfort, convenience, and tropical charm, making it an ideal choice for couples, families, or small groups seeking a relaxing vacation.
1 bedroom
4 bathroom
Sleeps 4
Hawaii> Kapaa
Guests walk into a space that immediately evokes 'wow' moments, thanks to a gourmet kitchen, expansive ocean views, and large bedrooms. Ideal for family reunions, this private estate offers cool mountain air at night and warm ocean breezes during the day.
6 bedroom
6 bathroom
Sleeps 18
Hawaii> Kailua-Kona
Discover your ideal Maui getaway in our tranquil North Shore residence, perfect for families or couples. This charming 800 sq ft home offers a tropical beach vibe with modern updates and comfortable amenities.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Haiku
Experience the ultimate Maui getaway in this beautifully renovated oceanfront condo on Ma'alaea Bay, a winter sanctuary for humpback whales. The property offers a perfect blend of island charm and modern comfort, making it an ideal retreat for relaxation and adventure.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maalaea
Discover this highly sought-after ground floor corner condo in Maui, offering ultimate privacy and just steps from a nearly private beach. The location provides panoramic ocean views with Lanai and Molokai in the background, perfect for relaxing and taking in breathtaking sunsets, sunrises, rainbows, and even moonbows.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience the perfect family escape in the heart of Princeville at Hanalei, Kauai. Our spacious vacation home offers a peaceful retreat surrounded by lush gardens and scenic landscapes. Designed with comfort and family fun in mind, the property features large bedrooms with cozy bedding for restful nights after adventurous days.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Discover this beautifully renovated oceanfront 2-bedroom, 2-bath condo in Maui, offering spectacular ocean and island views from the living room, lanai, and master bedroom. Wake up to breathtaking vistas without leaving your bed, as the master bedroom faces the ocean.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate Hawaii getaway in this modern beachfront penthouse located right across from Kahaluu Beach, renowned for its vibrant marine life and over 100 species of tropical fish and turtles.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Step into paradise at Kauhale Makai, a beautifully remodeled 2-bedroom, 2-bathroom condo nestled on Maui's sunny south coast. This fourth-floor unit offers breathtaking ocean vistas from the primary bedroom, living room, kitchen, and lanai, where you can relax while watching whales, windsurfers, and swaying palm trees.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to our stunning, newly renovated 2-bedroom, 2-bathroom beachfront home in Maui. This end-unit offers unobstructed panoramic views of Kaho’olawe Island, Molokini Crater, and the West Maui Mountains from the lanai, living room, and dining area.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this beautifully furnished 600 sq. ft. upstairs suite in Kailua, Oahu, blending modern elegance with beachy flair. Perfect for guests seeking comfort and convenience, it features a spacious bedroom with a plush king-size pillow top bed, dark wood blinds, a 40" TV, and split air conditioning to keep you cool. Registration Number: 420200410000, TA-071-652-3520-01, GE-071-652-3520-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Experience a luxurious stay at our beautifully renovated 2-bedroom, 2-bath condo located at Luana Kai in sunny Kihei, Maui. This oceanfront resort offers pristine grounds and a peaceful, park-like setting, with direct access to a gorgeous beach just steps away.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This charming end unit condo is situated on the first floor at the back of a lush Hawaiian resort, offering serene garden views and tropical breezes from a spacious lanai. Recently renovated, the condo features a comfortable master bedroom with a king-size bed and a main living area equipped with a wall bed featuring a queen-size Tuft and Needle mattress, making it ideal for both couples and small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
The Artist's House, Permit # STLA 2017/0002, offers a cozy home away from home with comfortable furnishings and excellent amenities. The outdoor deck provides breathtaking Lana'i sunsets, complete with a glass-top dining table for six and a barbecue for outdoor meals.
3 bedroom
3 bathroom
Sleeps 4
Hawaii> Lanai
Discover this stunning corner villa nestled inside the Beach Tower at Ko Olina, offering unrivaled proximity to the beach with just a 20-foot walk to the lagoon. This spacious, newly refurbished condo features a warm Koa wood design, top-of-the-line furnishings, and modern electronics, creating a perfect retreat for families or friends seeking luxury and comfort. Registration Number: TA-134-204-4160-01, GE-134-204-4160-01, 1910570090191
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Emmalani Court offers a peaceful retreat in Princeville, nestled in a lush setting on Hawaii's North Shore. This small, quiet complex is ideally located near the Princeville Resort, with easy walking access to beaches, the Bali Hai lookout, and the charming town of Hanalei.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Experience breathtaking sunsets from this beautifully renovated Kailua-Kona condo, just steps from the vibrant village. The property features air conditioning in both the living room and bedroom, ensuring comfort during your stay. Sunset in Paradise offers postcard-worthy ocean views right from the lanai, where you can unwind with a drink and listen to the waves or enjoy your morning coffee while watching cruise ships arrive.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Unit 1604 at the Fairways at Mauna Lani, a spacious 1646 square foot condo perfect for your Hawaiian getaway. The property features two bedrooms located upstairs, each with their own full en suite bathrooms, offering privacy and comfort for two couples or a small family.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
Located on the west side of Hawaii's Big Island, our custom-built studio cottage offers breathtaking ocean and coastline views from its spacious lanai. Perched at 1400 ft. on Mt. Hualalai, the 500 sq. foot retreat combines comfort with natural beauty.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Holualoa
Discover Hale Lanui, a stunning ocean-view retreat nestled in South Maui. This spacious two-story home offers 2700 SF of comfort, with four bedrooms, including two king beds and versatile twin options, perfect for families or groups. The home boasts three large bathrooms, a fully equipped open kitchen with high-end appliances like a Sub Zero refrigerator, and ample cookware and dishes for all your culinary needs.
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kihei
Enjoy a serene stay in this fourth-floor oceanfront condo on Hawaii's south shore. Wake up to the soothing sounds of the surf and stunning ocean views from your lanai. The stylish, contemporary furnishings create a cozy yet elegant atmosphere, perfect for unwinding.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> poipu
This stunning single-story home located just minutes from Kailua Kona offers an exceptional Hawaiian vacation experience. Recently fully renovated, the house features a spacious 2500 sq ft open floor plan and a 1200 sq ft covered lanai, perfect for relaxing or entertaining.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Welcome to Unit 1602 at the Fairways at Mauna Lani, a luxurious gated community nestled between the Fairmont Orchid and Mauna Lani Bay Hotel. This bright and airy condo offers a spacious, open layout in a lush, park-like setting, giving you the feel of your own private backyard.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waimea
Our Honu Villa in the Villages at Mauna Lani offers a luxurious, single-level Hawaiian Kahili home designed for relaxation, privacy, and enjoyment. 5-bath home is perfect for families or groups seeking an island getaway.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Experience the best of Maui at Milowai in Maalaea with this beautifully remodeled ground-floor condo offering direct oceanfront access. The unit is ideally located just 20 minutes from most island attractions, making it perfect for exploring Maui's diverse sights and activities.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuke
Experience the beauty of Hawaii in this spacious and beautifully renovated condo. With high ceilings, abundant sunlight, and gorgeous ocean views, you'll enjoy a luxurious stay. The elevated third floor offers a stunning lanai overlooking manicured golf courses, tropical flowers, natural lava formations, and lush palm trees, all set against a vibrant ocean backdrop.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the perfect blend of luxury and nature at Hale Mala Pua Garden Home, a spacious plantation-style house nestled on three acres within the lush Gardens at Botanical World Adventures in Hakalau. This 2,200 sq ft home offers two bedrooms, each with private en-suite bathrooms, one featuring a cozy futon for extra guests.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Hakalau
Welcome to Hale Mahina A-302! This stunning oceanfront condo offers unobstructed views of Molokai and Lanai, set on a secluded gated white-sand beach perfect for a peaceful Maui escape.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Escape to this beautiful 2-bedroom condo nestled in Hawaii's lush tropical landscape. Located just an 11-minute walk from the stunning Maalaea Harbor, this retreat offers easy access to the West Maui Mountains and Wailea Beach, both within a 30-minute drive.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Welcome to the Poipu Beach House, a charming historic property dating back to the 1920s, located just steps from the beautiful Poipu Beach. This plantation-style home immerses you in old Hawaii charm with original wood floors, lava rock footings, and a 100+ year old dry-stacked lava wall, offering a unique and authentic Hawaiian experience.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
This charming 1-bedroom, 1-bath ground floor condo offers ample space and modern comforts. Fully renovated with beautiful furnishings inside and out, it features numerous windows that open to a spacious patio, perfect for relaxing or entertaining.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> waikoloa village
Kauai Haven offers a luxurious retreat nestled on a bluff overlooking the spectacular Pacific Ocean. This award-winning, designer-designed condo combines comfort with breathtaking vistas, providing a perfect setting for your Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Your Island Paradise Awaits You! 5 bathrooms, with an attached garage and beautifully decorated interiors. Located just steps from a resort-style pool featuring a lava rock waterfall, Jacuzzi spa, and four BBQs, it’s perfect for relaxing and entertaining.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover this charming oceanfront home in East Hawaii, offering panoramic views and close encounters with whales, dolphins, and surfers. Situated just a few miles from Hilo, the house sits atop a dramatic shoreline, providing the unique sensation of being on a ship with waves lapping below.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Hilo
Welcome to this exquisite top-floor 2-bedroom, 2-bath condo nestled on the Ocean Bluff at Alii Kai Resort. Designed for travelers seeking luxurious comfort and stunning scenery, this property offers both ocean and tropical garden views, creating a serene escape in paradise.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
5-bath home nestled against an old sugar cane field, offering stunning ocean views and modern comforts. This charming Maui retreat is perfect for guests seeking peace and serenity while remaining close to the lively town of Paia and its renowned beaches.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Paia
Experience the ultimate Hawaii getaway at Puako Pau Hana, a charming and recently remodeled vacation home nestled in the heart of Puako. Just a short stroll from one of the area's most beautiful beaches, this residence offers a perfect blend of comfort, style, and island charm.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimea
Escape to paradise in this elegant third-floor condo at Maui Sunset, located in Kihei. The unit offers breathtaking ocean views right from your balcony, where you can enjoy the serene beauty of the Pacific. Nestled against lush landscaping and the largest pool in Kihei, this condo combines comfort and convenience for an unforgettable Hawaiian vacation.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Maui Kaanapali Villas, a stunning oceanfront resort nestled on 11 acres of lush tropical gardens and pristine beachfront. Our exclusive corner one-bedroom, two-bathroom unit on the 5th floor of the Aloha Tower offers breathtaking panoramic views of the ocean, visible from the living room, open kitchen, and bedroom.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This beautifully remodeled single-story house offers a peaceful retreat in the heart of South Kihei, Maui. Featuring ultra-quiet split AC systems in every room, ceiling fans, and a whole house water filtration system, comfort is guaranteed. The property boasts resort-style landscaping and lighting, creating a lush tropical oasis perfect for relaxing outdoors.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kihei
Aloha and welcome to our tropical paradise in beautiful Kauai, Hawaii! Indulge in an extraordinary couples' getaway at our top-floor ocean and beachfront studio condo in Kapa'a. Just 150 steps from our condo's front door, the beach awaits! Offering breathtaking, uninterrupted views of the Pacific Ocean, our serene retreat is the perfect haven for couples seeking a memorable holiday experience.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Escape to Maui and unwind in this charming second-floor condo, perfect for your island getaway. The unit offers breathtaking ocean and mountain views, with the soothing sounds of the surf to lull you to sleep. Step onto your private lanai to enjoy morning coffee, watch whales in winter, or marvel at the sparkling lights of Ma'alaea Harbor at sunset.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the best of Waikoloa Beach Resort with this stunning oceanfront condo at Halii Kai. Located directly across from the clubhouse, unit 16B offers breathtaking views of the Pacific Ocean, golf courses, and spectacular sunsets from your private lanai.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa
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
Experience the tranquility of this spacious Princeville home nestled on the Makai Golf Course, offering over 3100 sq ft of comfort and style. The property is located in a serene residential neighborhood, providing an ideal setting for relaxation and outdoor enjoyment.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Princeville
Welcome to your tropical paradise in Kauai, Hawaii! This top-floor ocean view studio condo in Kapa'a offers an ideal couples' retreat just 160 steps from the beach. Wake up to stunning Pacific Ocean views from your private, covered lanai, where the soothing sounds of waves and tropical breezes create a perfect setting for relaxation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Experience the comfort and style of this beautifully renovated condo at The Whaler, perfectly situated on Kaanapali Beach. The unit has been extensively upgraded, including a new 55" Samsung LED Smart TV with Dynamic Crystal Color, a modern Rohl kitchen and bathroom faucet, and a Grohe hand-held shower head for easy bathing.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to this charming 3-bedroom, 2-bathroom duplex just a 3-minute walk from the stunning Magic Sands Beach. Recently remodeled and professionally decorated, this vacation home offers all the comforts needed for a relaxing stay, including air conditioning in every bedroom and high-speed WiFi at 300 Mbps.
4 bedroom
2 bathroom
Sleeps 7
Hawaii> Kailua-Kona
Welcome to our beautiful ground-floor condo at Poipu Sands, purchased in 2013. This spacious unit features a large master bedroom with A/C and an en-suite bath with a walk-in shower, plus a convenient half bath. The expansive lanai offers the perfect spot for morning coffee or romantic dinners while enjoying garden views and gentle breezes.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Koloa
