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 9700, 50
Welcome to your perfect Hawaiian retreat on the serene island of Molokai! This beautifully renovated oceanfront condo offers a genuine taste of the Aloha spirit with modern comforts and breathtaking views.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaunakakai
Welcome to this beautifully furnished and inviting Maui condo offering a relaxed beachy vibe. The open floor plan is complemented by elegant granite countertops and tasteful backsplash in the well-appointed kitchen, perfect for preparing delicious meals.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Welcome to Once Upon a Tide at KaMilo, part of our Hawaiian Luxury Collection, brought to you by Paradise in Hawaii Luxury Homes. This exquisite 4-bedroom residence offers a perfect blend of comfort, style, and convenience for your Hawaiian getaway.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kamuela
Discover a charming tropical retreat just steps from Kamaole Beach Park III. This 884-square-foot air-conditioned suite features vibrant island artwork and accents that create a welcoming atmosphere. The full kitchen, equipped with stainless steel appliances, allows you to prepare meals and dine on the private lanai, which offers a picturesque backdrop of palm trees.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
Kaha Lani #213 offers a stunning oceanfront experience in a quiet area surrounded by lush greenery and scenic views. This two-story, two-bedroom corner condo is located on the 2nd and 3rd floors, providing an airy, light-filled space decorated with Hawaiian furnishings for a true island feel.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lihue
" to Beachside Cottage, a charming vacation rental offering all the comforts of home. This cozy studio is perfect for couples, featuring a comfortable queen-sized bed and a clean bathroom. The property boasts inviting outdoor living space and a warm, welcoming interior that makes you feel at home from the moment you arrive.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Holualoa
5-bath residence nestled within a gated luxury resort. This stunning property offers breathtaking views of the ocean, beaches, and mountains, providing an idyllic backdrop for your vacation. Located within walking distance to shopping, dining, entertainment, and famous beaches like Kahaluu Beach Park, known for its exceptional snorkeling, the townhome combines convenience with natural beauty.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kona
Immerse yourself in the tranquil beauty of Kauai at Bliss on the Bay in the Wailua Bay View condo complex. This charming second-floor, oceanfront one-bedroom, one-bath condo offers breathtaking views of the Pacific Ocean and swaying coconut trees, creating a perfect Hawaiian getaway.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Begin your Maui adventure with breathtaking views of Haleakala and the stunning Pacific Ocean from Hale Kamaole Unit 236. This inviting one-bedroom, one-bath condo comfortably sleeps four, making it ideal for couples or small families seeking a relaxing island retreat.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Maui retreat in Villa 6C at Luana Garden Villas, a prime 3-bedroom, 3-bath luxury residence nestled within the prestigious Enclave One. This top-floor villa offers stunning west-facing views between Hokulani and Konea Towers, with glimpses of the Pacific Ocean and Lanai, making it one of the most sought-after locations in the resort.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to your tropical escape at Casa De Emdeko #225 in Kona, Hawaii! This inviting 1-bedroom, 1-bath condo provides a perfect mix of comfort and convenience, with easy accessibility without stairs, making it ideal for all guests. Inside, you'll find a well-equipped kitchen, a plush California king-sized bed, and a queen sleeper sofa, ensuring restful nights for everyone.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
5 acres in Hawaiian Paradise Park, just south of Hilo. This private retreat comfortably accommodates up to nine guests, making it ideal for families or friends traveling together.
5 bedroom
4 bathroom
Sleeps 9
Hawaii> Keaau
Welcome to your perfect Maui getaway at Kamaole Sands! This spacious (almost 1300 sq ft) 2-bedroom, 2-bath condo features a versatile family room with a Queen Murphy bed, transforming into a third bedroom for added privacy and convenience. Located on the third floor of this prestigious complex, the condo offers stunning ocean views and easy access to the renowned Kamaole Beach III, just a 3-minute walk away.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to Kepuhi Hale, a beautiful beachfront vacation home nestled on Kauai's scenic north shore. This idyllic property offers breathtaking ocean views from the living room and all three bedrooms, each with a private bath and attached lanai perfect for outdoor dining and relaxing in the warm tropical breeze.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Hanalei
Welcome to Coral Reef Villa at KaMilo, a stunning single-level luxury home on the Big Island, brought to you by Paradise in Hawaii Luxury Homes. This spacious 4-bedroom residence is perfect for families or groups seeking a private, upscale Hawaiian retreat.
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Kamuela
Located along South Maui's renowned Keawakapu Beach, this charming ground-floor condo offers a serene garden view and is just 30 steps from the pristine sands and clear waters. The unit is on the first floor on the south side of the building, providing easy access to common areas and the beach pathway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to "The Big Pono Aloha," your private oasis at Pono Kai Resort in beautiful Kauai. This spacious condo offers stunning ocean views from the second-floor lanai, just 75 feet from the beach. Recently renovated in 2019, it features a full kitchen, dining space, living room, and two large bedrooms with private bathrooms.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
Welcome to your inviting retreat in Kailua-Kona, Hawaii! This charming 1-bedroom, 1-bathroom condo is situated within the vibrant Casa De Emdeko community, offering a perfect mix of relaxation and adventure. The condo features a well-equipped kitchen with all you need to prepare delicious meals, which you can enjoy at the dining table or al fresco on the private patio.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Ma'alaea Kai #214 offers a tranquil oceanfront retreat in the heart of Maui. Nestled in peaceful Ma'alaea, this well-appointed condo invites guests to unwind while providing easy access to Maui's main attractions. The unit comfortably accommodates up to four guests with a queen bed, sofa sleeper, full bathroom, and a fully equipped, upscale remodeled kitchen perfect for preparing island-style dishes or pupus.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Wailuku
Immerse yourself in the beauty of Maui at this bright corner unit condo in Maui Eldorado, nestled in the heart of Kaanapali Resort. Featuring tile flooring and a peekaboo ocean view from a prime location near the main pool, this condo offers a perfect blend of comfort and convenience for your island getaway.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kaanapali
Experience breathtaking 180-degree ocean views from this beachfront condo right on the sand. One wall is all windows, offering a stunning perspective of the ocean, while a mirrored wall reflects the vast horizons, creating a boat-like ambiance. Registration Number: GE-018-613-8624-01, TA-018-613-8624-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to this stunning third-floor condo in Hawaii, offering breathtaking views of the ocean, nearby islands, and the pool area. The spacious living room features sliding doors that add an Asian-inspired touch and create a seamless indoor-outdoor experience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the best of Waikiki at the Waikiki Sunset, a stylish condominium hotel located at 229 Paoakalani Avenue on the 30th floor. Managed by Ali'i Beach Rentals, this property combines hotel-quality amenities with the comfort of a private residence, making it ideal for anniversaries, honeymoons, or family vacations.Registration Number: GE-029-812-5312-01, TA-029-812-5312-01, 260280110319
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Champion View Retreat, a stunning luxury vacation home nestled in the prestigious Champion Ridge on the Kohala Coast of Hawaii. This elegant single-level residence is part of our Hawaiian Luxury Collection and offers unparalleled amenities, breathtaking views, and accommodations for up to 14 guests.
5 bedroom
5 bathroom
Sleeps 14
Hawaii> Kamuela
Aloha from Midway Vacations! Our dedicated local hospitality team ensures a seamless Hawaiian getaway with professionally cleaned, well-maintained properties and 24/7 support. This stunning one-bedroom condo at Waikiki Sunset Resort offers breathtaking mountain views from the 36th floor, perfect for relaxing on your private lanai. Registration Number: 1260280110401, GE-187-870-0032-01, TA-187-870-0032-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience a private oceanfront retreat in this beautifully renovated top-floor condo at Valley Isle Resort. With unobstructed views of the ocean, you can enjoy breathtaking sunsets and tropical breezes from your home-like space. The unit features a king-sized bed for restful nights and a queen pullout sofa accommodating up to four guests, perfect for family or friends.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to our beautiful villa at Waikoloa Beach Villas, nestled in the heart of the renowned Waikoloa Beach Resort on Hawaii's sunny Kohala coast. Owned by Canadians Moreno and Erika from Vancouver, this spacious 2-bedroom, 2-bath unit offers a perfect blend of luxury and comfort with tropical decor and modern amenities.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This luxury condominium is nestled on the 17th fairway of Kona Country Club golf course, offering breathtaking ocean and golf course vistas. Country Club Villas features on-site amenities including three swimming pools, hot tubs, and two tennis courts, providing a perfect blend of relaxation and recreation.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the perfect Maui getaway at Kamaoele Sands, where this ground-floor condo offers breathtaking ocean views and is just steps from the renowned Kamaole III Beach. Located in Building 10, one of the closest units to the shoreline, this property provides easy access to swimming, snorkeling, scuba diving, whale watching during winter, and miles of golden sand beaches ideal for leisurely strolls.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Come experience paradise at Kona Makai, a stunning oceanfront complex in Kailua-Kona. This beautifully decorated tropical-themed condo welcomes you with vibrant murals and comfortable amenities. It comfortably sleeps six, featuring two king-sized beds and a queen sleeper sofa, making it ideal for families or groups.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Aloha, welcome to your perfect Hawaiian getaway! Nestled at the very back of the complex on the top floor, this condo offers tranquil views of tree tops and the sparkling blue ocean, with only the sounds of chirping birds for company. The entire unit has been renovated with new furnishings, steering clear of outdated decor to provide a fresh and comfortable atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience upscale living in this beautifully remodeled oceanfront condo at the Royal Sea Cliff Resort in Kailua-Kona. The spacious open-concept unit features an upgraded kitchen, new bathroom, and modern flooring, providing a luxurious and comfortable retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover a perfect blend of luxury and affordability in this beautiful Maui private bedroom. Located in North Kihei, this fully furnished 14' x 16' room offers a comfortable king bed, an ensuite bathroom with a stand-up shower and a two-person soaker tub, and modern amenities including HDTV, WiFi, A/C, a ceiling fan, microwave, coffee maker, and an in-room fridge/freezer.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Experience the ultimate Maui getaway at Royal Mauian, a prime oceanfront property in South Kihei offering breathtaking views of the Pacific Ocean and neighboring islands. This renovated second-floor condo comfortably sleeps six with two spacious bedrooms and two bathrooms, providing a perfect blend of luxury and comfort.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to a tropical paradise at The M. Maui Ocean Club - Lahaina & Napili Towers, located on the iconic Ka'anapali Beach in Lahaina. This resort offers spacious, tastefully decorated vacation rentals with options for garden, ocean view, and oceanfront suites, all designed to provide comfort and style during your Maui holiday.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the ultimate beachfront getaway in this one-of-a-kind, fully renovated unit that combines luxury and comfort. The gourmet kitchen features high-end appliances, custom cabinets, and marble flooring that extends throughout the space, creating an elegant and cohesive look. Registration Number: GE-145-625-7024-01, 310320100019, TA-145-625-7024-01
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this charming home just a 5-minute drive from Hawaii Volcanoes National Park, nestled on nearly an acre of lush tropical forest at 3700 feet elevation. The house offers a cool, comfortable retreat with high-quality sheets, warm blankets, and a wood-burning stove to keep cozy during chilly nights.
4 bedroom
2 bathroom
Sleeps 7
Hawaii> Volcano
Discover this inviting open-concept island-style vacation rental on Kaua'i, situated on the 15th hole of the Makai Golf Course with breathtaking views of the ocean and lush greens.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to Maalaea Kai 220, your ideal island retreat located on the second floor of a peaceful oceanfront complex in the heart of Central Maui. This beautifully furnished 2-bedroom, 2-bath condo comfortably accommodates up to 4 guests, making it perfect for families, couples, or friends seeking a memorable Hawaiian holiday.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Escape to this charming second-floor condo offering breathtaking ocean vistas and modern comforts. Recently renovated, it features new furnishings, artwork, and a fully updated kitchen with granite countertops. The condo spans approximately 592 square feet and is thoughtfully designed for a relaxing stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the beauty of Maui at Hololani Resort, ideally situated between Ka`anapali and Kapalua. This remodeled two-bedroom, two-bath condo offers breathtaking ocean views, soothing sounds of the waves, and vibrant Hawaiian sunsets from your private lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to your tropical retreat at Kaanapali Shores 144, a charming ground-floor 1-bedroom, 1-bath condo designed for comfort and convenience. This unit comfortably accommodates up to four guests and offers easy access to lush gardens and resort amenities.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your perfect Waikiki getaway in this comfortable studio unit designed for convenience and relaxation. The space includes two full-sized beds, a fully equipped kitchen, and a modern bathroom, making it ideal for solo travelers, couples, or small groups. Registration Number: GE-200-617-9840-01, 1260190010137, TA-200-617-9840-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the best of Kauai at Plantation Hale Suite J8, a beautifully remodeled ground-floor end-unit condo located in the scenic Royal Coconut Coast. This charming one-bedroom, one-bathroom condo offers a comfortable and fully equipped space perfect for families or couples seeking an affordable island getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Experience the charm of this beautifully renovated 3,000 sq ft private home nestled in Princeville on Kauai's stunning North Shore. The main residence offers four spacious bedrooms plus an optional studio unit, accommodating up to 10 guests comfortably.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Princeville
Discover a tranquil retreat at Honokeana Cove, a charming gated community in Napili, West Maui. This beautiful second-floor condo offers nearly 180-degree views of the Cove, perfect for whale watching (seasonal) and spotting green sea turtles right from your lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This inviting studio unit features a plush king-size bed and a comfortable full-size sofa bed, perfect for relaxing after a day exploring Honolulu. Stay connected with free Wi-Fi and enjoy entertainment on the flat-screen TV. The fully stocked kitchen allows guests to prepare meals at their convenience, making it ideal for both short and extended stays.Registration Number: 260100070370, GE-155-133-6448-01, TA-155-133-6448-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
This beautifully appointed custom home offers three large bedrooms and two and a half bathrooms, making it ideal for families or groups seeking comfort and space during their Kauai vacation. The house is equipped with modern amenities including central air conditioning to keep you cool, high-speed WiFi for staying connected, and memory foam beds that promise restful nights after days spent exploring the island.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Aloha from Midway Vacations! Our local hospitality team is here to make your Hawaiian dream come true. When you stay at one of our properties, you can expect a professionally cleaned, well-maintained experience, with 24/7 support for any needs.Registration Number: GE-004-291-5840-01, TA-004-291-5840-01, 260230560088
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the relaxed island vibe in the Princess Palm Suite at Palm Hale, a spacious open-air studio ideally located in Kona. This charming retreat offers a queen bed, cable TV, and a fully equipped kitchenette with a full-size fridge, coffee maker, toaster, and microwave, perfect for light cooking and enjoying your meals.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
