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 6650, 50
Welcome to your Maui paradise. This stunning 2-bedroom, 2-bath condo offers breathtaking ocean views from the lanai, perfect for morning coffee or whale watching during season. The recently updated kitchen features granite countertops, a marble backsplash, and Whirlpool appliances, making meal prep a breeze.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
This stunning condo is nestled in the desirable Hale Kamaole complex, ideally located in the heart of the U-shaped property. Positioned exactly in the middle, it offers unobstructed views of the ocean and beautifully manicured grounds, making it a perfect spot for whale watching from the lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the ultimate Maui retreat at Kamaole Sands 5-303, a beautifully remodeled third-floor condo with stunning ocean and tropical courtyard views. This upscale unit features a Hawaiian-inspired decor with oversized Italian tiles, granite countertops, and stylish Plantation shutters, creating a luxurious yet inviting atmosphere for your stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to our exquisite oceanfront Maui home, where breathtaking 180° views of the Pacific Ocean await you. This private sanctuary offers a truly special experience, featuring exclusive access to your own sandy beach entrance that leads directly to the ocean.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Kahana Sunset unit C5 is a charming beachfront condo nestled on the tranquil Keonenui Bay on Maui's West Coast, between Kaanapali and Kapalua. This unique property offers direct access to one of Maui's most romantic white sandy beaches, perfect for relaxing, exploring, or enjoying breathtaking views of Molokai and Lanai islands.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this beautifully renovated first-floor condo in the heart of Kihei, Maui, offering easy access to the renowned Kamaole Beaches I, II, and III. Completed in September 2015, the condo boasts high-end kitchen cabinets, granite countertops, stainless steel appliances, recessed LED lighting, porcelain tile flooring, new ceiling fans, and a modern air conditioning unit, providing a comfortable and stylish retreat.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Welcome to your perfect Maui getaway in this stunning oceanfront condo at Royal Mauian Resort #316, offering spectacular views of three islands from your private lanai. Located in South Kihei, this unit provides easy access to two of Maui's most beautiful beaches, Kamaole I and II, just a minute walk away.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
This charming third-floor villa offers 818 square feet of comfortable living space with stunning views of lush gardens and partial ocean glimpses. Wake up to the aroma of fresh Maui coffee on the lanai, perfect for enjoying morning serenity. The condo features modern amenities including air conditioning, WiFi, flat-screen TVs, DVD player, ceiling fans, and a washer/dryer for your convenience.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
Experience the ultimate Kauai vacation in this stunning oceanfront ground-floor condo located on the sunny South Shore. Just thirty feet from the water's edge, this 1-bedroom, 1-bath unit offers spectacular ocean views and direct access to some of the island's best snorkeling spots, including Lawai Beach and Prince Kuhio Beach.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to Sugar Beach #521, your perfect Hawaiian retreat nestled on Maui's beautiful south shore. 5 miles of pristine sandy beach from your spacious lanai. Ideal for relaxing strolls during sunrise or romantic sunset walks, the beach is just steps from your door.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian getaway at Fairway Villa #2217, a beautifully renovated contemporary two-bedroom, two-bath condo offering stunning views of Waikiki, Diamond Head, and the Pacific Ocean. This spacious 723 sqft condo, plus a 151 sqft lanai, comfortably sleeps up to five guests and features modern furnishings, a fully equipped kitchen with new appliances, and a cozy living area with a 55" Sony Bravia Smart TV and gaming system. Registration Number: TA-088-347-5968-01, GE-088-347-5968-01, 260210210269
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
The Dancing Dolphins Point Villa offers a luxurious and tranquil retreat on Hawaii's Kohala Coast. With breathtaking views of the ocean and Mauna Kea Mountain, this villa combines natural beauty with modern comfort. Wake up to the sunrise from the lanai, listen to tropical birds, and breathe in the fragrant Hawaiian flowers.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimea
Experience ultimate Maui beachfront living in this gorgeous, private condo located on the stunning Keawakapu Beach. The unit offers spectacular ocean views and tropical scenery from every window. Situated in Hale Hui Kai, a small and exclusive 40-unit complex, this property boasts proximity to the ocean with private access to the sandy white beach just steps away.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the best of Big Island living in this beautifully remodeled condo at Kona Pacific. This spacious unit offers 2 bedrooms and 3 bathrooms, perfect for families or groups seeking comfort and style. The large 260 sq ft lanai with fans provides stunning ocean views, ideal for relaxing while listening to the sounds of Hawai'i.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
This beautifully renovated condo offers a stylish and comfortable retreat on the Arnold Palmer golf course, just minutes from Turtle Bay Resort. The unit features a spacious open-plan living area with a large kitchen, perfect for preparing meals with modern appliances including a refrigerator with filtered water and ice, stove, oven, microwave, dishwasher, crockpot, rice cooker, and coffee maker. Registration Number: GE-166-672-7936-02, TA-166-672-7936-01, 570010270133
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Discover the perfect Hawaiian getaway in our beautifully renovated condo located in the prestigious Haliʻi Kai community of Waikoloa. Designed to comfortably sleep 6, this cozy yet spacious unit offers modern comforts and stunning views.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikola
Experience the comfort of this spacious 3-bedroom, 2-bath condo located in Building 5 at Kamaole Sands Resort. Recently renovated with a stunning coastal beach theme, this ground floor corner unit offers privacy and convenience on one level, eliminating stairs or lofts.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience this newly built, spacious beachfront home located directly on Hilo's top beach park. The house spans 2,400 sq ft and features two bedrooms and two bathrooms upstairs, along with two bedrooms and one bathroom downstairs, comfortably accommodating up to 15 guests.
6 bedroom
3 bathroom
Sleeps 15
Hawaii> Hilo
Immerse yourself in the ultimate island experience at this stunning oceanfront home in Kona Bay Estates, professionally managed by Kona Coast Vacations.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua Kona
Escape to Sea Village #1302, a charming oceanfront condo in Kona offering breathtaking views and modern amenities. This two-bedroom retreat is your perfect base for exploring Hawaii's natural beauty and vibrant culture.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Immerse yourself in the ultimate Maui getaway at Makena Surf Resort. This meticulously cleaned and sanitized condo offers breathtaking ocean vistas from your private lanai, perfect for romantic evenings or family relaxation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Makena, Maui
Come stay in our luxurious 3rd-floor, wrap-around corner oceanfront condo, offering breathtaking 270-degree views of Molokai and Lanai from a spacious 1400 sq ft interior. 5 baths, and is fully furnished with modern amenities including two new AC units for comfort.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kahana
Welcome to Kuilima Estates West #85 by Gather, a beautifully updated corner unit nestled near the iconic Turtle Bay Golf Course on Oahu’s stunning North Shore. This spacious three-bedroom, two-bathroom condo comfortably hosts up to seven guests, making it an ideal retreat for families, friends, or groups seeking a relaxing Hawaiian getaway. Registration Number: 570010290085, STR 2211-CCH-0332, GE-066-635-4688-01, TA-066-635-4688-01
4 bedroom
2 bathroom
Sleeps 7
Hawaii> Kahuku
Welcome to Mahina Surf 215, a delightful Maui retreat offering breathtaking ocean vistas and a welcoming island ambiance. Upon entering, you'll be greeted by the soothing scent of plumeria and the gentle trade wind breezes that carry through the open, whimsical decor celebrating Hawaii's natural beauty and sea turtles.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to paradise at Waikoloa Colony Villas, a gated community within the renowned Waikoloa Beach Resort. This beautifully furnished 2-bedroom, 2-bath condo offers the perfect setting for a relaxing Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This exceptional end unit on the third (top) floor offers arguably the best ocean views on the island, with 150-degree vistas that encompass both sunrise and sunset sights. Located at Lae Nani, The Jewel of the Coconut Coast, it’s the closest condo to the pool and ocean, providing unparalleled convenience and scenery.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapa?a
Welcome to our beautifully remodeled Kihei condominium located within the lush 15-acre Kamaole Sands complex in Maui. This ground-floor unit offers a perfect blend of comfort, style, and convenience, making it an ideal choice for your island escape.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Hale Pohaku Kai offers a harmonious blend of elegance and comfort in a stunning Poipu location. This spacious two-story house comfortably sleeps six and features three bedrooms and three full bathrooms, including two primary suites with king beds and private lanais.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Aloha! Welcome to UNIT #111 (Building 18), a charming deluxe garden view condo located on the ground floor, offering easy level access without stairs and convenient parking. This cozy 1-bedroom unit comfortably sleeps 4, featuring a plush king-sized bed in the bedroom and a fold-out queen sofa in the living room, perfect for families or couples.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the perfect Hawaiian getaway in the Hibiscus Suite, a spacious garden studio located at Hale Pua Villa in Beachside Poipu. Just a short 100-yard walk (about a minute) from Poipu Beach Park, this location offers easy access to the blue waters of the Pacific for surfing, snorkeling, swimming, or simply sunbathing.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Welcome to Waikiki Shore #1408, a stunning one-bedroom condo offering breathtaking ocean views right on the famous Waikiki Beach. This 706 sqft unit provides the perfect tropical retreat, allowing you to step directly onto the soft sands of Waikiki Beach from the building itself. Registration Number: GE-142-199-8592-01, TA-142-199-8592-01, 260040120141, 1933
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This ocean-view top-floor condo offers the perfect blend of comfort and paradise in South Maui. Relax on your lanai and watch sea turtles frolic in Turtle Cove, or take in breathtaking sunsets over the turquoise ocean. The condo's prime location means you can snorkel right in front of the building, swim or boogie board at two nearby top beaches, or lounge by the heated oceanfront pool.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
This charming plantation-style home offers a tranquil retreat nestled among banana and mango trees on a lively organic farm. With breathtaking views of the Kona coast, the property features two comfortable bedrooms, each with Cal King beds, fresh linens, and large windows that showcase lush tropical scenery.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kealakekua
Welcome to your tropical escape at Grand Champions Villas in Wailea, Maui, where luxury comfort blends seamlessly with tranquility. This beautifully appointed two-bedroom, two-bathroom condo offers a relaxed Tommy Bahama-inspired vibe, perfect for unwinding and enjoying the island lifestyle.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience a peaceful getaway in this beautiful private home located on sacred Hawaiian land near Kailua Kona. The property offers stunning ocean views and is just a short walk to Magic Sands Beach, one of the area's top beaches. Whether you're seeking a family-friendly retreat or a relaxing escape, this house provides all the comforts of home, including a fully equipped kitchen with granite countertops, spacious living areas, and a large master suite with an en-suite bathroom.
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience breathtaking ocean views from the lanai of your charming Maui condo at Kamaole Nalu, perfectly situated just steps from the beautiful Kamaole I & II beaches. This small, friendly resort offers a peaceful community atmosphere with a large lawn area featuring lounge chairs and tables, ideal for relaxing while watching turtles and whales or enjoying a meal outdoors.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the ultimate Maui getaway in this beautifully renovated oceanfront 1-bedroom, 1-bath condo. Wake up to magnificent ocean views and step out to the pristine Sugar Beach, just a 5-minute walk away. The condo features a comfortable queen bed, a queen sofa bed, and a twin air mattress, making it perfect for couples or small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuku
Emmalani Court is a luxurious 42-unit condo complex perched on a bluff overlooking the ocean in Princeville, Kauai. Unit 221, situated at the quiet end of the complex, offers nearly 1500 square feet of elegant living space on the second floor, ensuring tranquility and unobstructed views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Immerse yourself in the serene beauty of Maui at Hale Hui Kai 208, a charming beachfront condo located directly on Keawakapu Beach, one of Maui's most beautiful and less crowded beaches. This unit offers peaceful views of lush tropical vegetation and the shimmering blue waters just steps from your door, providing a perfect retreat from the busy world.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Hana Homestead offers an eco-conscious sanctuary nestled in the charming town of Hana. This spacious home boasts breathtaking ocean views, large decks, a new hot tub, and an infrared sauna, perfect for relaxation and rejuvenation.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Hana
Blue Makai offers a spacious and tranquil setting on the shoreline, featuring a large yard shaded by trees near the water's edge. The property provides breathtaking views of the Pacific Ocean, perfect for enjoying the sights, sounds, and breezes of the sea.
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Kamuela
Experience the ultimate Kauai getaway at Moku Hale, a serene and luxurious condo nestled on the Kiahuna Golf Course. This beautifully appointed second-floor unit offers breathtaking views from each private lanai: the front lanai presents picturesque mountain and golf course vistas, while the expansive back lanai overlooks swaying palm trees, the sparkling Pacific Ocean, and provides perfect vantage points for sunsets and winter whale watching.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Escape to our elegant villa in the heart of Ko Olina, Hawaii. This spacious property comfortably hosts six guests within a gated community, offering privacy and luxury. The villa boasts a private pool, perfect for relaxing after a day exploring the stunning lagoons and beaches nearby. Registration Number: 910560050070, TA-200-188-7232-01, GE-200-188-7232-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Aloha and welcome to Maui! This gorgeous, spacious 2-bedroom, 2-bath condo at Honua Kai, Konea 139, offers the perfect blend of luxury and convenience. Located on the first floor, it provides easy access to the pool and resort amenities, with a large private wrap-around lanai ideal for dining or relaxing while enjoying breathtaking ocean and island views.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this beautifully renovated private townhome nestled in a tranquil area with the best beach access in Princeville. The property offers stunning views and easy walking distance to pristine beaches, making it an ideal spot for your Hawaiian vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> princeville
Koni Kai, meaning Passionate Sea in Hawaiian, is a beautifully designed and exceptionally comfortable oceanfront home perched on an ancient lava flow in Milolii, the oldest fishing village in Hawaii. This property has been thoughtfully equipped with everything needed for a memorable vacation, from modern appliances to relaxing outdoor spaces.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Captain Cook
Welcome to Kona Riviera Villa #104, your ideal tropical retreat on The Big Island of Hawaii. This beautifully appointed villa offers a cozy and bright interior, perfect for relaxing after a day of adventure. The spacious living area features plush furniture and a large TV, creating a welcoming atmosphere to unwind or enjoy movie nights.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover your perfect Maui retreat in this charming one-bedroom, one-bath condo located in the heart of Kihei. The warm tropical decor sets a relaxing tone immediately, making you feel right at home. Situated on the main floor with no stairs or elevators, this low-rise property offers easy access and a secure, peaceful environment.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
A slice of paradise awaits at this beautiful West Maui vacation rental located within the Maui Eldorado Resort! This well-appointed 2-bedroom, 2-bath condo features an authentic Hawaiian theme, creating a warm and inviting atmosphere for your stay.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
5-bath condo located in the heart of Waikoloa Beach Resort on the Big Island of Hawaii. Situated on the second floor with stairs leading up (no elevator), this spacious unit offers a perfect retreat for families or small groups, comfortably accommodating up to four guests with one king bed in each bedroom.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
