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 15400, 50
Welcome to The Islands at Mauna Lani Q3, a beautifully furnished two-bedroom, two-and-a-half-bath condo offering an exclusive and luxurious Hawaiian retreat. Located just across from the stunning waterfall pool and entertainment center, this residence is ideal for families or groups of friends seeking comfort, style, and convenience.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
The Studio Premium Villa offers stunning views of wildflowers and majestic mountains on Kauai's North Shore. Each 512-square-foot villa features sliding glass doors leading to a private lanai, perfect for soaking up the sunshine and enjoying the natural beauty surrounding you.
1 bedroom
1 bathroom
Sleeps 1
Hawaii> Princeville
Discover your perfect island escape in this luxurious Ocean View Guestroom at Ko Olina Beach Club, nestled on the beautiful island of Oahu. Spanning approximately 360 sqft (32 sqm), this inviting space offers a cozy yet upscale retreat, ideal for couples or small families seeking comfort and convenience.Registration Number: TA-005-837-4656-01, GE-005-837-4656-01, 910570140000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
Experience breathtaking ocean vistas from this beautifully decorated condo on the Big Island. The spacious lanai offers perfect views of whales and mountain scenery, complemented by soothing breezes and abundant natural light. Inside, enjoy a fully equipped kitchen with high-end appliances and a stylish, calming ambiance.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Escape to the stunning island of Maui and stay at Hilton Vacation Club Kaanapali Beach. This 1-bedroom scenic view unit offers a perfect blend of comfort and luxury, situated on the renowned Kaanapali Beach. The spacious primary bedroom, which can feature a king or two double beds, provides a restful retreat after a day exploring Maui’s natural beauty.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to the serene shores of Maui with Makena Surf F104, a beautifully appointed three-bedroom beachfront condo nestled within the prestigious Makena Surf community. This elegant residence offers a perfect blend of luxury, comfort, and natural beauty, making it an ideal retreat for families, friends, or couples seeking a memorable Maui getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Makena, Maui
Welcome to your perfect beach retreat in Waikiki! This charming Studio Ocean View condo offers breathtaking views of the Pacific Ocean, Waikiki Beach, and the lush palm treetops of Fort DeRussy Park. Step onto the private lanai, furnished with seating for two and a small table, ideal for enjoying sunset cocktails or the Friday night fireworks. Registration Number: 260040120030, GE-129-976-9344-01, TA-129-976-9344-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> HONOLULU
This peaceful mountain hideaway offers a perfect escape into Hawaii’s natural beauty. Situated in a tranquil rural area close to Hawai'i Volcanoes National Park, the private home provides a serene retreat with modern comforts. Enjoy covered lanais with dining and lounging areas, perfect for relaxing or entertaining, alongside a cozy fire pit for cooler evenings.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Volcano
Experience a relaxing stay in these spacious hotel suites located in Kapolei, Hawaii. The property features 2 cozy bedrooms with king beds and a sofa bed, providing ample comfort for families or groups. Two modern bathrooms equipped with bathtubs and hair dryers ensure convenience during your stay. Registration Number: 1910570120000000, TA-051-521-6896-01, GE-051-521-6896-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Experience a comfortable stay in this 2nd floor Hawaii condo with a private balcony. The open kitchen is fully updated with granite counters and modern appliances, including a stove, oven, refrigerator, microwave, coffeemaker, toaster, blender, and tea kettle, along with a complimentary tea starter kit.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautifully appointed 2-bedroom, 2-bath condo situated in the tranquil part of Waikiki, offering a perfect retreat from the city's hustle. The unit features central air-conditioning for your comfort, along with a generous pool area complemented by a relaxing spa, ideal for unwinding after a day of exploring. Registration Number: GE-142-336-2560-01, TA-142-336-2560-01, 260160020032
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Your memorable family vacation or romantic retreat begins here on the sandy shores of Waikiki Beach! This 355 sq ft Pacific Tower ocean view unit offers two queen beds, daily housekeeping, a coffee maker, mini-fridge, and all linens and essentials, ensuring a hassle-free stay. Registration Number: TA-113-154-4576-01, 260040100000, GE-113-154-4576-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the perfect Maui getaway at Honua Kai's Konea 308, a spacious two-bedroom, one-bath suite offering privacy, comfort, and stunning partial ocean views from its large lanai. This rare accommodation combines the cozy feel of a one-bedroom with the added space of a second bedroom, making it ideal for families or groups seeking value without sacrificing comfort.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the peaceful charm of this spacious two-bedroom retreat on the Big Island, designed for comfort and relaxation. Surrounded by lush landscapes and close to beaches, waterfalls, and cultural sites, this is the perfect place to start your Hawaiian adventure.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Pepeekeo
Enjoy a prime location in Waikiki at this charming studio, ideal for couples or solo adventurers. Just a 5-minute walk to the iconic Waikiki Beach, the property is surrounded by vibrant shopping, dining, and entertainment options. Designed for convenience, the studio features a comfortable queen-size bed, a flat-screen TV with cable, reliable Wi-Fi, and a small dining area. Registration Number: GE-158-364-3648-01, TA-158-364-3648-01, 1260230450012
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
S. Environmental Protection Agency.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville, Kauai
This charming 2-bedroom villa in Lahaina provides a perfect retreat on Maui's stunning coast. The property features a comfortable king bed in the master, a queen bed in the second bedroom, and a cozy living area with a sofa bed, accommodating families and groups comfortably.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience island living in this charming oceanfront Maui condo located on the west side of the island. The property offers a peaceful retreat away from large resorts, providing guests with a more intimate and authentic Hawaiian experience. The recently renovated and beautifully furnished unit spans the third and fourth floors, offering breathtaking views of the ocean, Molokai, and Lanai from two spacious lanais.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
This stunning ocean-view cottage is perfectly situated just steps from the renowned Waimanalo Beach, rated as the #1 beach in the USA in 2015. Nestled in a peaceful Hawaiian village, you'll wake up to the melodious sounds of native birds, the sweet fragrance of tropical flowers, and the gentle rhythm of waves lapping on the shore. Registration Number: TA-106-722-5088-01, 410060570000, GE-106-722-5088-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
Experience the authentic charm of this beautifully renovated Kauai country cottage, nestled in a native Hawaiian farm homestead. Guests are invited to pick ripe fruits like avocados, mangoes, passion fruit, and coconuts directly from the farm, adding a fresh touch to your stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Anahola
Experience the lush beauty of Marriott’s Ko Olina Beach Club nestled on Oahu's western coast, where waterfalls and fountains welcome you into a tropical paradise. The resort's expansive grounds feature seven stunning lagoons, swaying palm trees, and vibrant native flora, creating a serene environment perfect for relaxation and adventure. Registration Number: 494386936432, TA-151-645-6123-02, GE-205-657-9211-03
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Kealia Resort #608 is a new unit to our program located on a oceanfront complex in North Kihei. This unit has a full kitchen, and washer / dryer. Complimentary Wi-Fi is provided. The bedroom is equipped with a ceiling fan, and a queen bed. The room is connected to an en suite bathroom with a walk-in shower.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover Oceanfront Bliss at Marriott’s Maui Ocean Club – Lahaina & Napili Towers
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Whispering Palms, a luxurious and spacious four-bedroom vacation home nestled in the serene Kiahuna Golf Village of Poipu, Kauai. Perfect for families, friends, or multi-generational groups, this retreat offers a blend of tranquility, elegance, and modern comforts.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Welcome to this charming ground floor retreat in West Maui, offering easy access with parking right out front. The property features a refreshing pool, an inviting barbecue grill area, and lush landscaping that creates a tropical oasis ambiance.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
Mana Kai 714 is a spacious 2-bedroom, 2-bathroom condo that comfortably sleeps up to six guests. Both bedrooms are furnished with a luxurious king-sized bed. For additional sleeping space, there's a queen sofa sleeper in the living area. A split A/C system and ceiling fans throughout ensure a cool and relaxing stay.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This condo, situated on the 9th floor of the Ilikai Hotel, offers a perfect blend of comfort and scenic beauty. The cooler side of the building provides morning sun and breathtaking views of the Hilton Lagoon and the Pacific Ocean. Step out onto a large private lanai, ideal for relaxing while enjoying the view and watching the weekly Friday night fireworks. Registration Number: TA-135-329-3824-02, 260100070163, GE-135-329-3824-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the ultimate beachfront escape in Waikiki with this spacious hotel room offering stunning ocean views and convenient amenities. Situated just minutes from popular attractions, this property provides the perfect setting for a memorable vacation or romantic retreat.Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience the best of Honua Kai Resort & Spa with this unique offering—two separate condos that are perfect for groups traveling together who desire proximity and privacy. Hokulani 612, a 2-bedroom, 2-bath condo located on the 6th floor, accommodates up to 6 guests, while Hokulani 414, a 1-bedroom, 1-bath condo on the 4th floor, sleeps up to 4 guests.
3 bedroom
3 bathroom
Sleeps 10
Hawaii> kaanapali
Discover the perfect stay in Waikiki with our 1BR ocean view hotel rooms, offering stunning views and luxurious amenities. Each unit, approximately 532 sq ft, features a comfortable king bed, daily housekeeping, a coffee maker, mini-fridge, and all linens and essentials provided. Registration Number: GE-107-900-1088-01, TA-107-900-1088-01
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Discover this charming 1-bedroom, 1-bath condo in the heart of Honolulu, blending beachside relaxation with city adventure. The property offers a private lanai with sweeping mountain views, perfect for unwinding after a day exploring the island. Registration Number: 260070060021, GE-084-133-4784-01, TA-084-133-4784-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Hawaiian escape at Waikiki Shore, the only beachfront condominium directly on Waikiki Beach. This fully renovated condo offers breathtaking ocean views and direct access to the white sands and turquoise waters of the Pacific. Registration Number: STR # 2553, 260040120050, TA-019-302-1440-01, GE-019-302-1440-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This beautifully furnished condo in Maalaea offers a perfect retreat with stunning ocean views and easy access to local attractions. Situated just a short drive from the airport, Island Sands 110 serves as an ideal base for exploring Maui's diverse offerings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Maalaea
Experience breathtaking ocean vistas from our beautifully upgraded Ocean View Suite nestled in the vibrant heart of Downtown Honolulu. This spacious 450 sq ft retreat offers a perfect blend of comfort and style, with panoramic views of the ocean, harbor, and city skyline. Registration Number: TA-119-862-2720-01, GE-119-862-2720-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Discover this charming 3-bedroom, 2-bath home nestled in the heart of Oahu, offering a peaceful retreat with easy access to the island’s top attractions. The spacious, modern living area provides a comfortable space to unwind after a day of adventure. Registration Number: GE-140-425-7792-01, TA-140-425-7792-01, 740150180000
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Wahiawa
Tastefully renovated, this spacious condo offers vaulted ceilings and a fully equipped chef's kitchen, perfect for preparing memorable meals. The large bar and coffee station create a welcoming atmosphere for gatherings. The living area opens seamlessly to an expansive covered lanai, where you can unwind while taking in breathtaking ocean and golf course views—ideal for watching stunning sunsets over the water each evening.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Discover the charm of The Westin Nanea Ocean Villas, a luxurious retreat nestled on twenty-six acres of lush tropical landscape in Hawaii. This upscale resort seamlessly combines modern comfort with authentic Hawaiian design elements, offering guests a true island experience.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Discover the epitome of island luxury at our newly listed 2-bedroom oceanfront villa in the exclusive Hali'i Kai community at Waikoloa Beach Resort. Nestled on the sun-drenched shores of the Kohala Coast, this exquisite property offers breathtaking views of the Pacific Ocean, Maui Island, and the vibrant Kohala sunsets.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Beach Resort
Welcome to Villa Bianca by Gather, a luxurious four-bedroom, four-bathroom retreat in the scenic town of Haleiwa, Hawaii, just minutes from Waimea Bay. Designed to host up to eight guests, this elegant home combines comfort, style, and breathtaking ocean vistas, providing an ideal setting for families or groups seeking a tranquil Hawaiian escape.Registration Number: TA-008-330-5472-01, 610030740000, GE-008-330-5472-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Haleiwa
Experience the vibrant heart of Honolulu from this chic 1-bedroom condo located on the 12th floor of the Aston Executive Centre. The unit boasts expansive floor-to-ceiling windows that frame stunning city views, creating a bright and inviting atmosphere. Registration Number: GE-150-367-5904-01, TA-150-367-5904-01, 1210120040128
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This private studio in Waikiki offers a comfortable and convenient stay for solo travelers, couples, or friends. Located in the east area of Waikiki, the condo is just a 3-minute walk to the beautiful beach, perfect for enjoying sunrise or sunset walks amidst nature. Registration Number: 260270200013, TA-099-018-1888-01, GE-099-018-1888-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Bamboo Temple offers a serene, private escape on Maui’s North Shore, perfect for honeymooners and couples seeking tranquility and natural beauty. Wake up to stunning sunrises over the Pacific and fall asleep under a star-filled sky visible through a unique pyramidal skylight.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maui County
Welcome to Kihei Kai Condo #20, a charming lower-level unit offering serene pool and garden views. This tastefully decorated and meticulously maintained condo provides the perfect retreat for your Maui holiday, blending comfort with convenience in a peaceful setting.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the charm of Waikiki with this inviting Castle at Waikiki Grand Unit 722. Spanning 384 square feet, this retreat offers a thoughtful layout that separates the kitchen, living, and sleeping areas for comfort and convenience. Floor-to-ceiling windows flood the space with natural light, while tile floors and air conditioning keep the environment cool after a day of sun-soaked adventures. Registration Number: TA-049-014-7840-01, GE-049-014-7840-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> HONOLULU
Experience the ultimate Waikiki retreat in this fully equipped condo, ideal for stays of a month or longer. The unit features a comfortable queen-sized bed, sofa, and a window-type A/C system to keep you cool during warm Hawaiian days. Stay connected with free WiFi throughout the property, and enjoy the convenience of an in-unit washer/dryer, dishwasher, and a full kitchen stocked with utensils, condiments, and appliances like a coffee maker and microwave. Registration Number: 260210490156, TA-138-666-8032-01, GE-138-666-8032-02
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to this charming Garden View 2-Bedroom, 2-Bath Villa offering a comfortable and fully equipped retreat for up to 6 guests. The spacious layout spans between 1194 and 1299 square feet (111-121 sqm), making it ideal for families or groups seeking a relaxing getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Experience a unique stay in our family-friendly hotel rooms located near Hawaii Volcanoes National Park. Each 250 sqft unit offers two double beds, daily housekeeping, and essential amenities, ensuring a comfortable visit. The property features on-site dining, free bikes for exploring, 24/7 front desk and security, and free parking for one vehicle per unit.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Pahoa
This beautifully remodeled one-bedroom condo in South Kihei offers a perfect blend of modern comfort and island charm. Located in the vibrant heart of the neighborhood, it’s just steps from Maui’s stunning beaches, local cafes, shops, and cultural sites.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
5-bath condo in Wailea Ekahi Village, offering stunning ocean views across the lush greenbelt. This high-end residence combines comfort and style, perfect for a memorable Maui vacation.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
Welcome to Hale Ho'ola, a stunning 2-bedroom, 2-bathroom sanctuary designed for your Hawaiian escape. The open floor plan creates a bright, airy atmosphere, perfect for relaxing and entertaining. Step onto the spacious lanai to enjoy refreshing ocean breezes and lush green views, ideal for morning coffees or evening sunsets.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Pahoa
