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 16050, 50
Escape to paradise at Kaanapali Royal B201, a luxurious two-bedroom, two-bath condo nestled in the heart of Maui. Wake up to the gentle sounds of swaying palms and tropical fragrances, and enjoy a perfect blend of comfort, style, and natural beauty.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate beachfront retreat in this stunning villa located closest to Oneloa Beach. Spanning 2,200 square feet, this first-floor residence offers direct ocean views and easy access with just 16 stairs from parking to the front door.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the beauty of the Big Island from this beautifully remodeled ground-floor condo at Kanaloa at Kona. This two-bedroom, two-bathroom unit offers stunning oceanfront views and a tranquil, residential setting. The fully equipped kitchen makes it easy to prepare meals, while three TVs and ceiling fans throughout provide comfort and entertainment.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua Kona
Experience a luxurious stay in this highly desirable, beautifully renovated high-floor condo just 900 feet from the famous Waikiki Beach. Boasting stunning ocean and sunset views, this 600 sq. ft. condo offers a perfect blend of comfort and convenience for your Hawaiian getaway.Registration Number: TA-157-990-7072-01, 260250050556, 2210-CCH-0045, GE-157-990-7072-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hale Konane by Gather, a luxurious five-bedroom, five-bathroom retreat nestled within the exclusive Mauna Lani Resort on the Big Island. This custom-designed home offers an unforgettable Hawaiian escape with stunning ocean and golf course views, expansive indoor-outdoor living spaces, and a private backyard oasis perfect for relaxation and entertainment.
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Mauna Lani
Hale Kailea, Hanalei - Now offered through Makana Kauai Properties.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Hanalei
Welcome to Banyan Harbor I-101, your ideal Hawaiian retreat in Lihue, HI! This beautifully renovated condo features breathtaking ocean and mountain views from three private lanais, perfect for relaxing mornings or sunset cocktails. Each room is air-conditioned, ensuring comfort during your stay.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Lihue
Escape to this charming and peaceful ground floor condo in picturesque Kihei. 3 mile) to Charley Young Beach, making it an ideal retreat for individuals or couples seeking relaxation. The fully equipped new kitchen allows for convenient self-catering, while split A/C ensures comfort during warm days.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Discover Waikiki's vibrant charm from our beachfront hotel room, just steps from Kuhio Beach and Diamond Head. Wake up to breathtaking ocean views and immerse yourself in Hawaiian adventures. Each room offers thoughtful amenities like a mini-fridge and Keurig coffee maker, ensuring a comfortable stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Sea Glass by Gather, a beautifully renovated three-bedroom, three-bathroom residence nestled in the exclusive Villas of Kamaliʻi community in Princeville, HI. Just steps from the Princeville Makai golf course, this pristine home offers a luxurious and comfortable retreat for up to six guests.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Discover this beautifully updated 5th-floor condo located on the south side of the building, offering a private balcony with scenic views. The spacious 431 sq ft layout includes a comfortable king bed and a twin sofa bed, ideal for up to three guests including children and infants.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience a relaxing stay in this first-floor walk-out condo offering beautiful garden views and a bright, tropical ambiance. The spacious unit features luxury vinyl plank flooring throughout, creating a modern and inviting atmosphere. The full kitchen is equipped with quartz countertops, a full-size refrigerator, stovetop/oven, dishwasher, microwave hood combo, coffee maker, blender, toaster, cookware, and tableware, making meal prep effortless.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to Kalani at Haiku Garden Cottages.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Haiku-Pauwela
Escape to Maui Vista 3307 — your perfect island getaway! This beautifully maintained one-bedroom, one-bath condo offers peaceful privacy and gorgeous garden views from your private lanai. Located on the third floor of Building 3 with elevator access, it’s just a short 5-minute walk to the white sands of Charlie Young Beach and Kamaole I, II, and III.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Imagine waking up to the gentle sound of waves and stepping onto your lanai to take in the vast, sparkling ocean stretching out before you. With a fresh cup of coffee or tea in hand, the morning breeze carries a sense of tranquility as you watch the sun rise over the horizon.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
Enjoy a comfortable stay in this spacious 1,141 sqft condo located on the first floor of a Hawaii resort. The unit features tile flooring throughout and vinyl plank flooring in each bedroom, creating a warm and inviting atmosphere. The open kitchen is fully equipped with a fridge, stove/oven, microwave, cookware, and tableware, making meal preparation convenient.
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui getaway at Wailea Elua #2107, a beautifully upgraded two-bedroom, two-bathroom oceanfront condo that combines luxury, comfort, and breathtaking views.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea, Maui
Experience a taste of paradise in this beautifully decorated Maui Banyan T108A condo. The unit features tasteful artwork, vibrant floral patterns, and rustic island colors that create a warm, inviting atmosphere. Wide-open living spaces are accentuated by sleek hardwood floors and oversized windows facing the lush garden, flooding the space with natural light.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this beautifully remodeled 1-bedroom, 1-bath condo nestled in the heart of Princeville. The unit offers a spacious living room, a full kitchen with brand new appliances, and a large lanai overlooking the picturesque Princeville golf course and mountains.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Experience the perfect Maui getaway at Hale Mahina B205, a charming 1-bedroom, 1-bath condo offering direct ocean views and lush landscaped grounds. This well-appointed unit is ideal for up to 4 guests seeking a relaxing beachside retreat without air conditioning but with ceiling fans for comfort.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui vacation in this stunning Kihei beachfront condo. Located just steps from the world’s best beaches, this 5-star rated property offers unparalleled convenience and comfort. Walk to nearby restaurants, shops, and enjoy easy access to the surf and snorkeling right across the street.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the perfect Maui retreat at Napili Point B26, a charming 1-bedroom, 1-bathroom condo overlooking the stunning Napili Bay and the Pacific Ocean. This beautifully appointed condo combines comfort and luxury, offering breathtaking views and modern amenities for a memorable island escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Napili-Honokowai
Discover this inviting Wailupe home perched on the mountain side, offering stunning ocean vistas just five blocks away. The property features two bedrooms within a spacious, open great room layout, perfect for family or friends traveling together.Registration Number: GE-302-728-1228-01, TA-221-361-9920-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
Experience an exceptional stay at Honua Kai with this combined offering of three neighboring suites, totaling over 2500 sq ft of luxurious living space. Designed for large families or groups, these suites seamlessly blend comfort and style, providing breathtaking views of the West Maui Mountains from expansive lanais and partial ocean vistas from two suites.
7 bedroom
3 bathroom
Sleeps 16
Hawaii> Lahaina
vrKCV - Alii Villas 329, Professionally Managed by Kona Coast Vacations
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to your ideal Maui retreat! This charming 2-bedroom loft apartment in Kihei offers a perfect blend of comfort and convenience. The spacious unit features a cozy queen bed and a king bed, ideal for families or couples seeking a relaxing island escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to the serene beauty of Princeville with a stay in these spacious suites at Club Wyndham Makai Club Cottages. Nestled along the first fairway of the renowned Makai Golf Course designed by Trent Jones, the cottages offer breathtaking views and a peaceful atmosphere.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Princeville
Welcome to your ideal Maui retreat at Kihei Kai Nani 8-363, where elegant Aloha Chic design complements breathtaking sunset, sunrise, and mountain vistas. This thoughtfully decorated 1-bedroom condo is situated at the back of the property, providing a tranquil escape away from the main road noise.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Looking for a beachfront getaway in Waikiki? This property offers three separate hotel rooms, each uniquely decorated and approximately 360 sqft with stunning ocean views. Just minutes from Waikiki's most popular attractions, the location is perfect for exploring the vibrant island life.Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Discover a hidden gem on the west side of Oahu, where a beautifully renovated plantation-style home offers an authentic Hawaiian experience away from tourism. Nestled on a private, gated lane with key code access, this cozy beach house provides exclusive access to a secluded beachfront—no tourists, just you and the natural beauty of Hawaii's raw aloha spirit.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> EWA BEACH
Discover this beautifully renovated studio in the heart of Waikiki, perfect for a relaxing getaway. The space features vibrant decor, a comfortable memory foam queen bed, and a lanai offering a peekaboo view of the ocean. The fully equipped kitchenette includes basic pans and utensils, ideal for preparing your favorite meals. Registration Number: 1260230500060, TA-155-307-7760-01, GE-155-307-7760-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience comfort and convenience in this spacious 898 sqft condo offering stunning courtyard views. The one-bedroom features a plush king bed and an en-suite bathroom with a tub and separate shower. The living area includes a queen sofa bed, perfect for up to four guests, including children and infants.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
- 4th floor 2 bedroom ocean front condo
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the perfect Maui getaway at Haleakala Shores A-309, a spacious two-bedroom condo ideal for two couples or small families. Located just a block from the stunning Kamaole Beach III, this condo offers breathtaking views of lush gardens and distant mountains.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a stylish stay in this beautifully updated 1-bedroom ocean view condo on the 9th floor of Discovery Bay in Waikiki. Featuring a fully equipped kitchen with modern appliances, including a dishwasher, built-in microwave, and new refrigerator and range, this condo offers comfort and convenience. Registration Number: 1260120100010, GE-181-866-6496-01, TA-181-866-6496-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Makani A Kai Unit A8, your ideal Hawaiian retreat nestled on the peaceful shores of Maalaea Bay in Maui. This charming ground-floor condo features one bedroom and one bathroom, providing a cozy and serene escape perfect for couples or small families seeking a memorable island vacation.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Wailuku
Welcome to Wailea Elua Village #2108, a beautifully remodeled and decorated two-bedroom, two-bath residence offering over 1,500 square feet of luxurious living space. Situated on the second floor, this condo provides breathtaking, elevated views of the ocean, off-shore islets, and lush tropical gardens visible from every room.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Experience the ultimate tropical escape in this spacious 11th floor one-bedroom condo located on the stunning Kaanapali Beach. Wake up to breathtaking ocean views from the comfort of your king-size bed, and unwind on your private balcony overlooking the turquoise waters.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
The Ohana House, established by the Lenett family in 1998, offers a unique Hawaiian experience on Big Island. Designed with influences from Balinese, Japanese, and native Hawaiian architecture, this home provides a truly authentic and luxurious retreat.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Experience the comfort and convenience of this spacious 2-bedroom, 2-bath suite at Waikoloa Beach Resort on the Big Island. Spanning 1,283 sqft, this suite is perfect for families or groups seeking a relaxing getaway. The primary bedroom features a king-size bed and an en-suite bathroom with a shower/tub combo, while the second bedroom offers two double beds and its own bathroom.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover the vibrant charm of Waikiki from our beachfront hotel room, ideally located just steps from Kuhio Beach and iconic Diamond Head. Wake up to stunning ocean views from your private lanai and immerse yourself in Hawaiian adventures. Designed with comfort in mind, each 338 sq ft unit features two queen beds, a private lanai, mini-fridge, Keurig coffee maker, free bottled water, television, and all essentials for a relaxing stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this charming condo located in the heart of the Ala Moana corridor, just one block from the beach. Perfectly situated within walking distance to Waikiki Marina, Magic Island, Hilton Lagoon, and the popular Ala Moana shopping mall. Wake up to breathtaking sunset views and enjoy the spectacular Friday night fireworks from your balcony. Registration Number: GE-196-421-7856-01, TA-196-421-7856-01, 260110080069
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover Kaua Corner Haven, a charming mid-century modern apartment just steps from the beach. This bright and airy retreat combines comfort with convenience, making it an ideal island escape. The space is thoughtfully designed to provide a relaxing atmosphere, perfect for unwinding after exploring Maui's beautiful coast.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Paia
Experience the comfort of this spacious Garden View 2 Bedroom 3 Bath condo, offering a full kitchen with granite countertops, Wolf cooktop, convection oven, microwave, and high-end appliances. The master bedroom features a king bed and an en suite bathroom with a bathtub and separate shower, while the second bedroom can be configured with a queen bed, two doubles, or a double bed with an en suite.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Welcome to Koa Resort 1B, a beautifully renovated ground-floor condo in sunny Kihei, Hawaii. This charming unit is perfect for up to four guests, blending modern comforts with tropical charm.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your perfect Hawaiian retreat at Kiahuna Plantation on Kauai's sunny south shore. This top-floor, 1-bedroom condo offers stunning ocean views and a serene, relaxing atmosphere set on the historic grounds of Hawaii's first sugar plantation.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the perfect Maui getaway in this charming 1-bedroom condo located in Kahana Manor. The space features a cozy queen bed in the bedroom and a queen sleeper sofa in the living room, making it ideal for couples or small families. A pack-n-play is available for toddlers, ensuring our youngest guests are comfortable.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Kahana Villa F208, a beautifully updated 2-bedroom, 2-bathroom condo ideally located just across the street from the beach in Maui's scenic West Maui. This spacious and peaceful unit offers a relaxing retreat with modern touches and convenient amenities.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Napili-Honokowai
The Haleaha Road Home in Hau'ula offers a spacious, beautifully renovated retreat on Oahu’s northeast shore. Designed to comfortably sleep up to 8 guests, this home features two king bedrooms, twin bunks, and a full futon, making it ideal for families or larger groups. Registration Number: GE-076-562-9952-01, 53006057, TA-076-562-9952-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Haleiwa
Maui Kaanapali Villas #C263 offers a luxurious and comfortable retreat on N. Kaanapali Beach. This one-bedroom, two-bath loft features a garden view and access to resort amenities including pools, hot tub, and beach access.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Lahaina
