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 6250, 50
Welcome to Hokulani 414 at Honua Kai Resort, your perfect Maui retreat! This bright and airy 1-bedroom, 1-bath condo offers stunning ocean views and a comfortable, stylish interior. Step onto the lanai and enjoy southern exposure and partial ocean vistas—perfect for sipping coffee at sunrise or unwinding with sunset views.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Immerse yourself in breathtaking ocean and lagoon vistas from the 19th floor of the iconic Ilikai Towers. This beautifully appointed condo offers stunning views from the bedroom, living room, and kitchen, making every moment memorable. Watch Friday night fireworks from your private lanai, creating perfect vacation memories.Registration Number: GE-004-925-6960-01, 260100070000, TA-004-925-6960-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Unit 231 in the Plumeria building at Kauhale Makai offers breathtaking ocean, island, sunset, and mountain vistas from both the living room and bedroom. Being on the second floor and just 100 yards from the water, this is one of the closest one-bedroom units to the ocean in Kihei.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to our Ohana at Maui Lemurian Condos, nestled within the beautiful Kamaole Sands resort in Kihei. This prime location offers direct access to Kamaole III Beach, renowned for its golden sands, excellent swimming, snorkeling, and breathtaking sunsets.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to this stunning Maui villa situated in a private gated community, offering upscale amenities and ample space for a memorable vacation. The spacious two-story layout features seamless indoor-outdoor living, with the upper level opening onto a large covered lanai perfect for outdoor dining and relaxation.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Wailea
Discover your perfect Maui getaway at Kihei Akahi, unit D-505. This peaceful one-bedroom condo offers stunning panoramic ocean views from the lanai, ideal for relaxing and soaking in the Hawaiian sunsets. 5 lush acres. Just across from Kamaole Beach #2, one of South Kihei's most beautiful beaches, you'll be steps away from soft white sands and crystal-clear waters.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to our tropical paradise in Waikiki! This listing offers two separate studio apartments within the same building, perfect for families or friends traveling together. Each studio is tastefully decorated with island-inspired furnishings, offering a spacious layout with a large balcony, fully equipped kitchen, and all the comforts of home. Registration Number: GE-193-411-2768-01, 260190010000, TA-193-411-2768-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
This stunning 26-acre oceanfront property located on Kaanapali Beach offers a luxurious resort experience blending comfort and convenience. The resort features three swimming pools, whirlpools, and a poolside bar and food service, providing ample relaxation options.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Located at Ma'alaea Surf, Unit C4 offers a fully upgraded, ground-floor experience just steps from a small pool. This beautifully furnished condo features a contemporary kitchen with alder cabinets and Caesarstone counters, perfect for preparing meals after a day exploring Maui.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover paradise at this beautifully renovated seaside condo in Ali'i Kai Condominiums. The ground-level unit offers breathtaking views of lush tropical landscapes on the mountain side and partial ocean vistas on the ocean side. Witness spectacular sunsets and rainbows over the iconic Bali Hai mountain range right from your own home away from home.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to Maui Banyan H-308, a spacious and beautifully furnished 1-bedroom, 2-bathroom condo offering breathtaking ocean views from a private lanai. Perfect for long stays (28+ nights), enjoy a 15% discount, excluding peak holiday weeks. Recently renovated, the condo features a modern kitchen and bathrooms, a luxurious King-sized Beautyrest bed, and contemporary decor that creates a bright, tropical ambiance.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this beautifully updated top-floor one-bedroom condo at Kihei Akahi, perched high with panoramic views of the Pacific, West Maui mountains, and neighboring islands. The unit is located at the back of the complex, ensuring peace and quiet with no street noise, just the soothing sounds of the surf at Kamaole II Beach, a short walk away.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Discover this beautifully upgraded 1 bedroom plus den condo at Honua Kai Resort, offering breathtaking views of the West Maui Mountains. Spanning 915 sq. , the condo features designer furnishings and two private sleeping areas, making it ideal for families or groups seeking comfort and style.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kaanapali
Experience the best of Waikiki from this first-class condo in the iconic Ilikai Hotel. This spacious 2-bedroom, 2-bathroom unit offers breathtaking views of the Pacific Ocean and Honolulu's city skyline. Recently remodeled, the condo boasts a modern, comfortable living area perfect for relaxing after a day of exploring. Registration Number: GE-000-777-4208-01, TA-000-777-4208-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Aloha from Midway Vacations! Our expert team is here to make your Hawaiian dream come true with a clean, well-maintained condo just a block from Waikiki's renowned waves. This inviting, Southeast-facing unit on the 37th floor offers breathtaking views of Diamond Head and the ocean from the living room, bedroom, and balcony, creating a picture-perfect backdrop for your stay.Registration Number: 260250050427, TA-174-630-5024-01, GE-174-630-5024-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the beauty of Maui from this stunning oceanfront condo in Kihei. Located on the 4th floor, this unit features a California King bed, comfortable pull-out sofa, and a private lanai overlooking the waves. The condo is fully equipped with modern amenities including a washer and dryer, full kitchen with microwave, stove, and coffee maker, 46" LCD TV with cable, DVD player, and free high-speed Wi-Fi.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the beauty of Maui from this luxurious 2-bedroom, 2-bath condo at Maui Kamaole. Spanning over 1,200 sq feet, this beautifully decorated unit offers breathtaking views of the Pacific Ocean, West Maui mountains, and Haleakala. The spacious interior features upgraded quartz countertops throughout, a fully stocked gourmet kitchen, and comfortable furnishings that make you feel right at home.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Escape to the stunning shores of South Maui with this charming 1-bedroom condo at Kihei Kai Nani Resort. , the interior offers modern comforts including WIFI, air conditioning, and two new TVs, ensuring a relaxing stay. , provides a perfect spot to watch incredible sunsets and observe the lively beach scene below.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Napili Surf 207, a cozy beachfront condo on Maui's upper west coast offering breathtaking ocean and beach views. Nestled just steps from Napili Bay, this charming retreat combines comfort and convenience for an unforgettable island getaway.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
Welcome to this spacious and beautifully renovated two-story house located in Hauula, North Oahu, just two miles east of the Polynesian Cultural Centre. Spanning over 3,000 square feet, this property offers seven air-conditioned bedrooms and is ideal for large families, groups, or retreats seeking comfort and convenience.
8 bedroom
3 bathroom
Sleeps 18
Hawaii> hauula
Our ground-floor one-bedroom condo in Kona Makai offers a luxurious yet affordable vacation retreat. Featuring a Cal King bed and air conditioning, the unit is fully equipped for your comfort and relaxation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Discover Maui paradise at Kihei Ali'i Kai D204, a beautifully remodeled condo perfect for your island escape. Fully updated in November 2021, this bright and modern retreat offers a comfortable and stylish stay with stunning ocean glimpses from your spacious balcony.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Escape to this charming ground floor condo, ideal for families and guests of all ages. step onto your private lanai and discover a sprawling six-acre yard perfect for outdoor fun. Just 300 feet from the ocean and a quick 17-foot walk to the pool, this fully stocked home offers comfort and convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Come experience paradise at Keauhou Akahi unit #408, a comfortable two-bedroom, two-bathroom condo in the scenic Keauhou area of Kona. Enjoy your private lanai offering stunning ocean and golf course views, perfect for relaxing mornings with Kona coffee or whale watching during winter.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Our charming "Happy Place" is nestled in the lush rainforest of Volcano Village, just across from local restaurants and the Sunday Farmers Market. Located only 5 minutes from Volcanoes National Park, this cozy retreat offers the perfect home base for exploring eastern Hawai'i's natural beauty and vibrant local culture.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Step into this charming Hawaiian plantation-style villa, ideally situated across the lake from King's Shops and just a short stroll to the picturesque Anaeho'omalu Bay. Located on the 14th hole of a world-class beach golf course, this retreat offers the perfect blend of luxury and convenience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Immerse yourself in the natural beauty of the Big Island with this solar-powered 3-bedroom house in Kawaihae. Located just a scenic 30-minute drive north of Kona Airport, this home offers a perfect blend of comfort, convenience, and stunning surroundings.
3 bedroom
1 bathroom
Sleeps 5
Hawaii> Kamuela
The Royal Garden at 440 Olohana St. offers a cozy 352 sqft studio, perfect for exploring Waikiki. Located within walking distance to Luxury Row on the Waikiki Strip, Ala Moana Shopping Center, and the Hawaii Convention Center, it's an ideal base for your Hawaiian getaway.Registration Number: GE-145-203-3536-01, TA-145-203-3536-01, 1260160390124
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Escape to this charming, private cottage nestled in the lush forests of Kaloko Mauka, perched at 2700 feet on Mount Hualalai just above Kailua Kona. Set on five tranquil acres, the stand-alone home is surrounded by native ohia trees and offers a peaceful retreat from the bustling town below.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Discover the beauty of Maui from this charming beachfront 2-bedroom, 2-bath condo in Kihei. Located on the renowned Charley Young Beach, this property offers the perfect blend of comfort and scenic views. Nestled within a quiet, 12-unit complex, guests can enjoy the tranquility and aloha spirit that Maui is known for.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a beautifully renovated upscale condo in Hawaii, featuring all new bathrooms, kitchen, flooring, and furnishings. This second-floor unit offers a clean, comfortable space with great ocean breezes and air conditioning for your comfort.Registration Number: TA-129-794-3040-01, 1570010290080, STR # 164, GE-129-794-3040-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
Experience the ultimate Maui vacation in this stunning beachfront condo offering breathtaking ocean views and direct access to a sandy beach. Spanning over 2,000 square feet, including two spacious lanais, this three-bedroom, two-bathroom unit is one of the largest and most luxurious on the island.
4 bedroom
2 bathroom
Sleeps 12
Hawaii> Lahaina
5-bath villa spanning 2,426 sq. ft. Located in the desirable Wailea area, this townhouse offers a perfect blend of comfort, style, and convenience. The residence features a spacious master suite with a premium king bed and private lanai, a second bedroom with a queen bed, and a third bedroom with two queen beds, accommodating up to eight guests.
3 bedroom
4 bathroom
Sleeps 10
Hawaii> Wailea
Experience the ultimate beachfront escape at Laie Beach Retreat, a renowned North Shore Hawaii property perfectly situated on Hukilau Beach in Laie Bay. This charming Hawaiian home offers a true oceanfront experience with no crossing roads to access the sandy beach, making it ideal for families, friends, or remote workers seeking paradise. Registration Number: GE-142-706-2784-01, TA-142-076-2784-01, 550030190000
5 bedroom
6 bathroom
Sleeps 16
Hawaii> Laie
Aloha from Midway Vacations! Our dedicated team of local hospitality experts is here to make your Hawaiian escape unforgettable. When you choose to stay with us, you get a professionally cleaned, meticulously maintained property with 24/7 support to assist you anytime.Registration Number: GE-113-942-6816-01, TA-113-942-6816-01, 260250050732
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this charming, newly renovated 1-bedroom apartment nestled in the historic Wailuku Town of Maui. This permitted and legal home offers a private entrance and parking right at your doorstep, providing a safe and convenient base for your island adventures.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuku
Discover the perfect Hawaiian getaway in our stunning oceanfront condo located in the charming town of Kailua. This well-maintained complex offers breathtaking views from the lanai, where the soothing sounds of surf on the rocks create a tranquil atmosphere.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua-Kona
Pili Mai 12 offers a luxurious, brand-new two-story condo nestled within the upscale Pili Mai community in Poipu. The property seamlessly blends traditional Hawaiian charm with contemporary comforts, making it an ideal retreat for families, couples, or groups seeking a premium vacation experience.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Discover this beautifully remodeled beachfront condo in Maui, offering stunning ocean views and modern comforts. The one-bedroom unit features a Cal King bed, stylish bathroom, fully equipped kitchen with stainless steel appliances, and a private lanai to enjoy breathtaking sunrises over Haleakala.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maalaea
Discover this cozy and inviting one-bedroom condo at Turtle Bay, ideal for up to five guests seeking an authentic Hawaiian experience. The spacious master bedroom features a comfortable Cal King bed and is equipped with air conditioning to ensure a restful stay. Registration Number: 1570010290078, TA-058-275-2768-01, GE-058-275-2768-01
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kahuku
Spectacularly renovated and offering a prime oceanfront location, this condo at Paniolo Hale is a true paradise. First time available to rental guests, it boasts 1900 sq. ft. of stylish living space and breathtaking views from nearly every corner.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Maunaloa
Welcome to your perfect Maui getaway at our Kahana Outrigger condo, a true home away from home. This spacious 3-bedroom, 3-bathroom unit offers stunning ocean views and incredible sunsets from every room. The condo features two primary bedrooms with luxurious king beds and en suite bathrooms, providing ample privacy and comfort.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to our beautiful historic Hawaiian plantation home and charming cottage, ideally situated beachfront on over a third of an acre at Hanalei Bay's renowned Pine Trees Park. This picturesque, single-level compound offers a perfect retreat for families and friends seeking a blend of relaxation and adventure.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Hanalei
Experience breathtaking panoramic views of Lanai and Molokai from this spacious, clean studio at Maui Kai Condominium, Lahaina. Watch seasonal whales breach, see sea turtles and fish swim by, and enjoy stunning sunsets from your lanai.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover this amazing country retreat nestled on the coveted North Shore of Oahu. Perfect for creating unforgettable memories with family and friends, this private estate boasts stunning ocean views and is just a short drive from world-renowned beaches like Sharks Cove, Waimea Bay, and Haleiwa Town. Registration Number: GE-765-435-6650-01, TA-776-565-9860-01, 590170650000
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Haleiwa
Escape to this stunning, fully equipped home perched above Waipio Valley, offering mesmerizing views of the ocean and lush surroundings. The spacious 4-bedroom, 2-bath layout provides ample space for families or groups of friends to relax and soak in the tropical ambiance.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Honokaa
Indulge in an unforgettable Maui escape at Kamaole Sands 1-403, a spacious two-bedroom, two-bathroom condo boasting stunning ocean vistas from a large lanai. The open-plan living area features vaulted ceilings, three ceiling fans, and a striking spiral staircase leading to the upstairs bedroom and bath, creating a charming and airy atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to this charming Kauai vacation home, ideally located just a short walk to the sandy beaches of the island and close to resort amenities like a swimming pool and tennis courts. This welcoming residence offers a perfect blend of relaxation and adventure, making it ideal for families and couples seeking an authentic island experience.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Poipu
Discover this beautifully updated ground-floor condo at Maui Vista, offering easy access to lush gardens, a heated pool, and the scenic walkway to Charley Young Beach. The condo is thoughtfully furnished with a well-stocked kitchen, a 55" Smart TV, and a luxurious Cal King bed with a pillow-top mattress for restful nights.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> KIHEI
This stunning single-story waterfront residence in Hawaii Kai offers a perfect blend of comfort and scenic beauty. Inside, you'll find a fully equipped kitchen, spacious living room, and four bedrooms with three bathrooms, all thoughtfully furnished for a relaxing stay.Registration Number: TA-068-140-0320-01, GE-068-140-0320-01, 390680200000
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
