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 6550, 50
A charming and comfortable country cottage located on over an acre of beautifully landscaped, private yard on Kauai. This inviting retreat can host up to 6 guests, making it ideal for family or friends. The main house features a cozy bedroom and a spacious loft that includes the kitchen and living area, providing an open and airy feel.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimea
Experience the perfect Maui getaway in this beautifully designed condo at Maui Vista 2-212. Located on the second floor, this end-unit offers extra privacy and stunning tropical views from your private lanai. Just a block from the popular surf spot, The Cove, and minutes from renowned beaches like Kamaole Beach I, II, and III, as well as Kalama Park, this location is ideal for beach lovers and adventure seekers alike.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Kamaka Cottage, a charming studio nestled in the lush rainforest town of Volcano on the Big Island. Surrounded by three acres of pristine ohia forest, this cozy retreat offers a perfect base for exploring Hawai'i Volcanoes National Park and the scenic Kalapana Coast.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
Welcome to Kailana, a charming and well-maintained one-bedroom condo offering breathtaking views of the Pacific Ocean, Maui, and Kohala Mountain. Located right on the Village Golf Course at Waikoloa, this private retreat is ideal for couples seeking a peaceful escape.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waikoloa Village
Welcome to our stunning 3-bedroom vacation home in Captain Cook, located just a 2-minute walk from the tranquil Kealakekua Bay. This Hawaiian-style house offers the perfect blend of comfort, beauty, and convenience for your island escape.
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Captain Cook
Experience the tranquility of this spacious two-story home nestled on 2 acres of lush land surrounded by towering palm trees, a vibrant flower farm, and a fruitful orchard. The property offers breathtaking sunset views over the Kahalawai Mountains, making it a perfect retreat for those seeking both comfort and natural beauty.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Haiku-Pauwela
Experience tranquility at Napili Shores Resort in this newly renovated 1-bedroom, 1-bath condo (F249) located on the second floor. The condo features a comfortable living area with a queen sofa bed and a twin sofa bed, both with Posturepedic mattresses, providing ample space for relaxation.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
This fifth-floor condo at Kihei Surfside Resort offers a prime oceanfront location and breathtaking views from the kitchen, living room, dining area, and lanai. Recently remodeled with new porcelain flooring in 2021, the unit is in excellent condition and highly popular among guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover the perfect island escape at Poipu Sands Condo #524, nestled in the heart of Poipu Kai. This contemporary two-bedroom, two-bath condo offers breathtaking unobstructed views of Shipwreck Beach from its spacious private lanai, ideal for relaxing outdoors or dining al fresco.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Discover this charming golf-front property in Wailea, offering peace and serenity with a prime location on the Blue Golf Course. The spacious lanai invites you to unwind while soaking in breathtaking sunsets over the Pacific. Feel the gentle trade winds as they sweep across the greens, carrying the fragrant aroma of Plumeria trees and lush Hawaiian foliage.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this spacious and welcoming mountain-top home offering breathtaking panoramic views of the Pacific Ocean, Diamond Head, Waikiki, and downtown Honolulu. The home features a vintage Hawaiian charm with three bedrooms, two bathrooms—one with a tub/shower and one with a shower—and a large living and dining area that comfortably seats eight. Registration Number: GE-122-615-2448-01, TA-122-615-2448-01, 330310320000
4 bedroom
3 bathroom
Sleeps 7
Hawaii> Honolulu
Experience the ultimate Hawaiian getaway at Poipu Kapili, a beautifully remodeled oceanfront condo nestled on five lush acres of tropical landscaping. This owner-managed unit offers a seamless blend of modern upgrades and authentic island charm, making it an ideal retreat for couples or small families seeking comfort and adventure.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
This beautifully remodeled fourth-floor condo offers an upscale oceanfront experience on the sought-after side of Kihei. With 504 sq ft of living space plus a lanai, it boasts breathtaking views of the Pacific Ocean from the living room, dining area, kitchen, and lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Pili Mai is the newest development on the south shore, situated adjacent to the scenic Kiahuna Champion Golf Course. 5-bath condo provides a perfect retreat with ample space to relax and enjoy your vacation. The interior features white washed wood tones combined with a relaxed coastal vibe and vibrant accents that bring tropical excitement to your stay.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
Discover this charming Hawaiiana-style house nestled in the heart of Hanalei on Kauai's North Shore, offering a perfect blend of comfort and natural beauty. The fully furnished home features authentic Hawaiiana decor, creating a warm and inviting atmosphere for your island escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hanalei
Discover the perfect Kauai retreat at Unit 1 in Poipu Kapili, a highly desirable corner unit offering unparalleled oceanfront vistas. This boutique luxury complex is nestled in the vibrant heart of Poipu, just minutes from renowned beaches perfect for snorkeling, swimming, and surfing.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to your perfect home away from home in Waikiki! Recently renovated in March 2024, this stylish 675 sq. ft. condo offers a fresh, modern feel with all new furnishings and appliances. The spacious living area features a comfy sofa that converts into a full-sized bed, a large flat-screen TV, and tasteful decor that creates a relaxing atmosphere. Registration Number: GE-135-456-0512-02, 260140260050, TA-135-456-0512-01
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Discover this beautifully appointed condo offering stunning ocean views, a tennis court, and a lush garden setting. The master bedroom features a comfortable king bed, while the loft has a cozy queen hide-a-bed, perfect for family stays. The living room is furnished with stylish rattan furniture and equipped with an air conditioner for your comfort.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Kuhio Shores 208 offers a perfect Hawaiian getaway with breathtaking ocean views, modern living spaces, and convenient beach access. Relax on the spacious lanai while watching the Pacific's waves, or enjoy the panoramic views from the floor-to-ceiling windows in the living room.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Located in the beautiful Keauhou Gardens I complex within the Kona Coast Resort, Bob and Meri's Condominium offers a spacious and luxurious escape on the Big Island of Hawaii. This two-level townhome spans 2000 sq ft and features three bedrooms and three bathrooms, making it ideal for families or groups seeking comfort and convenience.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Welcome to Kuhio Shores, our 'Jewel on the Sea,' a stunning oceanfront condo on Kauai's renowned Gold Coast. From the moment you step inside unit #305, the breathtaking views of the ocean, lush green hills, and swaying palms captivate your senses.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover this stylish and spacious 1,930 sq ft island-chic home nestled along Waikoloa's Beach Course and overlooking the Pacific Ocean. Designed with comfort and convenience in mind, this residence features a master bedroom and bath suite, a well-equipped open gourmet kitchen perfect for preparing shared meals, and a ground-floor lanai ideal for relaxing outdoors.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> WAIKOLOA
Brimming with tropical ambiance and contemporary charm, Hale Kahili Ohana offers a luxurious 3-bedroom, 3-bath villa in the sought-after Poipu Beach Estates. This beautifully furnished home combines comfort, style, and stunning outdoor spaces, making it an ideal retreat for families or groups seeking a memorable Hawaiian getaway.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Welcome to Hale Imaka, your ideal retreat in Poipu on the beautiful island of Kauai. This spacious 4-bedroom, 3-bathroom home offers a perfect blend of comfort, privacy, and modern convenience, making it an excellent choice for families and groups seeking an unforgettable getaway.
4 bedroom
3 bathroom
Sleeps 11
Hawaii> Koloa
Located right on one of Maui's most beautiful beaches, Mana Kai 601 offers unparalleled ocean views from both bedrooms, providing the perfect setting for a memorable vacation.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Welcome to this beautiful two-level residential home nestled in South Kihei, Maui. Designed for comfort and convenience, this spacious property features three large bedrooms, each with its own private bathroom, and two smaller bedrooms sharing a fourth bathroom.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kihei
Experience the perfect Hawaiian retreat at our charming Kona condo, located just a mile south of Kailua-Kona on Alii Drive. Situated on the second floor of the highly desirable Building 1, unit 1204 offers stunning ocean views from both sides, providing an ideal setting for a memorable vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Surrounded by lush greenery with stunning ocean and mountain vistas, this tranquil retreat offers a cozy, private escape with two separate spaces—a comfortable studio and a screened porch—where you can relax and enjoy the mellow sounds of nature.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Haiku-Pauwela
5-bath townhouse located within the popular Waikoloa Beach Resort on the sunny Kohala Coast. Spanning 1699 sq ft, this beautiful villa is just steps from the resort pool, fitness center, and tennis courts, offering convenience and recreation at your doorstep.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Maui Kamaole offers a luxurious, low-rise resort experience on 23 lush acres of tropical landscape, perched on a hillside overlooking the ocean along South Maui's coast. The complex features two stunning pools and spas—one adjacent to the office and check-in area, and a natural-design upper pool with a waterfall and a sand-bottom hot tub.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Step into this charming one-bedroom garden-view condo located at Kiahuna Plantation in sunny Poipu Beach, Kauai. Unit 428 in building 41 offers a peaceful retreat with modern updates and lush surroundings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Immerse yourself in the tranquil luxury of Fairway Villas B21, a beautifully furnished second-floor condo nestled within the renowned Waikoloa Beach Resort on Hawaii's Kohala Coast. This elegant 2-bedroom, 2-bath unit offers front-row views of the Robert Trent Jones-designed Beach Course's 1st Hole greens and fairway, making it a perfect retreat for golf lovers and those seeking peaceful surroundings.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Discover this stunning sixth-floor ocean-view suite at Honua Kai Resort & Spa, offering breathtaking panoramic views of Molokai and the Pacific Ocean. This elegant two-bedroom, two-bathroom suite features sophisticated furnishings and high-end finishes, creating a luxurious retreat perfect for families or couples seeking relaxation and comfort in Maui.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This charming ground-floor condo is located in a quiet 15-unit building less than 80 feet from the ocean, offering easy access to sandy beaches and breathtaking coastal scenery. The spacious lanai invites you to unwind while watching dolphins and whales swim by, with spectacular sunsets and the rare Green Flash visible through the palms.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover Hale Makani Kai, a charming upstairs rural home on Oahu's lush, green side where mountains meet the Pacific. Ideal for guests seeking an authentic Hawaiian experience, this 30-day rental offers a unique blend of comfort and natural beauty. Registration Number: GE-018-249-9328-01, TA-018-249-9328-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hauula
Welcome to Wailea Ekolu 310, a stunning single-level luxury condo offering breathtaking panoramic ocean views from the main living area and oversized lanai. Located in the heart of Wailea, this spacious retreat provides unobstructed 180-degree vistas of Molokini Crater and the outer islands, thanks to its coveted second-story corner position.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Discover Konea #329, a stunning third-floor condo at Honua Kai Resort offering spectacular ocean views from every bedroom, living room, and kitchen. Located centrally in the resort’s courtyard, this unit faces the beach, providing unobstructed vistas over landscaped grounds, pools, and the azure waters of Kaanapali Beach across Auau and Kalohi Channels to Lanai Island.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Experience breathtaking 180-degree ocean views from this direct oceanfront studio condo at Valley Isle Resort, situated right on the beautiful West Maui beach. The condo offers a spacious private lanai perfect for morning coffee or sunset cocktails, with stunning vistas of Lanai and Molokai islands, rainbows, windsurfers, and seasonal whales.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to Lae Nani, a tranquil oceanfront haven on Kauai's east coast, perfect for your island escape. This second-floor condo offers breathtaking sea and mountain views, surrounded by lush coconut trees and vibrant tropical flowers. The open-concept layout features two bedrooms with ensuite bathrooms, making it ideal for families or couples seeking a comfortable and stylish retreat.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Welcome to Kealia 302, a beautifully remodeled beachfront condo located right on the renowned Sugar Beach in Maui. This stunning property offers breathtaking ocean and beach views from every room, making it an ideal getaway for relaxation and adventure.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover the perfect getaway at Hale Carly & Lia Lani, a charming and modern vacation home nestled in the heart of Princeville, Hawaii. This beautifully updated residence offers breathtaking mountain and ocean vistas, making it an ideal retreat for families, friends, or solo travelers seeking tranquility and adventure.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Princeville
Experience breathtaking ocean views and a relaxing tropical atmosphere at our beautifully updated 2-bedroom condo in Kona Makai. This well-appointed unit offers privacy with bedrooms on opposite sides, each with wall air conditioning and large windows that let in the soothing sounds of the ocean.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Kahana Sunset C3 welcomes you with warm ALOHA on Maui's pristine West Coast. This unique beachfront condo offers spectacular views of Molokai and Lanai from protected Keonenui Bay, renowned for its romantic white sandy beaches. The resort features 72 low-rise units nestled on quiet Keoni Nui Bay, where often, only your footprints mark the sand.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Located on the scenic Makai Golf Course in Princeville, this spacious home is ideal for large groups seeking comfort and convenience. The property boasts a private pool and spa, two expansive decks perfect for outdoor dining and relaxation, and an open-concept modern kitchen designed for entertaining.
6 bedroom
5 bathroom
Sleeps 14
Hawaii> Princeville
The Address to Wailea Ekolu #314: 10 Wailea Ekolu Pl. #314 Kihei HI 96753
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kihei
Imagine waking up to breathtaking ocean vistas from your private lanai in this beautifully renovated (2023) duplex townhouse located in the Keauhou-Kona Surf and Racquet Club. This two-story home offers unobstructed views of the Pacific from multiple rooms including the kitchen, dining/living area, master bedroom, and upstairs bath, making it arguably the best location in the complex.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua-Kona
Experience the ultimate Hawaiian vacation in this charming and comfortable condo located in the family-friendly Waikiki Banyan. Situated just one block from world-famous waves, this west-facing unit on the 19th floor offers breathtaking ocean and city views, visible from your private lanai. Registration Number: 260250050176, 2211-CCH-0049, GE-175-050-0864-01, TA-175-050-0864-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to Hokulani 820, a luxurious two-bedroom penthouse suite nestled within the prestigious Honua Kai resort on Maui's beautiful Kaanapali shoreline. This corner suite offers breathtaking views of both the azure Pacific Ocean and the majestic West Maui Mountains, providing a truly spectacular setting for your island getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Enjoy a modern, spacious studio located on the 38th floor of the Hawaiian Monarch Hotel in Waikiki. This newly renovated unit features a comfortable queen bed, a full kitchen, and a full bath, offering breathtaking views of the ocean, canal, Diamond Head, and surrounding mountains. Registration Number: GE-177-814-3232-01, TA-177-814-3232-01
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience the best of Maui in this fully remodeled 2021 studio at Maui Banyan, located in sunny Kihei. The property features a comfortable king bed, a modern bathroom with walk-in shower, and a well-equipped kitchenette with new cabinets and counters.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
