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 11550, 50
Experience the beauty of South Kauai from Kuhio Shores 107, a charming ground-floor oceanfront condo. Wake up each morning to the soothing sounds of waves crashing onto nearby rocks, with sliding plantation doors that open directly onto your lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Poipu
Discover this exceptional one-bedroom suite at Honua Kai, a luxurious and spacious 7th-floor "penthouse" located in one of the closest 1BR columns to the ocean, offering stunning views of the Pacific and neighboring islands Lanai and Molokai. The 780 sq ft floor plan provides ample space with a comfortable bedroom featuring a king and queen-sized beds, and a convertible sofa in the living room that can accommodate up to six guests, making it ideal for families or groups.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Enhanced COVID cleaning protocols ensure a safe stay at Sealodge J1, a private and tranquil Kauai condo offering stunning oceanfront views along Anini Reef. Nestled on the edge of a bluff between lush trees and the sea, this unit feels like a secluded little house with breathtaking vistas from every room.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Princeville
Immerse yourself in the beauty of Maui with this charming studio located in the heart of Kihei at the popular Maui Banyan Resort. Just a five-minute walk takes you to Kamaole Beach II, renowned for its soft white sands, clear turquoise waters, and breathtaking sunsets where you might spot turtles!
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Escape to this charming Waikiki beach studio, perfectly situated on the site of the historic Royal Hawaiian coconut grove. Decorated in a tropical style, this inviting space offers a full kitchen, allowing you to prepare your favorite meals after a day of sunbathing, swimming, and surfing just steps away on the beautiful blue Pacific surf. Registration Number: TA-045-163-1104-03, GE-045-163-1104-04, 260230180015
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to your island retreat in east Kauai, where breathtaking ocean views and tropical surroundings set the scene for a perfect getaway. This 739 sq. ft. condo offers a relaxed island lifestyle with modern comforts. The spacious living area is furnished with rattan furniture and tropical accents, creating a cozy and inviting atmosphere.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> LIHUE
Discover this charming 1-bedroom condo located on the second floor of Alii Villas, offering beautiful garden views and direct access to the shoreline. This oceanfront community provides a relaxing atmosphere with a sparkling heated swimming pool centrally located for your enjoyment and a BBQ deck for outdoor dining.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Escape to Kona Bali Kai #220, a charming and family-friendly condo offering breathtaking ocean vistas from your private balcony. This well-equipped unit features comfortable bedrooms with ceiling fans, ensuring a cool and relaxing stay. The full kitchen allows for home-cooked meals, while the in-unit washer and dryer provide convenience after beach days filled with sandy adventures.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Welcome to your perfect island retreat at Islander on the Beach! This top-floor oceanview studio offers breathtaking views of the Pacific Ocean and lush tropical gardens. Equipped with a split Fujitsu AC system, it keeps you cool during warm days.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kapaa
This luxurious oceanfront condo in North Kihei offers breathtaking panoramic views of the Pacific from the living room, kitchen, and bedrooms. The master suite features a plush king bed and a private lanai to enjoy the sparkling ocean scenery. The second bedroom is well-appointed with a queen-sized bed and a newly renovated en suite bathroom, providing comfort and privacy for guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Ko Olina Beach Villas O521 by Gather, a luxurious beachfront retreat located within the prestigious Ko Olina Resort. This elegant three-bedroom, three-bathroom villa on the fifth floor offers breathtaking ocean vistas and a serene island ambiance, perfect for a memorable Hawaii getaway.Registration Number: GE-100-422-6560-01, TA-100-422-6560-01, 910570090064
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to Hale Hawaii Ohana, your ideal long-term retreat in the charming town of Pahoa. This newly-built, single-level home is perfect for stays of 30 nights or more, offering a comfortable and spacious environment to enjoy the Hawaiian lifestyle.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Pahoa
Pali Ke Kua 116 offers a serene oceanfront retreat on Kauai’s North Shore, boasting breathtaking Bali Hai views directly from your private lanai. This cozy, ground-level suite is ideal for couples seeking privacy and natural beauty. The recently renovated space features a light-filled, fully equipped kitchen, a comfortable living room with a sectional sofa, and a dining area that overlook the Pacific, Hanalei Bay, and the Na Pali Coast.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Escape to this inviting 2-bedroom, 2-bath condo in Poipu, offering breathtaking ocean views from your private lanai where every sunset creates a magical moment. The unit comfortably hosts up to 6 guests, featuring a king-sized bed in the master bedroom, two twin beds in the guest room, and a queen sofa bed for additional sleeping space.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> POIPU
Makaha Valley Towers offers a luxurious resort feel with private, breezy, and comfortable accommodations. These spacious 1-bedroom, 2-bedroom, and studio apartments are perfect for relaxing on the leeward coast of West Oahu. Originally built as a Sheraton Hotel, the property has been converted into charming units featuring large bathrooms, walk-in closets, and breathtaking views. Registration Number: TA-077-324-0832-01, GE-077-324-0832-01, 840020100348
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waianae
Welcome to your perfect Maui getaway at Kahana Villa, a beautifully remodeled high-end one-bedroom, one-bath condo on the 4th level offering stunning ocean views. This well-appointed home comfortably sleeps four, featuring a luxurious king bed in the bedroom and a sofa bed in the living room, ideal for families or couples.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the relaxed aloha spirit in this charming furnished Kihei condo featuring authentic Hawaiiana decor. Located directly across from Kamaole Beach Park III, this one-bedroom, two-bathroom unit offers the perfect blend of comfort and convenience for your Maui vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience breathtaking views of the ocean and lush grounds from this top-floor Maui Sunset condo in Kihei. Situated on the fifth floor, this spacious 1-bedroom suite offers direct access to a private 114 sq foot view deck where you can watch the vibrant Maui life unfold from sunrise to sunset.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
5 acres of tropical gardens and close to Maui's best beaches.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honokowai
Experience the perfect Maui retreat in Makani Sands #103, a charming ground-floor condo offering direct access to lush grass and the beach. The private lanai, accessible from the living room, provides an ideal spot to enjoy your morning coffee or watch breathtaking sunsets over the ocean, with outdoor seating for relaxation.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Immerse yourself in the stunning surroundings of Waikoloa Beach Resort with this elegant two-bedroom townhome in Colony Villas. ft. ground-floor villa offers a perfect blend of comfort and natural beauty, ideal for both short and extended stays.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience the ultimate Kauai getaway at Hale Poipu Nani, a charming private residence nestled in the Poipu Kai - Lanai Villas community on Kauai's stunning South Shore. This beautifully furnished home boasts high-end finishes, air conditioning in every room, and a well-equipped kitchen overlooking the lush backyard and tropical landscaping.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Welcome to your deluxe 3-bedroom vacation condo at the renowned Waikoloa Beach Resort on the Big Island. Situated just minutes from the stunning A-Bay Beach, this beautiful condo offers the perfect blend of comfort, luxury, and tropical surroundings.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Our popular ground-floor condo at Hale Kai O'Kihei offers approximately 100 feet of lush grassy area leading directly to the sandy ocean edge, with no roads in between. This prime location provides easy access to ocean activities and long beach walks in either direction.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Polynesian Shores 123, your perfect island retreat in Maui! This inviting ground-floor condo offers a seamless blend of comfort and natural beauty. Inside, you'll find an open-concept living area decorated with vibrant Aloha vibes, featuring a flat-screen TV and a cozy sofa sleeper for extra guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this fully equipped and modern Kona condo conveniently located on Alii Drive. Perfect for a relaxing vacation, it features 2 bedrooms comfortably sleeping 4 guests, two full bathrooms with showers, and an in-unit washer and dryer for your convenience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Enjoy a charming and convenient stay in this adorable condo overlooking Fort DeRussy Beach Park, located just across from Hilton Hawaiian Village. The unit offers a stunning ocean view and is only a block away from the world-famous Waikiki Beach, with easy access to nearby Ala Moana Beach and a variety of local restaurants and shops. Registration Number: TA-203-873-3312-01, GE-203-873-3312-01, 260070260130
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the perfect Hawaiian getaway in this stunning Turtle Bay condo, offering breathtaking sunset views, modern furnishings, and a fully equipped kitchen. Surrounded by a world-class golf course, lush tropical greenery, and vibrant native birds, the property provides a serene and scenic setting for your vacation.Registration Number: GE-030-197-4016-01, 138, TA-030-197-4016-01, 570010290056
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
Our charming and simple gated private lodge offers a cozy retreat close to Volcanoes National Park on the Big Island. The cabin features warm Ohia log touches, a fully equipped kitchen, and a complete indoor bathroom, providing comfort and convenience for your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> mountain View
Experience the perfect Hawaiian getaway in this charming Kailua-Kona condo, ideally situated on the quiet side of Keauhou. This cozy 1-bedroom, 1-bath unit is perfect for couples or small families seeking relaxation and adventure.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover the charm of the Kapalua Resort with this beautifully appointed 1-bedroom, 2-bath condo offering breathtaking ocean views from a large balcony. ft of comfortable living space, including a fully equipped kitchen, washer/dryer, and air-conditioning for your convenience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your perfect island retreat! This charming west-facing 22nd-floor studio at Pacific Monarch offers breathtaking ocean vistas and a prime Waikiki location. The inviting space comfortably hosts up to three guests, making it ideal for a romantic getaway or small family adventure. Registration Number: TA-201-640-5504-01, 260230560135, GE-201-640-5504-01
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Welcome to Waikoloa Fairway Villas D1, a beautifully appointed family-friendly villa located in the heart of Waikoloa Beach Resort. This spacious home is perfect for those seeking a relaxing Hawaiian getaway with plenty of amenities and convenient location.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa
Located on the ground floor and right on the oceanfront in Kahana, this charming 1-bedroom, 1-bath condo offers a perfect setting for a peaceful Maui escape. The living room features a large window and a portable A/C unit, providing comfort during warm days.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the true spirit of Hawaii at Kona Isle, a stunning oceanfront property surrounded by lush tropical gardens and offering direct access to a private beach. Lounge under swaying palm trees and watch dolphins and whales play in the ocean, or take a dip in the solar-heated pool.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to your hidden gem in Aina Haina, Honolulu! This fully furnished midterm rental offers a comfortable and private retreat with 3 bedrooms and 2 bathrooms. The unit features a spacious living room, dining area, a well-equipped kitchen, and a balcony perfect for relaxing or enjoying the Hawaii breeze. Registration Number: GE-057-028-1984-01, TA-057-028-1984-01, 360020580000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to Mahina Surf 107, a welcoming condo in Lahaina offering a laid-back coastal vibe. The open-concept living and kitchen area create a perfect space for entertaining or relaxing after a day at the beach. Step outside onto your private patio and enjoy the salty air, or cool off at the shared pool while the kids splash around nearby.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to paradise at Kepuhi Beach Resort #2236, a beautifully appointed condo nestled in the stunning landscape of Maunaloa, Molokai. This peaceful retreat offers breathtaking ocean views right from your lanai, perfect for sipping your morning coffee or watching the sunset.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Maunaloa
Discover this stunning 19th-floor condo located in the iconic Ilikai Hotel in Waikiki, offering breathtaking views of the Pacific Ocean and Duke Kahanamoku Lagoon. The spacious private lanai is perfect for relaxing and watching the weekly Friday night fireworks. Registration Number: GE-112-221-5936-01, TA-112-221-5936-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to paradise at Wailea Grand Champions 149, a family-friendly condo nestled on Maui's stunning south coast. This spacious unit offers the perfect blend of comfort and convenience, making it ideal for family vacations or a getaway with friends.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Discover this inviting Poipu condo on the beautiful island of Kauai, featuring a private lanai with stunning ocean vistas. The cozy and charming space offers a true tropical Hawaii experience, with the soothing sounds of the surf and lush palm trees creating a peaceful retreat.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Poipu
Welcome to Haleakala Shores, a relaxing two-bedroom, two-bath condo ideally located just across the street from Kamaole Beach Park III. This inviting condo offers a comfortable retreat with a queen-sized bed in the master bedroom and two twin beds in the second bedroom, both with en suite bathrooms for added privacy.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience a relaxing Maui vacation at Mahina Surf 118, a cozy condo ideally located just steps from the shared pool and the beautiful ocean. The private furnished patio offers stunning views perfect for unwinding after a day of exploring. Inside, you'll find a fully equipped kitchen with full-size appliances, including a dishwasher, coffee maker, and blender, making meal prep convenient and enjoyable.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Immerse yourself in luxury with this stunning oceanfront suite located in the iconic Ilikai building in Waikiki. Spanning 1,000 sq ft, this corner unit offers two spacious master bedrooms, each with direct access to lanais that showcase sweeping, unobstructed views of the ocean, Magic Island, sandy beaches, Waikiki Yacht Harbor, and spectacular sunsets. Registration Number: 260100070304, GE-065-712-9472-01, TA-065-712-9472-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> HONOLULU
Welcome to the Honolulu Ala Moana Hotel, ideally situated next to the world-famous Ala Moana Center, Hawaii’s largest open-air shopping mall. This prime location offers easy access to premier shopping, dining, and entertainment options, all within walking distance to Ala Moana Beach. Registration Number: 129906789012, GE-561-456-7891-05, TA-921-456-5543-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Noelani 310, a spacious and beautifully decorated two-bedroom, two-bathroom condo nestled in the sought-after Noelani Resort in Maui. This charming retreat offers all the comforts of home combined with luxurious touches, making it perfect for a memorable island getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Aloha from Midway Vacations! Our dedicated team of local hospitality experts is committed to making your Hawaiian escape unforgettable. When you choose to stay with us, you can expect a professionally cleaned, meticulously maintained property, and our support is available 24/7 for your convenience.Registration Number: 260250050807, GE-174-630-5024-01, TA-174-630-5024-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your Maui paradise at Ocean & Beach Front Maui Condo in Royal Kahana. This 777 sqft one-bedroom, one-bathroom condo comfortably accommodates four guests and offers modern amenities and stunning ocean views. The private unit features central air conditioning, a spacious bedroom with a CalKing bed, and an updated bathroom stocked with towels, body wash, shampoo, and conditioner.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Adventure Awaits Villa, part of the Hawaiian Luxury Collection by Paradise in Hawaii Luxury Homes. This stunning single-level home offers four bedrooms and three bathrooms, perfect for families or groups seeking comfort and elegance during their Hawaiian vacation.
5 bedroom
3 bathroom
Sleeps 11
Hawaii> Waikoloa
Escape to this beautifully maintained fifth-floor 2-bedroom, 2-bathroom condo at Menehune Shores, offering breathtaking ocean vistas and direct beach access. Located on the peaceful left side of the building, this rental provides a perfect retreat for families seeking relaxation and adventure.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
