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 10250, 50
Discover this stunning 4,400 sq ft private estate in Napili, Maui, offering spectacular ocean views just 300 feet from the water. The gated property sits on a half-acre lot, providing ultimate privacy and a resort-like atmosphere.
6 bedroom
5 bathroom
Sleeps 12
Hawaii> Lahaina
Escape to the peaceful shores of North Kohala at Awhalecrossing Cabin, perched on the edge of Upolu Point where the Big Island meets the vast Pacific Ocean. This hidden gem offers a private oceanview retreat in a secluded, tranquil setting ideal for those looking to unwind and reconnect with nature.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Hawi
Experience breathtaking ocean and mountain views from this beautifully updated 3rd floor condo with high ceilings and comfortable furnishings. The spacious layout features plush king beds, two full bathrooms, and a sofa bed, providing ample space for family and friends.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapa?a
Experience the ultimate Kauai vacation in Kuhio Shores 416, a top-floor oceanfront condo offering spectacular panoramic views of the Pacific Ocean. This popular one-bedroom unit is just steps from Lawai Beach, providing easy access to pristine tide pools, lush parks, and exotic botanical gardens.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Discover your perfect Maui getaway at Maui Eldorado E104, a spacious and comfortable 1-bedroom condo ideal for families or couples seeking a relaxing vacation. This air-conditioned unit comfortably sleeps up to 6 guests with two queen beds and a sofa sleeper, offering plenty of space to unwind.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Experience a serene getaway at this beautifully remodeled ground-floor condo nestled between pristine beaches and stunning mountains at Turtle Bay Resort. This 1-bedroom, 1-bath unit comfortably sleeps up to 4 guests, featuring a queen-size bed with A/C and a sofa bed in the living room. Registration Number: 570010290053, TA-184-988-0576-01, GE-184-988-0576-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Experience the best of Maui at Royal Mauian 204, a stunning oceanfront condo situated in South Kihei. This spacious 2-bedroom, 2-bathroom unit offers breathtaking views of the Pacific Ocean, neighboring islands, and Molokini Crater. The private lanai provides the perfect spot for romantic beach walks, spectacular sunsets, and seasonal whale watching.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Little Garden by the Sea, a delightful retreat nestled in the heart of Hanalei. This charming 3-bedroom, 2-bath home offers a perfect blend of comfort and natural beauty. Inside, you'll find a fully equipped chef's kitchen with stainless steel appliances, ideal for preparing local delicacies or family meals.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Hanalei
Experience the stunning beauty of Maui at Hale Aloha, a charming one-bedroom corner condo located on the 5th floor of The Maui Kaanapali Villas. This oceanfront resort offers breathtaking views of Molokai and the West Maui Mountains, providing a perfect backdrop for your island escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This beautiful Hawaiian-themed condo offers breathtaking ocean views facing west-southwest, ideal for watching sunsets year-round. Perfectly situated in a quiet residential area, it provides easy access to nearby restaurants, shops, golf courses, swimming, snorkeling, and surfing spots.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the ultimate Maui getaway in this expansive 5-bedroom luxury residence, perfectly designed for larger groups or families seeking both comfort and adventure. This stunning property combines a premier 3BR oceanfront "Ali'i" residence with a neighboring 2BR oceanview suite, offering a total of 3,300 sq ft of elegant indoor living space and an additional 835 sq ft of balcony space to enjoy Maui's breathtaking vistas.
6 bedroom
5 bathroom
Sleeps 16
Hawaii> Lahaina
This beautifully decorated tropical-themed condo offers a welcoming retreat just across from the stunning Kamaole Beach III. Inside, vibrant blue decor, rattan furniture, and wooden surfboard accents create a relaxing paradise atmosphere. The spacious living area features a large couch and a smaller one, perfect for unwinding after a day at the beach.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Keauhou Kona Surf & Racquet Club#4-102, an exceptional direct oceanfront ground floor condo offering breathtaking views and modern comforts. This fully remodeled 1170 sq ft unit features a spacious open floor plan with a contemporary kitchen adorned with gorgeous granite countertops, fully equipped with all cookware, dishware, and appliances needed for a perfect stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Step into paradise with Fairways at Ko Olina 8G by Gather, a beautifully upgraded three-bedroom, two-and-a-half-bath townhome nestled along the scenic Ko Olina Championship Golf Course. This inviting two-story residence combines luxury, comfort, and convenience, offering an ideal retreat for your Hawaii adventure.Registration Number: TA-057-967-4112-01, 910560140063, GE-057-967-4112-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Enjoy breathtaking views from the private balcony of Maalaea Kai 305, a comfortable two-bedroom oceanfront condo in Maui. ' Located in a prime area, this condo is just a 10-minute walk to the beach, perfect for sunbathing and relaxing by the ocean.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
This charming Hawaiian-style home in the prestigious Kahala area offers an authentic local experience with breathtaking views of the Diamond Head crater from nearly every room. Nestled in a quiet, private setting, the property provides a peaceful retreat while remaining close to the best attractions on Oahu. Registration Number: TA-105-889-1776-01, GE-105-889-1776-01
4 bedroom
3 bathroom
Sleeps 7
Hawaii> Honolulu
Experience the perfect Hawaiian escape at Casa de Emdeko 212, a beautifully renovated oceanfront condo managed by Kona Coast Vacations. Recently upgraded with new flooring and a modern bathroom in 2022, this unit offers a warm, inviting atmosphere with all the comforts of home.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to Hale Leilani in Ko Olina, a stunning beachfront condo nestled amidst exotic gardens and tranquil koi ponds. This prime resort location offers quick access to an array of amenities, ensuring a memorable Hawaiian retreat.Registration Number: TA-183-401-0624-01, 910570090227, GE-183-401-0624-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to Banyan Tree 200, a beautifully situated oceanfront retreat in the heart of Kailua Kona. This elegant condo offers stunning vistas of the Pacific Ocean, providing an ideal setting for your Hawaiian holiday. Imagine waking up to the soothing sounds of waves and feeling the gentle ocean breeze from your spacious wraparound lanai, perfect for relaxing or dining al fresco.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
A tropical oasis awaits you in this beautifully renovated Princeville condo, nestled among mature tropical landscaping with stunning mountain and waterfall views. Whether you're seeking relaxation or adventure, this property offers the perfect Hawaiian escape.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Princeville
Welcome to Kulakane 307, a beautifully remodeled oceanfront condo nestled in the heart of Honokowai, West Maui. This spacious 2-bedroom, 2-bathroom retreat offers stunning sea views and modern comforts, making it an ideal choice for your Hawaiian getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to your perfect Hawaiian getaway at Colony Villas #1606, a spacious end-unit condo nestled within lush resort grounds. This beautifully decorated condo combines comfort and style, offering an ideal base for exploring all that the island has to offer.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Experience the ultimate Maui vacation in this beautifully upgraded 2-bedroom, 2-bath condo at Mana Kai Kihei, offering direct oceanfront views and exclusive beach access.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Step into your own luxurious oceanfront sanctuary at Kukui’ula Kai, where thoughtful design and stunning views create an unforgettable vacation experience. This spacious property features two private master suites that offer breathtaking ocean vistas and soothing sounds of the sea, perfect for unwinding after a day of exploring.
5 bedroom
5 bathroom
Sleeps 8
Hawaii> Koloa
Islands at Mauna Lani Q-1: You will love this spacious 2 story Villa which has everything you need for a fun and relaxing vacation! This property has recently had a partial remodel. It is surrounded by lush tropical landscaping and has a beautiful, large area in the back.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Kamuela
Discover this charming Maui condo nestled in the sunny town of Kihei, offering a perfect blend of comfort and natural beauty. Set within a lush 15-acre complex, the property features stunning gardens, waterfalls, and fountains that create a tranquil tropical atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the best of Hilo in this beautifully decorated mid-century modern condo located at Hilo Lagoon Centre. Unit 814 offers breathtaking views of the lagoons, Pacific Ocean, and the charming downtown area, making it an ideal choice for both vacationers and business travelers.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hilo
Escape to Kaleialoha 112, a beautifully renovated oceanfront condo on Maui offering a perfect blend of modern comfort and island charm. The condo features a brand new kitchen with striking Vortice Veneziano granite countertops, solid cherry cabinetry, and the warm appearance of bleached wood floors, creating a welcoming and stylish space.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience breathtaking ocean views from this contemporary 24th-floor condo at Waikiki Banyan. This 533 sqft one-bedroom suite features a spacious lanai with a table and chairs, perfect for relaxing while watching cruise ships pass by. The air-conditioned living area includes a queen sleeper sofa, a 50" TV, and cool tile flooring, creating a comfortable space to unwind.Registration Number: TA-051-254-1696-01, 260250050676, GE-051-254-1696-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your perfect island getaway at Makani A Kai #B-7, a charming and centrally situated condo in Kihei. Located on the second floor, accessible via stairs, this two-bedroom, two-bath residence offers a comfortable and stylish retreat with stunning beach and ocean views.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
Experience breathtaking ocean vistas from this second-floor, air-conditioned condo located in Wailua Bay View. Wake up to the sound of waves crashing on Wailua Beach, a popular spot among kite surfers, and watch the Wailua River flow seamlessly into the bay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapa?a
Welcome to Endless Summer Hale, a stunningly renovated home situated right across Kahalu'u Beach on the historic Ali’i Drive. This property offers a perfect blend of modern comfort and natural beauty, making it an ideal getaway for families and groups seeking a relaxing beachside retreat.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kailua Kona
Escape to Napili Shores I270, a beautiful oceanfront studio nestled on Maui's serene Northwest shoreline. Wake up each morning to breathtaking direct ocean views, the soothing sounds of the ocean, and vibrant Plumeria flowers blooming right outside your window.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Discover this popular Maui Vista Resort ground floor condo in building 1, offering a comfortable and convenient getaway. The one-bedroom, one-bath unit features a private lanai with garden views, perfect for relaxing after a day at the beach. The bedroom boasts an adjustable king-size bed, while the living room has a sofa bed, accommodating up to 4 guests comfortably.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Maui getaway in this exquisite second-floor one-bedroom condo located in Wailea Ekahi. Boasting breathtaking ocean views and lush tropical gardens from your private lanai, this retreat offers a blend of luxury and comfort.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Wailea
Immerse yourself in the natural beauty of Kauai at this charming oceanfront condominium. With stunning views from indoors and a private lanai, you can enjoy the tranquil ocean and sandy beach right outside your door. The property features a shallow, protected area ideal for children and non-swimmers, offering safe and easy access to the calming waters.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailua
Welcome to Wailua Bay Views 202, a beautifully managed oceanfront condo offering spectacular views from your private balcony. Located just 7 miles from Lihue Airport on Kauai's scenic Coconut Coast, this one-bedroom unit provides easy access to both North and South Shore attractions.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Welcome to Hale Kai #108, your cozy home by the sea that captures the charm of old Hawaii while offering all modern comforts. This beautifully decorated condo features a fully equipped kitchen with new appliances, granite countertops, and travertine floors, providing a luxurious yet inviting atmosphere.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience the ultimate Waikiki getaway in this beautifully furnished 2-bedroom, 2-bath condo located on the 35th floor with breathtaking ocean views. Spanning 1,025 square feet, the unit boasts elegant marble floors, a full-sized kitchen equipped with microwave, refrigerator, stove, oven, dishwasher, coffee maker, rice cooker, toaster, and electric kettle. Registration Number: GE-078-028-0832-01, 260230070121, TA-078-028-0832-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Experience the perfect island retreat in this beautifully furnished 2-bedroom, 2-bath condo located in White Sands Village, one of Kona's premier vacation destinations. Situated directly across from Magic Sands Beach, the only white sand beach between Kailua and Keauhou, this property offers stunning ocean and mountain views.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the beauty of Maui from this popular oceanfront condo at Papakea Resort. Located on the 2nd floor in the F building, this desirable corner unit offers breathtaking, unobstructed views of neighboring islands, perfect for enjoying stunning Maui sunsets, rainbows, turtles, dolphins, and from mid-December to mid-April, the spectacular whale watching.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Maui Kamaole J-104 offers a luxurious and serene retreat just steps from some of Maui's best beaches. Located near the Maui Kamaole's stunning Grotto pool, this beautifully updated condo combines elegance with comfort for the perfect island escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian retreat in this charming 1-bedroom, 1-bath Waikiki Banyan condo located at 201 Ohua Ave. Spanning 533 sqft plus a private lanai, this unit offers a comfortable, well-equipped space ideal for couples or small families. Registration Number: 260250050180, TA-013-456-7936-01, GE-013-456-7936-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Kauhale Makai Unit 627, a top-floor, beautifully renovated 1-bedroom condo offering breathtaking ocean views in Maui. This fully updated unit combines comfort and luxury, making it an ideal choice for your vacation, honeymoon, or special celebration.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei, Maui
' It offers a serene, family-oriented environment with deep roots in Hawaiian traditions, making it perfect for travelers seeking relaxation away from tourist crowds. Ke Nani Kai #132 is a spacious end-unit, the largest of three floor plans, beautifully decorated in vibrant Hawaiian style by the owners.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Maunaloa
Welcome to Alii Cove KK4, a beautiful townhouse nestled in the heart of Kailua Kona, offering stunning ocean views and the calming sounds of waves that set the perfect scene for your Hawaiian escape. This spacious 3-bedroom, 3-bathroom home comfortably accommodates up to 6 guests, making it an excellent choice for families or groups seeking a relaxing long-term stay in paradise.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to Haleakala Shores, a charming community in Kihei offering a perfect blend of owner-occupied and vacation units that feel like a friendly neighborhood. Our condo boasts over 1000 sq ft of living space with stunning 16-foot vaulted ceilings and expansive windows that flood the interior with natural sunlight and refreshing island breezes.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This inviting one-bedroom condo is situated on the third floor, offering stunning views of the shoreline, ocean, and Prince Kuhio Park. Lounge on your private lanai to soak in the breathtaking sights and fragrant tropical breezes of the Garden Island.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Poipu
Experience the perfect blend of luxury and value in our spacious condo at Kanaloa Resort, located just 100 yards from the ocean on the north edge of the complex. , this elegant Hawaiian-style unit offers a tranquil setting facing the Kona Country Club course, ideal for both family vacations and peaceful retreats.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the beauty of Maui from Royal Kahana 701, a beautifully remodeled 2-bedroom, 2-bath condo that comfortably sleeps 6. Enjoy stunning ocean views from the lanai, where you can see all the way up the coast of Kahana, Napili, and Kapalua.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
