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 9400, 50
Immerse yourself in the stunning oceanfront living experience at Keauhou Kona Surf & Racquet Club#3-203. Located on the second floor of building#3 within a secure, gated complex, this meticulously remodeled condo offers unobstructed views of the ocean and Kailua town.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Escape to this stunning North Shore retreat at The Plantation in Princeville, Kauai. This meticulously maintained 3-bedroom, 3-bathroom condo combines Hawaiian charm with modern comforts, making it the perfect base for your Kaua'i adventure. Each bedroom features an en-suite bathroom, offering privacy and convenience for all guests.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Experience the relaxed Hawaiian vibe at Mahina Surf 214, a beautiful two-story condo ideal for families seeking a comfortable stay. This well-equipped unit features a full kitchen where you can brew Kona coffee and prepare your favorite meals. Dine indoors at the table or enjoy al fresco meals on the furnished balcony, soaking in the tropical ambiance.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Aloha and welcome to Honu Hale by Gather, your luxurious Hawaiian retreat in Kona. 5-bathroom estate comfortably hosts up to 16 guests, making it perfect for family reunions, gatherings, or a dream vacation. Situated in an upscale neighborhood just 2 miles from popular beaches like Magic Sand Beach and Kahaluu Beach Park, Honu Hale offers unparalleled access to Kona’s top attractions.
7 bedroom
4 bathroom
Sleeps 16
Hawaii> Kailua Kona
Experience the beauty of Maui from this beautifully decorated one-bedroom oceanfront condo at Paki Maui Resort. Located on the ground floor but elevated above ground level, unit #102 offers breathtaking views of the Pacific Ocean, Moloka'i, and Lana'i.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Immerse yourself in the beauty of Maui from this newly listed oceanfront condo, offering breathtaking views of turquoise waters with Molokai and Lanai in the background. The spacious, modern retreat boasts unobstructed vistas that instantly transport you to a paradise island setting, perfect for relaxation and adventure alike.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Alii Villas 201, your perfect island retreat nestled right on the oceanfront. This beautifully updated condo offers a blend of comfort and convenience, ideal for a memorable Hawaiian vacation. The property features a sleek, well-equipped kitchen with granite countertops and stainless steel appliances, perfect for preparing your favorite meals.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to this beautifully situated beachfront condo at Colony Surf, nestled at the peaceful end of Waikiki in the Gold Coast area. This residence offers a perfect blend of comfort and convenience, just steps away from the pristine sands of Waikiki Beach and close to attractions like Kapiolani Park, the Honolulu Zoo, and the Waikiki Aquarium. Registration Number: TA-205-326-1312-01, GE-205-326-1312-01, 310320100025
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Aloha from the iconic Ilikai in Waikiki! This spacious 1-bedroom condo offers a comfortable open-concept layout with no walls separating the bedroom and living area, providing a seamless living space. The unit features a king bed, a sofa with a full-size sleeper for additional guests, a large closet, flat-screen TV with basic cable, and complimentary Wi-Fi, making it ideal for couples or small families.Registration Number: GE-110-108-7744-01, 1260100070704, TA-110-108-7744-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your own 2-bedroom beachfront Maui condo ideally located just 5 minutes north of Kaanapali in the beautiful West Maui. This spacious oceanfront condo offers breathtaking views framed by the outer islands of Molokai and Lanai, providing a stunning backdrop as you listen to the soothing sounds of the surf from the large lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the beauty of Princeville in this breathtaking waterfall house set on a large 18,000 sq ft property surrounded by lush trees and fruit groves. The residence boasts dramatic 240-degree panoramic views of over 500 acres of pristine Hawaiian jungle and the iconic mountain peaks visible from expansive windows in the kitchen and dining areas.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Princeville
Welcome to Napili Shores F-252 by Gather, exclusively listed by the Stice Team. This charming one-bedroom, one-bath condo comfortably hosts up to five guests, offering an authentic Hawaiian escape in Lahaina, Maui. Located within the renowned Napili Shores Resort, just a three-minute walk from Napili Bay, this second-floor end unit provides breathtaking views of the Pacific Ocean, Molokai, lush gardens, and the West Maui Mountains, setting the scene for a relaxing and memorable vacation.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
Experience the perfect Maui getaway at Maui Sunset A504, a beautifully located condo with stunning ocean peak views. This 1-bedroom, 2-bath unit comfortably accommodates small families or couples traveling together. The private lanai provides an ideal spot for peaceful mornings with coffee, seating four, where you can enjoy the gentle ocean breezes and scenic vistas.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Welcome to Hale Ono Loa #311, a beautifully remodeled condo offering a perfect Maui getaway. This unit features a comfortable Cal King bed, all tile flooring, and a spacious lanai ideal for relaxing and enjoying the breathtaking sunsets. The condo is fully equipped with all essentials, including linens, cooking utensils, and starter supplies, ensuring a hassle-free stay.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Come and unwind at this inviting 1-bedroom condo nestled within the lush grounds of Kiahuna Resort. Perfectly situated in the heart of Poipu, the condo is just a 5-minute stroll to the stunning Poipu Beach, offering a serene escape surrounded by tropical beauty.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the perfect Hawaiian retreat at Waikoloa Fairway Villa F6, a beautifully maintained end unit offering seamless indoor and outdoor living spaces. This 3-bedroom, 3-bath townhome comfortably accommodates up to six guests, making it ideal for families or friends seeking a luxurious getaway.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa,
Treat yourself to this stunning oceanfront condo at Kahana Sunset Resort, a perfect Maui escape. Upon entering, you'll be captivated by spectacular Pacific Ocean views and modern island decor. 5-bath unit offers two private lanais, ideal for sunset viewing, rainbows, and whale watching during winter months.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kahana
Experience the perfect Maui vacation in this beautifully upgraded 1-bedroom, 2-bath condo located in South Maui. As of December 2024, the property now features split air conditioning throughout, ensuring all guests stay cool and comfortable during their stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Paki Maui 120 is a cozy, beautifully remodeled 1-bedroom, 1-bathroom condo situated on the first floor, offering stunning garden views from your lanai and floor-to-ceiling sliding glass doors that fill the space with natural light. The condo does not have air conditioning, but the breezy layout ensures a comfortable stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover a stunning vacation retreat on the 16th floor with panoramic views of the Duke Kahanamoku Lagoon and the Pacific Ocean. This beautifully furnished condo offers a perfect blend of comfort and breathtaking scenery. Step out onto your private lanai to unwind and watch the Friday night fireworks over the lagoon, creating unforgettable memories. Registration Number: TA-063-555-9936-01, GE-063-555-9936-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Embark on a relaxing retreat to our contemporary haven offering breathtaking ocean views and a tranquil atmosphere. This spacious 1,950 sqft home features large windows that frame the stunning scenery, an expansive terrace surrounded by lush foliage perfect for outdoor dining and lounging, and a gentle ocean melody to soothe your soul. Registration Number: GE-185-652-1728-01, TA-185-652-1728-01, 440160030000
5 bedroom
3 bathroom
Sleeps 8
Hawaii> Kaneohe
Experience the ultimate North Shore getaway in this beautiful beachfront condo located in Haleiwa Town. As you step inside, the mesmerizing blue ocean view immediately captures your attention, creating a serene and inviting atmosphere. Relax on the patio while listening to the soothing sound of ocean waves, or step directly onto the sandy beach perfect for sunbathing, snorkeling, stand-up paddleboarding, or bodyboarding. Registration Number: TA-151-194-4192-01, 1660060050025, GE-151-194-4192-01
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Haleiwa
Welcome to Kaha Lani Unit #326, your ideal oceanfront retreat nestled on the top (3rd) floor of this charming Kauai condo. 5-bath unit provides stunning panoramic views and direct access to the pristine sandy beaches of Wailua. Please note, access involves a 28-step walk-up, as there’s no elevator.
2 bedroom
2 bathroom
Sleeps 3
Hawaii> Lihue
Escape to Hana Kama Unit 107, a charming ground-floor 1-bedroom condo situated in Hana Kai’s upper building, offering partial ocean views and a tranquil tropical setting. This corner unit features a comfortable King-size bed in the bedroom and a full-size futon couch in the living area, making it ideal for couples or small groups seeking a peaceful retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hana
This ground-floor tropical oasis is perfectly situated just steps from the ocean, nestled between two beautiful beaches with another protected beach right in front. The spectacular sunsets and 180-degree ocean views from your lanai create an unforgettable setting for your stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this stunning 1200 sq ft condo featuring two bedrooms and two bathrooms, offering breathtaking ocean and mountain vistas from the lanai and Haleakala Mountain views from the bedrooms. The master bedroom boasts a comfortable king-size bed and a private bath, while the second bedroom includes two twin beds and its own bath, perfect for family or friends.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the beauty of Maui at Kaanapali Villas #C259, a stylish studio nestled within a lush tropical garden setting on N. Kaanapali Beach. This upgraded condo offers modern comfort and contemporary design, featuring sleek furnishings, a chic kitchen with stainless-steel appliances, and a luxurious bathroom with walk-in shower.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the comfort and beauty of Kihei Akahi C-108, a meticulously maintained vacation condo that feels like a second home. Personal visits ensure every detail is perfect for your stay, providing a welcoming and well-kept environment.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect blend of luxury and privacy at Maui Kamaole D105, a spacious and beautifully furnished condo located in the heart of Kihei. Just minutes from the renowned Kamaole beaches, including Kamaole Beach Park III across South Kihei Road, this property offers easy access to South Maui’s best swimming, snorkeling, and sunbathing spots.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Valley Isle, ideally located in the heart of Kahana on the stunning island of Maui! This low-density, oceanfront property sits on Kahana Bay in West Maui, offering a tranquil setting with breathtaking views. The 10th-floor corner unit boasts spectacular ocean vistas, allowing you to enjoy mesmerizing sunsets from the comfort of your living room and master bedroom.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to Kaleialoha 401, a beautifully remodeled spacious studio condo nestled in the heart of West Maui. This oceanfront retreat offers stunning mountain views from your private balcony and is perfect for both active families and romantic couples seeking a memorable Hawaiian escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Puumoi Ocean View Hideaway offers a vibrant, comfortable one-bedroom retreat nestled on a 20-acre horse ranch along Hawaii's scenic Hamakua Coast. The property features some of the region's best sunrise vistas, filling the space with natural light and breathtaking scenery.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Papaikou
Kunea Place is a beautiful 3/4-acre property nestled in lush tropical landscaping, offering guests a luxurious retreat with stunning views of the West Maui Mountains and the ocean. The home comfortably sleeps eight guests across four spacious bedrooms, each facing out over the pool, tennis court, or gardens, providing a perfect setting for relaxation and entertainment.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Paia
Enjoy a luxurious escape at Kolea 14F, a stunning 2-bedroom, 2-bathroom villa nestled along the serene waters of Anaeho'omalu Bay on the Big Island. Designed to accommodate up to 5 guests, this spacious home offers a perfect blend of modern comfort and island charm, making it an ideal retreat for families, couples, or discerning travelers seeking exclusivity and relaxation.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Beach Resort
Experience the best of Kauai at Kapaa Shore Resort B118, a charming ocean-view ground floor end unit on the Coconut Coast. This fully furnished one-bedroom condo comfortably accommodates up to 4 guests, including children and infants, making it ideal for families or couples seeking a tropical escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Escape to the scenic Kona Coast and unwind in this stunning oceanfront home located in Kailua-Kona. Perfect for families or groups of friends, Kona Reef offers panoramic views and a tranquil setting next to Honles Beach. Situated near the lively Alii Drive and downtown Kailua Village, you'll find plenty of local restaurants, shopping, and entertainment just minutes away.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
This charming one-bedroom condo offers stunning ocean and garden views, beautifully decorated in authentic Hawaiian style. The spacious bedroom features a comfortable king bed, while the living room has a queen hide-a-bed for extra guests. The unit is fully renovated with stylish tiles in the kitchen and bathroom, a walk-in tile shower, and modern cabinetry.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Hawaiian vacation at Kolea 16B, a highly sought-after second-floor condo located within the gated Waikoloa Beach Resort on the Kohala Coast. This beautifully appointed unit offers stunning garden and mountain views, providing a serene backdrop for your stay.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Beach Resort
Immerse yourself in the stunning oceanfront setting of Maui Ka'anapali Villas #E295, a beautifully upgraded one-bedroom condo located on North Kaanapali Beach. This tropical retreat combines Polynesian and Asian Pacific influences, featuring wood furnishings, vibrant art, high-beamed ceilings, and plantation shutters that create an airy, exotic island vibe.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience the ultimate Maui vacation at Kapalua Bay Villas 23B1, a beautifully upgraded 1-bedroom, 1-bath condo offering direct ocean views from the living area and private balcony. This spacious unit comfortably sleeps four guests with a king-size bed in the bedroom, a sofa sleeper in the living room, and features modern designer touches with exotic woods, stone accents, and brushed stainless appliances.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Come stay at our beautifully remodeled Maui condo, perfectly positioned right across from Charley Young Beach—one of Maui's best swimming and snorkeling spots. Situated on the top (4th) floor of building #1 in the Maui Vista complex, this spacious and luxurious guest suite offers stunning ocean views from the lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Aloha and welcome to Hale Kai #205, your oceanfront retreat on beautiful Maui. This condo is a true tropical haven, featuring Hawaiian-inspired furniture and artwork that create a warm, inviting atmosphere. The full kitchen allows you to prepare your favorite meals, making your stay both comfortable and convenient.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the stunning Kapalua Bay Villa 36G5, a luxurious oceanfront condo on Maui's beautiful coast. This spacious 1-bedroom, 2-bathroom unit comfortably sleeps 4 guests and offers unobstructed panoramic ocean views from its prime location on the point.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your tropical retreat in Maui at our beautifully decorated 1-bedroom condo, unit C307 in Kihei Alii Kai. Located on the third floor in the upper building on the "quiet side" of the complex, this condo offers a peaceful atmosphere with beautiful views of lush tropical flora.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect family getaway at Maui Banyan Q109B, a ground-floor condo ideally situated for easy access to the pool, hot tub, tennis courts, and nearby beaches. Our home-away-from-home has been thoughtfully renovated to ensure comfort and convenience, featuring brand new Casper mattresses, a modern bathroom and kitchen, and in-unit washer and dryer.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Hale Mahina #B-405, a beautiful oceanfront condo nestled within the popular Hale Mahina Beach Resort on West Maui. This top-floor unit provides spectacular views of the Pailolo Channel and neighboring islands, making it an ideal spot for whale watching from your private lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina, Maui
Kahana Manor 701 offers an upscale Maui retreat with two bedrooms and two bathrooms, ideal for up to four guests seeking comfort and style. The penthouse is situated on the top floor, accessible via elevator with a single flight of stairs to reach it.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your dream vacation spot at White Sands Village in Kona, Hawaii. This beautifully appointed second-floor end unit condo offers breathtaking ocean views and a prime location just steps from Magic Sands Beach. Designed for comfort and convenience, the 2-bedroom, 2-bathroom condo features a luxurious King bed and two twin beds that can be converted to a King upon request for added flexibility.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Immerse yourself in the beauty of Hawaii with this stunning cliffside vacation home in Hilo. Featuring two bedrooms and two bathrooms, the property offers breathtaking water views from two private decks and expansive picture windows that bring the outdoors inside.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hilo
This second-floor, fully remodeled one-bedroom, one-bath condo at Kihei Surfside offers breathtaking ocean views from the living room, kitchen, and private lanai. Located just 70 feet from the ocean and 100 feet from one of Maui's top beaches, it provides unparalleled access to water activities like snorkeling, kayaking, and paddleboarding.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
