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 10850, 50
Villa 5 at Villas By The Cove offers a spacious 1-bedroom suite with breathtaking 180-degree ocean views from the living room and covered lanai. Recently remodeled, this 700 sq. ft. villa combines Hawaiian charm with modern comforts, making it an ideal retreat for couples or small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience this charming studio apartment situated at Marine Surf #1204, 364 Seaside Ave, offering a comfortable 392 sqft space plus a private lanai with sweeping Waikiki city views. Ideal for vacationers or corporate travelers, the unit features two queen platform beds, a 49" Smart TV, and a fully equipped kitchenette with a 4-burner stove/oven, microwave, apartment-sized refrigerator, and coffee maker. Registration Number: 260190010101, GE-111-534-8992-01, TA-111-534-8992-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Hali'i Kai at Waikoloa Resort, an exceptional destination on the sunny Kohala Coast of the Big Island. This beautifully appointed condo offers a perfect blend of comfort, luxury, and scenic beauty, ideal for families, couples, or groups seeking a memorable Hawaiian getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
Pali Ke Kua #233 is a charming ocean bluff condo offering stunning views of the ocean and nearby golf course. The open beam ceilings and spacious split-level design create a welcoming atmosphere, making it an ideal retreat for relaxation and exploration.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover this beautifully decorated third-floor oceanview condo at Papakea Resort in Maui, perfect for a relaxing getaway. The unit features one bedroom with a comfortable convertible bed, a fully-equipped kitchen, and modern appliances including a Keurig coffee maker and new kitchen gadgets.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Koko Resorts at The Waikiki Banyan! This deluxe high-level condo offers breathtaking views of Diamond Head and the Pacific Ocean, situated just a few blocks from the iconic Waikiki Beach. Recently renovated, the condo features a fully equipped kitchen with modern appliances including a refrigerator, stove, microwave, rice maker, and coffee maker, perfect for preparing delicious home-cooked meals. Registration Number: TA-066-364-9792-01, GE-066-364-9792-01, 260250050355
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Discover the ultimate Waikiki getaway at The Palms at Waikiki, a well-appointed condo offering breathtaking high-floor lanai views. This spacious unit comfortably sleeps four with two king-size beds, making it ideal for families or friends seeking a relaxing vacation.Registration Number: GE-060-772-3520-01, TA-060-772-3520-01, 1260120030243
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Enjoy breathtaking ocean views and the soothing sounds of the surf from this beautifully furnished ground-floor condo located on the famous Kaanapali Beach, known as West Maui's signature beach. The spacious one-bedroom unit features two full bathrooms, a fully equipped kitchen, washer/dryer, king-size bed, and comfortable living and dining areas, perfect for relaxing or entertaining.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Wailea Ekolu 912 offers a charming and stylish retreat in the heart of Wailea, Maui. This second-floor, one-bedroom, two-bathroom condo features stunning ocean vistas and a thoughtfully designed interior. The living space boasts an open floor plan with vaulted wood-paneled ceilings, exposed beams, and large windows that flood the room with natural light.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
This charming vintage Hawaiian home in Kona offers an authentic "sugar plantation" style, preserved since its original build, providing a nostalgic island experience. Located in the private, gated community of Kona Bay Estates, it's just minutes from downtown Kona and Kailua Pier, where fishing, canoe races, and IronMan events happen regularly.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua Kona
Welcome to Hawaiian Bliss Villa at KaMilo, a luxurious 4-bedroom home nestled on the Kohala Coast of Hawaii. This exquisite property is part of our Hawaiian Luxury Collection and offers a perfect retreat for families or groups up to 10 guests seeking comfort, style, and breathtaking sunset vistas.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kohala Coast
Discover the comfort of Kona Pacific E 735, a beautifully appointed top-floor condo offering stunning ocean views and modern amenities. The spacious living area features a queen-sized sleeper sofa and a fully equipped kitchen, perfect for preparing your favorite meals.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> KAILUA KONA
Experience the ultimate luxury stay in Suite 151 at Kaanapali Ali�i, a stunning 2-bedroom condo offering partial views of the Pacific Ocean from multiple rooms, including the balcony, living room, dining area, and primary bedroom. Gaze out at breathtaking vistas of the West Maui Mountains and the expansive Kaanapali Beach, stretching all the way to Black Rock in the distance.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Pono Kai Resort C101 offers a stunning ground-floor oceanfront condo in Kapaa, Kauai. With 1,150 sq ft of comfortable living space, this end-unit features a fully equipped kitchen, a Cal King bed facing the ocean, a queen bed in the second bedroom, and a queen sofa sleeper in the living room.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
Experience the vibrant energy of Waikiki from our conveniently located side-by-side suites, ideal for families, friends, or business travelers. Each suite offers a fully equipped kitchen, perfect for preparing meals during your stay. Relax in comfortable Queen beds with luxurious linens, and enjoy the modern entertainment options like a 32-inch Smart TV and SoundBar. Registration Number: 260190010021, GE-017-009-0496-01, TA-017-009-0496-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Make lasting memories at Keauhou Punahele Unit C104, a charming two-bedroom, two-bathroom condo ideal for your Hawaii vacation. This ground-floor unit features a recently remodeled kitchen with modern appliances, perfect for preparing delicious meals during your stay.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience the perfect Maui getaway in this ideally situated vacation rental. Located just minutes from stunning beaches, vibrant shopping districts, and lively restaurants, you'll find everything you need for a memorable stay.
1 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the ultimate Maui vacation in this secluded oceanfront condo nestled on one of West Maui's finest sandy beaches. The property offers breathtaking ocean views, a recently remodeled serene pool, and a private white sand beach just steps from your door.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to this charming 1-bedroom, 1-bath vacation rental nestled in the scenic town of Koloa, Hawaii. This fully furnished unit offers a comfortable and relaxing environment, ideal for couples or solo travelers seeking a memorable Hawaiian retreat.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Koloa
Discover this beautifully renovated 1-bedroom condo at Waikiki Park Heights, offering stunning ocean views from a private lanai. With 488 sq. , it comfortably accommodates up to 3 adults or 2 adults with 2 small children. The living area features a full-size sleeper sofa with a memory foam mattress, a 40" Smart TV with digital Variety Pack & Showtime, and a dining table for four. Registration Number: GE-063-282-9952-01, TA-063-282-9952-01, 260240240060
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Step into Puu Poa 102, a beautifully updated ground-floor condo perched atop the bluff above Hideaway Beach in Princeville, offering breathtaking panoramic views of the Pacific Ocean. The spacious suite features two bedrooms and two bathrooms, with the primary bedroom boasting a king-sized bed, an en-suite bath, and an oversized walk-in shower.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the beauty of Maui from this charming oceanfront condo in Kihei. The second-floor suite offers a spacious living area, a modern kitchen with breakfast bar, and a large private view deck perfect for relaxing or watching the sunset. Recently updated in 2023 with new flooring throughout, the condo features a comfortable 1 bedroom with an attached bathroom and an additional hall bathroom with shower.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
This beautiful condo is situated on the 14th floor of the Ilikai Hotel, offering breathtaking, unobstructed views of the Pacific Ocean and the Ala Wai Yacht Harbor. The spacious, freshly painted interior features new drapery, split air conditioning, and modern furnishings, making it a perfect retreat for travelers seeking comfort and stunning scenery.Registration Number: TA-136-538-9312-02, GE-136-538-9312-02, 2601000070302
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the perfect Kona getaway at Casa De Emdeko, where breathtaking ocean views and lush gardens create a tranquil setting. The complex features two refreshing pools, including a rare saltwater oceanview pool, ideal for relaxing and soaking up the sun.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Escape to this inviting family-friendly condo at Waikiki Banyan, perched on the 37th floor with breathtaking Diamond Head and ocean views. This spacious, air-conditioned unit comfortably sleeps up to 4 guests, featuring a bedroom with a queen-size and full-size bed, a living area with a pull-out sofa, and a fully equipped kitchen. Registration Number: STR Permit #: 722, TA-180-890-6240-01, GE-180-890-6240-01, 260250050425
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Kauai retreat at Sealodge G4, a beautifully remodeled end-unit condo with spectacular oceanfront vistas. Whether celebrating a honeymoon, anniversary, or just seeking relaxation, this condo offers a perfect blend of comfort and natural beauty.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
2 miles from the stunning Magic Sand Beach where you can surf, sunbathe, or enjoy local treats from beach grills and shave ice stands. Located only 2 miles from the lively center of Kailua Kona, you'll find a variety of restaurants, shops, and attractions to explore.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Aloha from Midway Vacations! Our dedicated team of local hospitality experts is committed to making your Hawaiian escape truly unforgettable. When you choose one of our properties, you can expect a professionally cleaned, meticulously maintained space and 24/7 support for any needs. Registration Number: GE-188-220-2112-01, TA-188-220-2112-01, 2301-CCH-0931, 260250050723
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the perfect getaway in this beautifully renovated condo located on the 19th floor of the Ilikai Hotel in Waikiki. This fully updated unit offers a blend of comfort and style, with two spacious lanais totaling 120 sq ft that provide stunning views of the city, Ala Moana Blvd, Diamond Head, and a peek at the ocean. Registration Number: TA-193-076-8384-01, GE-193-076-8384-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Immerse yourself in the stunning beauty of Kauai's south shore at this beautifully renovated Poipu Sands condominium. This spacious 2-bedroom, 2-bathroom condo spans 1371 sq ft and features expansive lanais on the second and third floors, offering over 354 sq ft of breathtaking ocean views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Discover this charming third-floor condo in Maui Sunset Resort, offering stunning pool and ocean views from the living room and a spacious private deck perfect for sunset watching. The condo features a large, renovated interior with modern appliances, including a full kitchen, washer and dryer, and air conditioning for your comfort.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Say aloha to your Big Island getaway at White Sands Village, an oceanfront condo complex nestled in the picturesque town of Kailua-Kona. This inviting 2-bedroom, 2-bathroom unit is ideal for small families or groups of friends looking to explore the island while enjoying modern comforts and stunning views.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Anini Ohana Estate offers a luxurious and spacious Hawaiian retreat with a combined 4,200 square feet of indoor living space plus 500 square feet of covered lanais, nestled just 225 feet from the ocean. This private estate includes the main house and Ohana guest house (TVNCU# 4255), providing ample space and comfort for families or groups seeking a true island escape.
5 bedroom
5 bathroom
Sleeps 13
Hawaii> Kilauea
Experience the perfect Maui getaway in this beautifully renovated 1-bedroom condo at Maui Sunset, situated on over 5 acres of oceanfront land in Kihei. The condo features new flooring throughout (2023), a spacious and modern interior, and a large private view deck measuring 114 sq feet, offering stunning views of the ocean from morning to evening.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the perfect Maui getaway at Eldorado K102, a spacious and air-conditioned one-bedroom condo that comfortably sleeps six. The unit features two queen beds in the bedroom, a sleeper sofa in the living room, and two full bathrooms with walk-in showers, making it ideal for families or groups.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to this charming Kamaole Sands unit #6206, a tropical-themed 884-square-foot condo designed for comfort and convenience. This air-conditioned suite provides a relaxing retreat with WiFi, flat-screen TVs, DVD player, ceiling fans, and tile floors throughout.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
Welcome to this inviting sixth-floor condo where comfort and relaxation come together. Step inside to discover a spacious living room adorned with charming furnishings, a TV, and refreshing air conditioning. The tasteful decor, featuring modern art pieces, creates a warm and welcoming atmosphere.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the perfect Hawaiian escape at this stunning oceanfront condo located at the Pono Kai Resort. Just 50 yards from the pristine beach and ocean, this unit offers breathtaking views from your private lanai. The resort is ideally situated halfway between the vibrant towns of Poipu and Koloa on the South Shore and Hanalei and Princeville on the North Shore, making it an excellent base for exploring Kauai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Escape to Maui at the Mahana, a tranquil oceanfront condo located on the renowned Kaanapali Beach. This spacious 1-bedroom, 1-bath unit offers breathtaking ocean views from both the bedroom and living room, creating the perfect setting for relaxation and enjoyment.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to our charming Kona Palisades studio, a cozy, private ohana designed for comfort and relaxation. Located just a few miles from Kona International Airport, this stand-alone unit offers an ideal base for exploring the Big Island.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
5-bathroom condo offering panoramic views of Kapalua Bay, Molokai, Lanai, and the nearby golf course. This open-concept villa features a spacious lanai perfect for morning coffee or sunset dinners, allowing you to soak in the breathtaking scenery from virtually every angle.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to Hale Hau'oli, a beautiful and tranquil home nestled in the heart of Princeville on the Garden Isle. This charming retreat offers a perfect blend of relaxation and adventure, with stunning nearby beaches, world-class golf courses, and exquisite dining options just minutes away.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Experience the ultimate in oceanfront luxury at Castle Kanaloa at Kona #1902. This spacious condo offers breathtaking views of the Pacific, where you can hear the soothing sound of the waves and catch glimpses of dolphins and whales during season.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> KAILUA-KONA
Updated as of 6/1/23, this stunning two-bedroom, two-bathroom ocean view condo at Kaanapali Shores has been recently renovated to offer a fresh and comfortable stay. The living area features all-new furniture including a pull-out sofa sleeper, side chair, table, and an inviting area rug, creating a cozy and stylish space to relax.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Enjoy stunning ocean views from your 4th-floor lanai at Maui Sunset, a beautifully renovated condo located in sunny Kihei. The interior has been professionally designed with recent upgrades including new stainless-steel appliances in 2024, new flooring throughout in 2023, and fresh couches added in 2022.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Kihei
Discover the beauty of Maui from this charming D306 condo at Luana Kai. This spacious two-bedroom, two-bathroom unit features a comfortable king-size bed in the master loft and two twin beds in the second bedroom, perfect for families or friends.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this beautifully renovated 1-bedroom condo located at Ilikai Marina. The unit features stunning marina and ocean views framed by palm trees, offering a serene backdrop for your vacation. Spanning 651 sq. Registration Number: GE-125-327-8720-01, TA-125-327-8720-01, 260100020018
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Maui Banyan B204, your perfect tropical escape on Maui's beautiful southwest coast! This inviting condo offers a blend of comfort and island charm, making it an ideal retreat for families, couples, or solo travelers seeking an authentic Hawaiian experience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover a serene oceanfront retreat at Hale Kona Kai, where spectacular lagoon and ocean vistas greet you from the private lanai. This beautifully remodeled condo offers a comfortable central A/C bedroom with a king bed, a living room with a queen sofa bed, and a modern kitchen perfect for preparing local delicacies.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
This luxurious top-floor, all-one-level condo offers three spacious bedrooms each with their own bathroom, perfect for groups seeking comfort and privacy. The master suite boasts a king-sized bed, a large soaking tub, double sinks, and a walk-in closet, providing a true retreat with hotel-like amenities and the warmth of a home.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
