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 3250, 50
Discover this beautifully crafted new home located just 200 feet from the ocean in the heart of Old Hawaii's Captain Cook area. The residence boasts expansive ocean views visible from every room, complemented by covered lanais and large open-air decks that invite relaxation and outdoor gatherings.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Captain Cook
Escape to this beautiful Kapalua condo offering breathtaking ocean vistas and a tranquil setting. Perfect for romantic escapes or peaceful retreats, this private residence provides a serene atmosphere to unwind and enjoy all that Maui has to offer.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
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 our charming farm cottage situated atop a lush 5-acre organic tropical fruit farm in Hawaii. This cozy home offers stunning views of the ocean, vibrant fruit trees, and colorful tropical flowers. Wake up to the sounds of chirping birds and watch breathtaking sunsets from the comfort of your living room.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> HANA
Escape to the rejuvenating rainforest experience in the Ginger Room, nestled on 5 lush acres in Volcano Village near Hawaii Volcanoes National Park. This spacious room features a full walk-in closet, thick curtains, and beautiful hardwood floors, creating a cozy and elegant atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Escape to this beautiful Hanalei Plantation Road home, nestled in a highly sought-after neighborhood. The property offers sweeping mountain and ocean views over Hanalei Bay, creating a perfect backdrop for your vacation. With 4 spacious bedrooms, a fully equipped gym, and a private pool, this retreat combines luxury and comfort.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Princeville
Pahinahina Place offers an exclusive private retreat nestled within the prestigious Hualalai Four Seasons Resort, boasting breathtaking Pacific Ocean views and overlooking the 5th green of the golf course.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua-Kona
Welcome to Napili Point, a beautifully remodeled oceanfront condo nestled directly on the stunning Honokeana Bay in Maui. This contemporary, air-conditioned space offers a perfect blend of style and comfort, ideal for couples, small families, or travelers seeking a peaceful retreat with easy island access.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the perfect Maui escape at Wailea Ekahi 14F, an upper-floor 2-bedroom, 2-bath condo nestled in a tranquil tropical garden setting within Wailea Ekahi Village. This peaceful retreat offers a refuge from road and pool noise, with convenient access to one of the three quiet pools and a nearby pickleball court.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea-Makena
Welcome to this charming plantation style cottage nestled on Baldwin Avenue in the heart of historic Paia Town Maui. Perfect for a romantic getaway or a peaceful retreat for two, this cozy one-bedroom home combines island charm with modern comforts.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
Welcome to your perfect Hawaiian getaway at The Fairways at Mauna Lani, a private gated community offering quiet luxury and close proximity to world-class beaches, golf, shopping, and spas.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kamuela
Welcome to your luxurious escape in the heart of downtown Honolulu! This spacious, modern two-story penthouse features soaring ceilings and a sleek design, perfect for families, groups, or anyone seeking the best city experience in comfort and style. Registration Number: TA-081-847-7568-01, GE-081-847-7568-01, 210120040498
3 bedroom
2 bathroom
Sleeps 12
Hawaii> Honolulu
This extraordinary property offers a luxurious oceanfront experience in the renowned Hawaii Kai neighborhood, situated on a rare double-sized ocean lot in Portlock. Designed for large groups seeking privacy and comfort, the estate features multiple structures nestled around a lush tropical courtyard and a sprawling 1,000-square-foot swimming pool. Registration Number: GE-060-531-3536-01, TA-060-531-3536-01, 390160020000
6 bedroom
7 bathroom
Sleeps 12
Hawaii> Honolulu
Welcome to Kaimana at Pohailani Maui! This cozy condo offers breathtaking ocean views, spectacular sunsets, and opportunities for wildlife watching from your private lanai. Located on West Maui, the property is so close to the ocean it feels like you're on a boat, with waves just a stone's throw away.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience the ultimate Hawaiian getaway at "Paradise at Ehupua," a stunning holiday home nestled in the Kahala district of Honolulu. This spacious property offers three bedrooms, each equipped with air conditioning and ceiling fans, ensuring comfort during your stay. Registration Number: 1350530730000, TA-154-674-7904-01, GE-154-674-7904-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to this beautifully renovated ground-floor studio at Wailea Ekahi Resort, offering stunning ocean views and a tranquil setting. The condo features a comfortable king-sized bed, a full modern kitchen, and in-unit laundry for convenience. Step onto your private lanai, shaded and peaceful, perfect for enjoying morning coffee or a barbecue while overlooking lush gardens and the expansive green spaces of the resort.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the ultimate island retreat at Lokomaikaʻi Kailua, a beautifully renovated home nestled on Oahu's east side. This spacious rental offers a seamless indoor-outdoor living experience with sliding glass walls that open to tropical gardens and a sparkling pool. Registration Number: GE-079-127-1936-01, TA-079-127-1936-01, 430830190000
5 bedroom
3 bathroom
Sleeps 11
Hawaii> Kailua
Escape to Lahaina and enjoy this charming condo designed for your comfort. Featuring a cozy bedroom with a king bed, a modern bathroom with a refreshing shower, and a welcoming living area with a sofa bed, this property provides a home-like atmosphere for your Hawaiian vacation.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
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
Experience the perfect Hawaiian retreat in this beautifully renovated 3rd-floor oceanfront condo at Kona Bali Kai Resort in Kailua-Kona. Featuring two spacious bedrooms each with luxurious king beds, this unit offers a comfortable and stylish space to unwind after exploring the island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kihei
Escape to the stunning shores of West Maui with this exceptional waterfront condo at Mahana, nestled along the pristine Kaanapali Beach. Perched on the 8th floor, Unit #811 offers a rare corner layout featuring expansive wrap-around windows that provide spectacular 270-degree views of the sandy beaches, swaying palm trees, and neighboring islands of Lanai, Molokai, and a distant glimpse of Oahu.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this gorgeous ocean view condo in Kauai, offering a bright, airy atmosphere with two private lanais perfect for whale and dolphin watching. The spacious, well-maintained space features modern amenities including a 60" HDTV, free internet, and a fully stocked kitchen with stunning ocean vistas.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Princeville
Discover paradise at Unit #611 in the Royal Mauian, a premier oceanfront resort in Kihei. This top-floor condo offers breathtaking panoramic views of the ocean, beaches, mountains, and neighboring islands, creating a perfect backdrop for your Maui getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this luxurious three-bedroom, three-bathroom villa, your ideal base for exploring Hawaii. The villa offers a spacious living area, a fully equipped kitchen, and modern amenities to ensure a comfortable stay. Enjoy high-speed internet, cable TV with a DVD player, and the convenience of a washer and dryer during your vacation. Registration Number: 910570190000, GE-123-311-8720-01, TA-123-311-8720-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kapolei
Escape to Ohia Retreat, a tranquil haven offering two beautifully appointed rooms, each with its own private lanai to enjoy the soothing sounds of nature. The retreat boasts 14-inch memory foam mattresses and flat-screen TVs for restful nights and entertainment.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Volcano
Discover the exquisite Garuda Estate, a spacious 3,300-square-foot villa nestled at the foot of West Maui's Mountains in Launiupoko. Located just off the bypass, this stunning retreat offers quick access to Ka'anapali's vibrant scene, including golf courses, restaurants, and shops at Whalers Village, a 15-minute drive away.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Launiupoko
Escape to the serene resort lifestyle in this beautifully upgraded townhouse located within the prestigious Kulalani at Mauna Lani community. This stylish residence offers a perfect blend of comfort and convenience, making it an ideal choice for families or small groups seeking a memorable Big Island vacation.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
The Honu Suite is a serene, design-forward retreat in the heart of Waikiki - just one block from the beach.Registration Number: GE-214-725-2736-01, TA-214-725-2736-01, 260250050369
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Perched on the rim of Poipu Crater, Hale Anuenue offers breathtaking views of Poipu Beach and Mount Haupu, making it a perfect spot to catch rainbows during liquid sunshine. The home features panoramic ocean and mountain vistas, with a stunning sunrise over the mountains and vibrant sunsets into the Pacific.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Experience the perfect Maui getaway in this beautifully remodeled condo located in the South Kihei Wailea area. The unit features all-new modern furnishings, appliances, and decor, ensuring a comfortable and stylish stay. Situated at Kihei Akahi, directly across from the popular Kamaole Beach 2, you'll be just steps from the sandy shores with a lifeguard on duty and breathtaking sunsets every evening.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Ko Olina Beach Villa B109 by Gather, a stunning two-bedroom, two-bathroom oceanfront retreat nestled in the prestigious Ko Olina Resort on Hawaii�s west coast. Perfect for up to four guests, this ground-level villa offers an exceptional blend of luxury, comfort, and natural beauty, making it an ideal destination for a relaxing Hawaiian vacation.Registration Number: 910570090168, GE-205-237-2992-01, TA-205-237-2992-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to your dream vacation at Waipouli Beach Resort, Kauai's premier destination for luxury and relaxation. This beautifully decorated, extra-large condo unit D310 offers breathtaking views of the Pacific Ocean and the resort's legendary fantasy pool.
2 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapaa
Welcome to Ikena Lani, a luxurious modern custom home offering spectacular ocean views and a tranquil island escape. Designed by award-winning firms, this residence features elegant materials sourced from Asia, creating a blend of sophistication and serenity.
5 bedroom
5 bathroom
Sleeps 12
Hawaii> Kilauea
Experience a slice of paradise at Castle Kaha Lani Resort #206, a charming second-floor condo on Kauai's Coconut Coast. 5-bathroom unit combines modern tropical decor with comfort and stunning views.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
Experience the pinnacle of Maui luxury at Honua Kai Hokulani 713, a top-floor penthouse suite offering breathtaking Pacific Ocean and Molokai views. This 1-bedroom residence is part of the exclusive inner courtyard column of the resort, providing unparalleled vistas and privacy.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui escape in Hokulani 738, a beautifully appointed 7th-floor one-bedroom suite offering spectacular views of the tranquil Pacific Ocean and lush West Maui Mountains. Spanning approximately 700 sq. , this spacious condo provides a perfect blend of comfort and luxury, with recent updates including new flooring, a plush bed in the primary bedroom, a stylish sofa, and modern appliances.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Ke Aloha Villa, your private slice of paradise nestled in the lush landscapes of Princeville, Kauai. This beautifully crafted, custom-built home opened in 2024, offering nearly 3,000 square feet of elegant living space designed to provide comfort, luxury, and unforgettable memories.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Princeville
This beautifully appointed two-bedroom, two-bathroom condo in Kapalua offers a perfect blend of relaxation and adventure for your Maui getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Book your stay in this newly remodeled Maui condo and immerse yourself in paradise! This ground-floor 1-bedroom unit offers stunning ocean and pool views, a fully upgraded kitchen with stainless steel appliances, and a spacious bathroom with a luxurious 2-person shower.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this charming 2-bedroom apartment nestled in the heart of Waikiki, Honolulu. Located just one city block from the stunning beach, this property offers the perfect blend of comfort and convenience for your Hawaiian getaway.Registration Number: , GE-126-009-0090-01, TA-126-009-0090-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Resort life awaits you in the Shores at Waikoloa in this beautiful condo overlooking the golf course—only half of a mile from the beach. Cooling cross breezes and central A/C provide year-round comfort. The spacious layout ensures privacy for each side of the condo.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This stunning 2200 square foot Hawaii estate was built in 2002, with an additional bedroom and bathroom added in 2012. Designed in classic old Hawaii estate style, it offers a spacious layout with 1100 square feet of covered lanai featuring screens and full-length sliders that open to embrace the tropical trade winds, creating a naturally cool and breezy ambiance without the need for air conditioning.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kamuela
Discover the ultimate beachside oasis in this exquisite end-unit condo at Puu Poa, featuring breathtaking ocean views from every angle. Step onto two expansive decks, perfect for sunbathing, grilling, or relaxing while taking in the spectacular scenery.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the best value in five-star accommodations at the Beach Villas at Ko Olina. This spacious 2-bedroom, 1200 sq ft upscale villa offers stunning mountain and distant ocean views. Inside, the large open kitchen features top-tier appliances from Wolf, Bosch, and Subzero, designed by Roy Yamaguichi of Roy's restaurants. Registration Number: TA-101-831-0656-01, GE-101-831-0656-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Experience the luxury of a tropical island vibe in this fully furnished Hawaii condo, ideally located within walking distance to shops, restaurants, beaches, and entertainment options. The highly desirable location offers privacy combined with convenience, putting you just minutes from Waikoloa Beach, Hapuna Beach, and the Hilton hotels with dolphin experiences.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Royal Kahana end unit condo 920, your ideal Maui retreat offering comfort and privacy. This spacious 2-bedroom, 2-bathroom condo spans 1,140 sq ft and features central air conditioning, ensuring a cool and relaxing stay. Located on the 9th floor, it boasts breathtaking ocean views from both bedrooms and the living room, perfect for whale watching and sunset gazing.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Lahaina
Welcome to Ainamalu 11, also known as "My Favorite Island in the Sun," a beautifully appointed single-story home managed by Kona Coast Vacations. This expansive 3-bedroom residence is perfect for families, friends, or athletes looking to enjoy a luxurious and convenient Hawaii getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Escape to this charming Keauhou ocean view suite, nestled along the edge of He'eia Bay in a peaceful community. The suite offers a semi-private space with an interior kitchen and large lanai, perfect for relaxing while listening to the soothing sounds of crashing waves.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Welcome to Hale Kai, a beautiful 2-bedroom condo perched at the ocean's edge overlooking two incredible surf spots. This condo offers a perfect retreat for relaxation, with stunning views of the ocean waves and unforgettable sunsets. Imagine sipping your morning coffee while watching dolphins play and seasonal whales breaching in the distance.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
