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 9800, 50
Experience the ultimate Hawaiian getaway in this stunning four-bedroom, four-and-a-half-bath home situated overlooking the Royal Kaanapali Golf Course with breathtaking ocean vistas. Inside, the spacious living areas are tastefully decorated with an array of artwork, creating a warm and inviting atmosphere.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kaanapali
Welcome to Hale Kamaole 107, a spacious and beautifully updated ground floor unit located in the lower part of the complex. Just steps from the pool, BBQ area, and office, this condo offers the perfect blend of comfort and convenience. The ocean is a mere 200 steps away, providing easy access to the stunning beaches of Maui.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Maui getaway in this beautifully maintained ground-floor condo offering spectacular direct oceanfront and beachfront views. The unit comfortably accommodates up to 8 guests with 2 bedrooms and 2 bathrooms, featuring a luxurious king bed, queen beds, and a sofa sleeper.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Wailuku
Welcome to Papakea F404, a charming and modern oceanfront condo nestled in the heart of Maui's lush paradise. This 2-bedroom, 2-bathroom residence comfortably hosts up to 5 guests, making it an ideal retreat for families or friends seeking a relaxing getaway.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Lahaina
Experience breathtaking sunrise and sunset vistas from this beautifully renovated top-floor suite in Princeville, Kauai. The spacious layout includes a private master bedroom with a king bed, TV, and en-suite bathroom, along with a cozy lanai perfect for morning coffee.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Immerse yourself in a charming studio-style condo offering picturesque views of lush tropical gardens and a sparkling swimming pool. Located directly across from a top-rated snorkeling beach and an oceanfront restaurant, this cozy retreat provides the perfect base for exploring the area.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Poipu
Welcome to your dream Hawaiian escape! This newly remodeled 2-bedroom, 2-bath home on Kauai's stunning North Shore offers a perfect blend of comfort and beauty. Owned and designed by T-Baer, who brings a personal touch to create a relaxing, enjoyable atmosphere for friends and family.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to Kihei Beach #207, a beautifully remodeled condo nestled within a peaceful six-story beachfront property in North Kihei. This condo offers breathtaking panoramic views of Kahoolawe, Molokini, and the sparkling Pacific Ocean from your private lanai, perfect for unwinding while watching vibrant sunsets or seasonal whale sightings.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kihei
Escape to our charming Maui condo nestled right on Napili Bay, listed as one of Maui's best-kept secrets by Frommer's Maui Guide. This oceanfront retreat offers a serene atmosphere with no tall buildings, just lush palms and the gentle sound of waves.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Knutson & Associates proudly offers Kona Plaza#402, a stunning top-floor condo located in the heart of Kailua Village. This one-bedroom, one-bath unit comfortably sleeps two and boasts an incredible ocean view from a spacious lanai. The condo features a king-sized bed, central air conditioning, Wi-Fi, washer/dryer, fully equipped kitchen, elevator access, and underground covered parking, ensuring a convenient and comfortable stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the ideal Maui vacation at Sugar Beach Resort 431, a beautifully appointed beachfront condo with spectacular ocean and garden views. Located directly on five miles of pristine white-sand beach, this resort offers guests the perfect blend of relaxation and convenience.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to Kona Plaza Unit 220, your ideal home base for exploring the beautiful beaches and vibrant culture of Kailua Kona, Hawaii. This charming one-bedroom, one-bathroom condo combines comfort and convenience, offering a welcoming Hawaiian ambiance that makes you feel right at home from the moment you arrive.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
The Palms at Wailea offers an idyllic Maui getaway with a perfect blend of comfort and luxury. Located above the stunning Keawakapu Beach, this beautifully furnished condo provides a spacious, air-conditioned retreat that feels like home. With approximately 1,200 square feet of living space, the unit features a modern kitchen with newer appliances, ideal for preparing meals during your stay.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Immerse yourself in the beauty of Maui with a stay at Kihei Akahi C413, a charming one-bedroom condo ideally located just across the street from Kamaole Beach Park II, one of Maui’s most popular swimming and scenic beaches. The property boasts a prime location with breathtaking sunsets and miles of pristine white sandy beaches perfect for early morning strolls.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Aloha and thank you for considering the Green Turtle Hideaway for your stay on Oahu's North Shore. This recently updated, beautifully furnished one-bedroom condo is nestled within the Turtle Bay Resort complex on the scenic grounds of Kuilima Estates West. Registration Number: TA-096-380-1088-01, GE-096-380-1088-01, 570010290128
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Experience a tranquil stay at this charming studio apartment on the Gold Coast, perfectly situated near Waikiki and Diamond Head. The space features a comfortable queen bed and an open floor plan that maximizes natural light and breezes. The bathroom includes a bath and shower combo, while a cozy sofa bed in the living area provides extra sleeping options for guests.Registration Number: GE-184-011-1616-01, TA-184-011-1616-01, 310320290028
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Mauna Lani KaMilo #217 by Gather, a charming four-bedroom, three-bathroom home nestled in the prestigious Mauna Lani Resort on the stunning Kohala Coast of Kamuela, HI. This property is now offering extended stays at reduced rates, making it an ideal choice for those seeking a longer tropical escape.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kamuela
Experience the beauty and tranquility of Molokai from this newly renovated 1-bedroom ground floor condo at Wavecrest Resort. Designed with attention to detail, the condo features soothing colors, crown molding, and a beautifully tiled step-in shower.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaunakakai
5-acre resort set amidst lush tropical landscaping. This second-floor studio condo, located in the desirable Lanai Building, offers a serene retreat just steps from the beach. Wake up to stunning ocean views from your private lanai, ideal for morning coffee or relaxing evenings.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kapaa
Welcome to Hale Ono Loa 203, a beautifully appointed 1-bedroom, 1-bathroom condo situated on the second floor, offering breathtaking ocean views and an expansive lanai perfect for relaxing and dining outdoors.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Hawaiian retreat at Holualoa Bay Villas, Unit#105, a stunningly remodeled 2-bedroom, 2-bath condo nestled in the heart of Kailua-Kona. Just across the street from the breathtaking Holualoa Bay, this residence offers spectacular ocean views, where you can watch waves crash against the rocky coastline, outrigger canoe paddlers glide by, and mesmerizing sunsets paint the sky from your expansive 280-square-foot lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
This charming private garden condo is located on the second floor of the south side of the building, offering a peaceful retreat just a short walk to Keawakapu Beach, one of South Maui’s most beautiful spots. About 60 steps along a scenic pathway lead you to the soft golden sands, crystal-clear waters, and lush greenery that make Keawakapu Beach a favorite for both locals and visitors alike.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to Napili Shores Condo D130, a perfect Maui retreat offering breathtaking ocean views and easy access to pristine beaches and vibrant marine life. This comfortable condo comfortably sleeps four and features a fully equipped kitchen, making it ideal for families or couples seeking a relaxing vacation.
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this legally permitted rooftop penthouse located at 2947 Kalakaua Ave #1401, offering breathtaking views of the Pacific Ocean and Diamond Head crater. The suite features a light, airy ocean-inspired decor and floor-to-ceiling windows on two sides, providing panoramic vistas of the ocean, Diamond Head, and the lush Kapiolani Park gardens.Registration Number: TA-199-305-2160-01, 310320290061, GE-199-305-2160-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the beauty of Kauai at Sunset Kahili, a 5th-floor vacation condo situated in the vibrant Poipu Beach area. Designed to maximize breathtaking ocean sunsets, this property offers guests a luxurious and comfortable retreat. The condo is conveniently close to popular beaches like Brennecke's and Poipu Beach, renowned for their pristine sands and crystal-clear waters, perfect for swimming, snorkeling, and surfing.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the perfect Hawaiian escape at this charming 1-bedroom, 1-bath Kona Coast condo, ideally situated near Kailua Bay and Ali'i Drive's Ocean Front Mile. The condo offers a comfortable and modern interior with a fully equipped kitchen, smart TV, and free WiFi, making it an ideal retreat for solo travelers or couples.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Experience breathtaking 270-degree views from this spacious 2-bedroom, 2-bath penthouse in Waikiki. The open living area and fully-equipped kitchen provide a perfect setting for family and friends. The primary ocean-side bedroom features a king bed, while the Diamond Head side has two full beds. Registration Number: GE-182-936-1664-01, TA-182-936-1664-01, 1260140320126
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Welcome to this beautifully renovated 2-bedroom, 2-bathroom condo nestled in a gated community in Waikoloa Village, situated on the 10th fairway of the renowned Robert Trent Jones, Jr. golf course. This inviting ground-floor unit offers a perfect Hawaiian getaway with abundant natural light and coastal decor that creates a warm, relaxing atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
5-bath townhome featuring a den, a mix of comfortable beds including a king, two queens, and a single. The interior showcases neutral tones complemented by rich wood accents, tropical carvings, and artwork depicting local landmarks, creating a true island ambiance.
4 bedroom
3 bathroom
Sleeps 7
Hawaii> Princeville
Escape to the beautiful island of Kauai with this upgraded condo at Kauai Beach Villas D10. Located on the second floor, this airy and light-filled space offers breathtaking views of the neighboring freshwater lagoon through a wall of windows in the open great room.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Lihue
Aloha! This charming ground-floor condo offers stunning oceanfront views of Wailua Bay on Kauai's Garden Isle. Step onto the lanai and gaze across lush lawns dotted with palm trees and tropical plants to a natural sandy beach perfect for swimming and relaxing.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapa?a
Welcome to Seabreeze, a premier Hawaii luxury home part of Paradise in Hawaii Luxury Homes. Nestled in the exclusive Mauna Lani Villages, this stunning 4-bedroom residence offers the perfect blend of comfort, elegance, and island charm. The home features a private pool and spa, high-end finishes, and spacious living areas designed for gatherings of up to 10 guests.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kamuela
This beautifully upgraded condo offers a peaceful retreat in Poipu, nestled below ground level in the quietest part of the complex. It features a lush garden view from the lanai, perfect for enjoying sunrises, starry nights, and the gentle sea breezes.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to Menehune Shores #623, an elegant and fully remodeled penthouse that offers breathtaking views of the ocean and mountains. " Inside, you'll find high ceilings and a thoughtfully designed open-concept living space that creates an airy, welcoming atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei, Maui
Perched on an ocean bluff near the renowned Hanalei Bay, Pali Ke Kua Unit #241 offers a cozy and inviting retreat with breathtaking ocean vistas. This second-floor one-bedroom plus loft, two-bath condo features large windows and a sliding glass door leading to a covered lanai, where you can enjoy panoramic views of the crashing surf, iconic Bali Hai, and vibrant sunsets that paint the sky.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the ultimate Maui getaway in Konea 444, one of Honua Kai's premier one-bedroom suites. Spanning 780 sq. , this spacious condo offers breathtaking views of the Pacific Ocean and the neighboring island of Lanai. The suite features a large, upgraded interior with glass doors that fold away seamlessly to blend indoor comfort with the refreshing Hawaiian breeze.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Enjoy your Maui getaway in this spacious one-bedroom condo (C309) at the popular Kihei Akahi complex, situated on the third floor of building C with stunning ocean views. This well-appointed unit features a new king-size bed and fresh bedroom furniture, providing comfort and style.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience a luxurious stay at Halii Kai, a premier oceanfront resort on the Big Island's Kohala Coast. This professionally managed 3-bedroom condo offers stunning ocean views and top-notch amenities.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
) to this charming 1940s plantation-era home nestled in Hilo, the vibrant cultural heart of Hawai�i. Relax and soak in the aloha spirit while exploring the lush windward side of the island, including the breathtaking Hamakua Coast and the world-famous Volcano National Park.
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Hilo
Welcome to Island Song Villa, part of Paradise in Hawaii Luxury Homes, nestled in the exclusive Islands at Mauna Lani. Spanning 2,189 sq ft, this spacious two-bedroom home comfortably sleeps five guests, making it ideal for families or small groups seeking a luxurious retreat on the Big Island.
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Kamuela
This spacious ground-floor studio at Grand Champions offers the perfect Maui getaway. With approximately 500 square feet, it features a large bedroom and sitting area, a comfortable brand new king-size bed, and a full-size futon for extra guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Located on the first floor, Kona Reef #B7 provides breathtaking year-round views of golden sunsets over the ocean and easy access to Honl's beach. Wake up to the soothing sounds of crashing waves and step out onto your private lanai to enjoy the spectacular seaside scenery.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Escape to our secluded Bamboo Treehouse on Hawaii's Big Island, perched 14 feet above the ground amidst lush jungle. Designed for couples, this cozy retreat features a luxurious queen bed with premium mattresses and soft bamboo sheets, ensuring a restful night's sleep.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
Welcome to our charming condo at Keauhou Palena, perfectly situated right on the Kona Country Club golf course and just a quarter mile from Keauhou Bay. This inviting unit offers a seamless blend of comfort and convenience, making it an ideal retreat for your Hawaiian getaway.
1 bedroom
2 bathroom
Sleeps 3
Hawaii> Kailua Kona
Experience upscale resort living at Hali'i Kai 19H, a beautifully renovated 3-bedroom ground-floor condo situated in a highly sought-after oceanfront community. Fully upgraded in 2020, the condo features new wood flooring, modern bedding, blackout drapes in the bedrooms, and a new BBQ grill, providing a comfortable and stylish retreat.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience unparalleled oceanfront living at Hanalei Colony Resort I2, a 2024 Traveler Award-winning rental nestled on Kauai's stunning north shore. This charming condo offers direct access to a secluded sandy beach, just steps from your door, with breathtaking sea views available from every room.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hanalei
Escape to the stunning island of Maui and enjoy a relaxing stay at Wailea Ekahi 37C, a spacious ground-floor condo nestled in the exclusive resort town of Wailea on South Maui's sunny coast.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Kulakane Resort, your perfect West Maui getaway just a few miles north of historic Lahaina Town. This beachfront resort features beautifully maintained one- and two-bedroom condos, each uniquely decorated by their owners to offer a warm, personalized touch.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Our recently renovated ocean view condo offers a serene tropical escape with modern comforts. It features a comfortable queen-size bed and a fully stocked full kitchen, including a stove with oven, microwave, and full-size refrigerator, making it ideal for a home-away-from-home experience.
1 bedroom
0 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Discover the perfect Maui escape with Konea 515, a luxurious oceanview studio offering breathtaking Pacific Ocean vistas and a tranquil setting just steps from the beach.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
