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 15900, 50
Welcome to Wailea Elua 1802, a beautifully appointed oceanfront condo offering breathtaking views and upscale amenities in Maui's exclusive Wailea/Makena area. This spacious 1-bedroom, 2-bathroom unit spans 1,008 sq ft and comfortably sleeps 2-4 guests, making it ideal for romantic getaways or small family vacations.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea, Maui
A state of relaxation and repose awaits you at The Westin Nanea Ocean Villas, nestled on twenty-six acres of lush Hawaiian landscape. This resort seamlessly combines top-tier amenities with authentic Hawaiian design elements, offering guests an immersive experience in paradise.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to Alii Park Place, a premier development along Alii Drive designed for luxurious and comfortable living. Unit#N-202 offers a beautifully furnished, private retreat with 3 bedrooms and 2 bathrooms, perfect for those seeking tranquility and convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> KAILUA KONA
Discover a stunning beachfront stay in Waikiki with this unique hotel offering three separate rooms, each providing 360sf of comfortable space with ocean views. Each unit includes a king bed, sofa bed, daily housekeeping, coffee maker, mini-fridge, and all linens and essentials, ensuring a hassle-free stay. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Escape to Napili Nest, a beautifully remodeled and private end-unit studio nestled in a tranquil Maui community. Located within minutes of Napili Bay, Kapalua resorts, golf courses, hiking trails, and fantastic dining options, this property offers a perfect blend of comfort and convenience for your island retreat.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Club Wyndham Ka Eo Kai perfectly captures the ambiance of the "Garden Island" while offering a variety of nearby recreational options including family fun adventures, horseback riding and golf. Set amidst a lush tropical garden, the resort offers easy access to top Kauai shopping, dining, entertainment, state parks, national wildlife refuges, beaches, natural landmarks, museums, and historic sites.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to Mahina Surf 205, a beautifully furnished and decorated oceanview condo that offers the perfect blend of comfort and tropical elegance. As you step inside, vibrant tropical hues and Maui-inspired artwork greet you, transporting you instantly to island paradise.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this stunning 8-bedroom home in Poipu Kai, perfect for large groups or families seeking a memorable Hawaiian getaway. Featuring seven bedrooms plus two loft bedrooms, this property comfortably accommodates up to 20 guests. Located just 250 yards from Brenneckes Beach and only a short walk to Poipu and Shipwreck Beach, your days can be filled with sun, surf, and relaxation.
8 bedroom
7 bathroom
Sleeps 16
Hawaii> Koloa
Discover the comfort and convenience of Island Paradise in Aina Haina, a centrally located condo just a short 5-minute walk to the beach. This fully furnished, air-conditioned midterm rental is perfect for families or professionals seeking a relaxing retreat with easy access to Honolulu's top attractions.Registration Number: GE-057-028-1984-01, TA-057-028-1984-01, 360020580000
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Kona! This spacious townhouse-style condo offers breathtaking ocean views from both floors and is just steps from the ocean. Located in a peaceful complex away from busy streets, it provides a serene retreat while remaining close to the vibrant town center.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Discover this exceptional nine-floor property that combines two beautifully appointed residences, offering nearly 2800+ sq ft of luxurious living space in the heart of Maui's Kaanapali region. Your group will enjoy five elegant bedrooms and bathrooms, two state-of-the-art kitchens, and two expansive lanais with breathtaking views of the Pacific Ocean.
7 bedroom
5 bathroom
Sleeps 14
Hawaii> Lahaina
- Ground floor condo near pools and BBQ grills
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Come and experience the best of Hawaii at this premium Marriott Resort in Oahu. Designed for family fun and relaxation, the property offers luxurious amenities including a lagoon-style pool with jacuzzis, a slide, and grotto area. For peaceful moments, enjoy the reflection pool or unwind on a private sandy beach and lagoon with crystal-clear waters. Registration Number: TA-199-820-7488-01, 910570130000, GE-199-820-7488-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Oahu
, Suite B-13. Office is open daily 9 am - 5 pm.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience the perfect blend of comfort and elegance at Kolea 6E, a spacious Hawaii villa overlooking the beautiful A'Bay. This property features an expansive great room with two plush sofas facing a large LCD TV with sound system, BluRay, and extended cable.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Waikoloa
5-bath Grand Residence at Montage Kapalua Bay, offering stunning ocean views and premium amenities. With over 2,700 square feet of elegant living space, this residence combines comfort and style seamlessly. The master suite and guest master, both featuring king beds, provide serene retreats, while a third bedroom with two queen beds is ideal for families or additional guests.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kapalua
Experience the perfect island getaway in this stylish 37th-floor ocean-view condo at Discovery Bay Waikiki. This inviting 1-bedroom unit features a comfortable queen bed, sleek vinyl flooring, and refreshed furnishings, creating a modern and relaxing ambiance ideal for both short-term vacations and extended stays.Registration Number: TA-025-277-6448-01, GE-025-277-6448-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Kaanapali Shores 736 is a charming Studio condo with central air conditioning, comfortably accommodating up to 4 guests. Perched on the seventh floor, this unit features an open-concept design, creating a peaceful island retreat. After a day of adventure, relax on the luxurious King-size bed or unwind with your favorite shows on the flat-screen TV.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect blend of luxury and comfort at Maluhia at Wainani, a contemporary single-level villa nestled in the peaceful area of Poipu. This beautifully designed home offers approximately 3,400 square feet of living space, featuring three spacious en-suite bedrooms, each equipped with plush king beds, air conditioning, and private lanais that invite the lush Kauai landscape inside.
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Koloa
Kaanapali Alii 192 is a beautifully appointed vacation residence nestled in the upscale resort area of Kaanapali, Maui. Spanning approximately 1,544 square feet, this luxurious one-bedroom plus den, two-bathroom condo offers a spacious and comfortable environment perfect for a relaxing getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this stunning beachfront resort located near Black Rock, offering a perfect blend of relaxation and recreation. Guests can enjoy an outdoor pool and a hot tub, perfect for unwinding after a day of exploring. The resort provides air-conditioned accommodations with spacious balconies, allowing guests to soak in the ocean views and gentle breezes.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to this exquisite split-level retreat at Pili Mai Resort in sunny Poipu, Kauai. This beautifully maintained 3-bedroom residence combines Hawaiian charm with modern luxury, perfect for families and discerning travelers seeking an unforgettable island experience.
4 bedroom
3 bathroom
Sleeps 9
Hawaii> Koloa
Discover the perfect getaway at Marriott Ko Olina Beach Club, nestled along the pristine shores of Oahu. Surrounded by clear blue waters and a picturesque lagoon, this resort offers a tranquil escape just steps from the ocean.Registration Number: GE-070-692-2496-03, 91057013, TA-070-692-2496-03
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to your dream Maui getaway at Kapalua Golf Villa 12 V3, a luxurious top-floor end unit nestled amidst lush landscapes offering breathtaking views of the golf course and sparkling Hawaiian waters. This serene retreat provides the perfect balance of comfort, natural beauty, and convenience, making it an ideal choice for travelers seeking both relaxation and adventure.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to our tropical home, a bright and breezy retreat located just one block from Oahu's most beautiful beach, featuring four miles of soft sand and gentle waves. From the house and patio, you can enjoy breathtaking sunset views over the Ko�olau mountains, creating a perfect backdrop for your vacation. Registration Number: TA-125-245-1840-01, 410070830000, GE-125-245-1840-01
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Waimanalo
Discover this beautifully renovated one-bedroom apartment located near Kapiolani and Date Street in Honolulu. The unit offers a cozy bedroom with a queen bed, a comfortable living room with a sofa bed, and a modern bathroom with a refreshing shower. Registration Number: GE-112-393-0112-01, TA-112-393-0112-01, 270180150000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This charming Laie beach house combines a relaxed island vibe with modern updates, creating a welcoming and comfortable retreat. Fully renovated eight years ago, the home received a fresh mini makeover just two months ago, ensuring a stylish and updated space for your stay.Registration Number: 550090370000, GE-018-462-4128-01, TA-018-462-4128-01
5 bedroom
4 bathroom
Sleeps 13
Hawaii> Laie
Enjoy a comfortable stay in this beautifully renovated 3rd-floor condo located on the southwest end of the building, close to the elevator and parking for easy access. The unit features a private covered balcony and an open sundeck, perfect for relaxing outdoors.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Reserve your exclusive beachfront escape at the luxurious Ko Olina Beach Club Resort in Oahu. This spacious 1-bedroom, 1-bath villa offers ultimate comfort with a king-size master suite, private balcony, and modern amenities. The living area features a pullout sofa, large windows, and a fully furnished balcony perfect for relaxing or entertaining. Registration Number: TA-199-820-7488-01, 910570130000, GE-199-820-7488-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
5-bath single-level upper corner villa offers a luxurious retreat in the heart of Hualalai Resort. With 2,646 sq ft of elegant living space, it comfortably accommodates up to 6 guests. Located on Waiulu Street, one of the resort’s most sought-after neighborhoods, the villa is just five minutes from the resort’s pools and dining options.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
Step into paradise with this charming oceanfront hotel room in Kahului, Maui. Wake up to the soothing sounds of Kahului Bay and enjoy breathtaking sunrise views right from your accommodation. This 588 sq ft suite features a comfortable king bed, a sofa bed, and all the essentials like a mini-fridge, microwave, and coffee maker for a convenient stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahului
The Ridge at Wai'ula'ula L101 offers a luxurious, well-equipped ground-level residence perfect for families or groups seeking a relaxing Hawaiian escape. Spanning 2,588 square feet, this elegant condo features three bedrooms and three and a half bathrooms, providing ample space and comfort for up to 8 guests.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
This is a NEW listing and it's gorgeous! We are confident you will have a great experience.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
Looking for a beachfront retreat in Waikiki? This hotel offers two separate rooms, each with 2 double beds, perfect for family or romantic stays. Located just minutes from popular attractions, the property boasts a variety of amenities including an outdoor swimming pool, on-site water sports, a spa for relaxation, and two restaurants to enjoy delicious meals. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Escape to this tranquil Ke'ena Ma Puna studio nestled in the lush landscape of Keaau, Hawaii. Perfect for couples or solo travelers, this cozy 1-bathroom retreat offers a serene environment with modern amenities and outdoor charm.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Welcome to your ideal Maui vacation spot at Maui Vista Condo #3-408! This beautifully updated condo combines comfort and convenience, making it feel like your home away from home.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Located in Waikoloa Village, this inviting 2-bedroom condo offers a comfortable and relaxing retreat. Guests can unwind in the spacious living room with a cozy sofa bed, perfect for relaxing after a day of adventure. The condo features air conditioning and a washing machine, ensuring convenience during your stay.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover the perfect Maui escape at our charming condo in Kihei Bay Vista unit C-105. Located just 20 minutes from the airport, this cozy one-bedroom offers a comfortable and convenient base to explore the island's top attractions. The condo is ideal for families or couples looking to enjoy Maui's vibrant lifestyle and stunning scenery.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Keauhou Akahi 103
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Bayside Estate offers stunning ocean vistas that truly impress. Large windows bring the picturesque scenery inside, perfect for relaxing with a book or enjoying your favorite shows on the smart TV. The spacious kitchen is fully equipped, making meal prep a breeze for the chef in your group. Registration Number: TA-048-778-0352-01, 450471180000, GE-048-778-0352-01
6 bedroom
6 bathroom
Sleeps 12
Hawaii> Kaneohe
Welcome to Kailua Beach Villa, your ultimate Hawaiian getaway. This luxurious two-level beachfront home offers five bedrooms, four bathrooms, and space for up to 11 guests, making it ideal for families or groups seeking comfort and privacy. Every detail of this property is designed to provide a relaxing tropical retreat, with breathtaking ocean views and direct access to the renowned Kailua Beach. Registration Number: TA-123-605-1968-01, 430180070002, GE-123-605-1968-01
6 bedroom
5 bathroom
Sleeps 11
Hawaii> Kailua
Experience the relaxed beauty of Kihei from this charming condo, just a short 4-minute walk to Kam 1 / Charley Young Beach where turtles bask on golden sands. The property offers a comfortable king bed, a queen sofa bed, and air conditioning in both the living room and bedroom, ensuring a cool and restful stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect blend of spacious luxury and intimate comfort in this beautiful three-bedroom villa on Oahu. Each bedroom features plush beds: two kings and two twins, ideal for larger groups or families. The villa spans approximately 1800 sqft (162 sqm) and offers a full kitchen with modern appliances, a microwave, and a dedicated dining area. Registration Number: TA-005-837-4656-01, GE-005-837-4656-01, 910570140000
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Discover this charming 1-bedroom, 1-bath condo in the heart of Waikiki, offering bright, partial ocean views and a prime location just 350 meters from the famous Waikiki Beach. The unit comfortably sleeps four with two full beds in the bedroom and a full sofa bed in the living area, perfect for families or small groups. Registration Number: TA-149-231-0016-01, GE-149-231-0016-01, 260250050656
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate tropical escape in this luxurious 1-bedroom ocean view suite at Hilton Hawaiian Village Waikiki Beach Resort. Spanning 607 sqft, this elegant suite offers a private balcony with stunning mountain, ocean, or Waikiki views, perfect for relaxing after a day exploring the island.Registration Number: GE-179-756-0320-02, 12600401200, TA-179-756-0320-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu Oahu
Welcome to your perfect Hawaiian retreat at Haleakala Shores! This beautifully furnished condo is ideally located directly across from Kamaole Beach Park 3, renowned for its stunning white sands, perfect for swimming, snorkeling, and boogie boarding.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Escape to Maui with a luxurious stay at the Westin Nanea Ocean Villas in Lahaina. This spacious 2-bedroom resort view villa offers the perfect blend of comfort and convenience for your family vacation. Built in 2017, the units are managed directly by the Westin resort, ensuring top-notch service and maintenance.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the vibrant spirit of Waikiki from this charming hotel room, perfect for travelers seeking comfort and convenience. This unit features two comfortable queen beds and a private balcony offering stunning Waikiki views, ideal for relaxing after a day exploring. Registration Number: TA-143-006-7712-01, GE-143-006-7712-01, 126006002077
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Maui getaway in this stunning 6-bedroom oceanfront residence at Honua Kai Resort, situated in the closest columns to the ocean. This exceptional property combines two neighboring 3BR/3BA residences to create a spacious 4,400+ square foot retreat designed for larger groups or families seeking comfort and luxury.
8 bedroom
6 bathroom
Sleeps 16
Hawaii> Lahaina
Experience the ultimate Maui getaway in this newly remodeled luxury 1-bedroom unit at the prestigious Whaler Kaanapali Beach Resort. Spanning approximately 1118 sq ft, this condo offers breathtaking ocean views visible from the living room, bedroom, and spacious balcony, perfect for enjoying sunrise over the mountains and sunset over the ocean.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
