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 14550, 50
Welcome to your perfect Maui getaway at Pikake Unit B5, a beautifully maintained beachfront condo in an exclusive complex with only 12 units. The home has been thoughtfully decorated with attention to detail, offering a blend of comfort and luxury for a memorable vacation.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your cozy getaway in Hawaii! This inviting studio offers a bright, airy living space designed for comfort and convenience, making it an ideal base for your adventures. Inside, you'll find a comfortable king bed with high-quality linens, ensuring a restful night's sleep.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hilo
Experience the perfect Hawaiian getaway in this beautiful 2-bedroom, 2-bathroom condo located in the heart of Waikiki. The spacious 1180 square feet layout offers a comfortable and stylish retreat, complemented by a large 124 square foot ocean view lanai where you can unwind and take in breathtaking sunsets.Registration Number: TA-088-354-4064-01, GE-088-354-4064-03, 260120020080
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Immerse yourself in the ultimate Maui vacation at the Sands of Kahana, a stunning oceanfront resort located on the pristine sands of Kahana Bay. This spacious 1 bedroom, 2 bathroom condo offers a perfect blend of comfort and natural beauty, making it an ideal choice for families, couples, or solo travelers seeking a memorable Hawaii getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Hale Malu, which means House of Peace in Hawaiian, a rare kamaaina-owned dual home and rental located in the upscale Ka Milo at Mauna Lani Resort. Perfect for those seeking a peaceful and authentic Hawaiian getaway, this fully upgraded 3-bedroom cottage offers a serene retreat in the heart of paradise.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Experience the perfect getaway at this charming villa located closest to Oneloa Beach. The split-level design offers easy access from parking with no stairs, making your stay comfortable and hassle-free. Inside, the villa features tile flooring in the kitchen, living room, and bathroom, complemented by plush carpeting in the bedroom for added coziness.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This exquisite 4-bedroom, 4-bathroom home in Hanalei offers a seamless blend of luxury and serenity. Located just a short stroll from the pristine sands of Hanalei Bay, this property provides easy access to local restaurants, charming coffee shops, and boutique stores, making it an ideal base for exploring the area.
4 bedroom
4 bathroom
Sleeps 9
Hawaii> Hanalei
Experience a stylish and contemporary stay in this 6th-floor condo at Ilikai Marina, offering stunning views of the ocean and yacht harbor. Wake up to calming ocean vistas and unwind watching the sunset from your lanai, with Friday night fireworks visible from your private space. Registration Number: GE-049-506-8160-01, TA-049-506-8160-01, 1260100020044
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Waikiki Banyan Tower 1 Suite 1408, a charming and airy condo ideally located just two blocks from the world-famous Waikiki Beach. This 14th-floor unit offers stunning views of the city and ocean, providing a perfect backdrop for your Hawaiian vacation.Registration Number: TA-051-731-8656-01, GE-051-731-8656-01, 260250050102
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the perfect Maui getaway in this beautifully decorated 2-bedroom, 2-bathroom condo at Kaanapali Alii, offering partial ocean views and a spacious, open floor plan. The residence boasts a large living area seamlessly connected to the dining space and a fully equipped kitchen with stainless steel appliances, granite countertops, and stylish wood cabinetry, creating a charming yet sophisticated atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your tropical retreat at Wailea Ekahi Village 47B—an ideal vacation escape for families or a group of friends. This beautifully appointed ground-floor residence offers the perfect blend of comfort, space, and convenience. With serene garden views and easy access to all of Wailea’s world-class amenities, it’s a home base that lets you fully unwind and enjoy Maui.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to your Hawaiian retreat! This charming boutique Corner Studio is situated on the 15th floor of the Aloha Surf Hotel, offering a peaceful, convenient location just a short walk from the beach. , this well-appointed space provides delightful ocean, sunset, and mountain views through two large glass windows, creating an inviting tropical ambiance. Registration Number: 260210160194, TA-161-954-0480-01, GE-161-954-0488-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Escape to the serene oceanfront Mahana Villa 1006 on Maui's stunning west side, offering breathtaking views of Molokai and Lanai from the living area, bedroom, and private lanai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Experience the ultimate Hawaiian escape at Kings' Land Resort, situated amidst stunning oceanfront golf courses designed by Robert Trent Jones, Jr. Our spacious 2-bedroom, 2-bathroom suites offer 950 sqft of comfort, with some units providing garden or resort views.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover this charming two-bedroom condo in the highly sought-after "43" stack of the Ilikai Hotel, perched on the 7th floor with breathtaking views of the lagoon and yacht harbor. From your private lanai, enjoy front-row seats to the spectacular Friday night fireworks, making your stay truly memorable.Registration Number: GE-056-009-7280-01, TA-056-009-7280-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hip Hawaiian Oceanfront Vistas, a beautifully remodeled one-bedroom, one-bath condo at Kauhale Makai in Kihei, Maui. Completed in August 2024, this modern retreat offers a perfect blend of comfort and style with luxurious touches throughout.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Feel the island’s Aloha in this cozy and stylish Waikiki studio! Located just steps from the world-famous Waikiki Beach, this fully furnished space offers comfort and convenience for your Hawaiian getaway. The studio features a queen-sized memory foam bed and a full-size sofa bed, providing ample space for relaxation after a day in the sun or exploring the vibrant neighborhood. Registration Number: TA-151-260-2112-01, GE-151-260-2112-01, 1260230500053
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the perfect Hawaiian escape in this spacious and beautifully appointed 1-bedroom suite nestled between the lush West Maui Mountains and the sparkling Pacific Ocean. This modern condo combines comfort and tropical charm, offering a fully equipped gourmet kitchen, a furnished lanai, in-room washer and dryer, and luxurious bedrooms and living areas.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This charming hotel room in Waikiki provides a perfect blend of comfort and convenience for your island getaway.Registration Number: TA-180-111-3600-01, 260060020054, GE-180-111-3600-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the ultimate Maui escape at Kapalua Golf Villa 22P2, a luxurious remodeled 2-bedroom, 2-bathroom retreat offering breathtaking panoramic ocean views from every room. The villa has been thoughtfully updated with all-new appliances and elegant RH furniture, creating a perfect blend of style and comfort.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapalua
Discover serenity and convenience in this 1-bedroom condo at Kahana Falls Resort. Located just steps from Maui's picturesque Kahana Beach, this unit is ideal for couples or solo travelers seeking a restful escape. Enjoy a queen bed, fully equipped kitchen, modern bathroom, and a relaxing living area with TV and free Wi-Fi.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover this comfortable ground floor condo in Hawaii, offering a spacious 473 square foot lanai perfect for outdoor lounging. The unit features a fully equipped kitchen with dishwasher, stove top/oven, microwave, refrigerator, coffeemaker, blender, cookware, and tableware, making it ideal for home-cooked meals.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Kolea 7F offers an exceptional Hawaiian vacation experience in one of the most sought-after buildings at Kolea. This spacious villa features breathtaking ocean views and convenient access to the Kolea Beach Club and the pristine white sands of A'Bay.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience the perfect Hawaiian getaway at this 4-star boutique hotel situated at the entrance to Waikiki's world-famous strip. Whether you're seeking relaxation or adventure, this hotel offers an ideal base for exploring all that Hawaii has to offer.Registration Number: GE-173-278-3104-01, TA-173-278-3104-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience breathtaking oceanfront living in this beautifully furnished top-floor condo in Kahana. Gaze upon the stunning island of Molokai and the expansive blue Pacific from your private balcony, perfect for relaxing and soaking in the scenery.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahana
Feel emersed in the Hawaiian culture in the Hawaiian Kingdom Hula Room. It is located on the top floor with a King bed, it's a large room with a sliding door which provides access to back lanai with waterfall views, Bathroom is dedicated but is also the bathroom for the living area on the top floor, not an en-suite.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hilo
The studio offers a welcoming open-concept layout with a charming island-inspired design. Large sliding glass doors flood the space with natural light and open to a furnished lanai, ideal for morning coffee or evening cocktails.Registration Number: GE-079-079-6288-01, 260230670056, TA-079-079-6288-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Welcome to Keauhou Kona Surf & Racquet 188 by Gather, a fully renovated two-bedroom, two-bathroom, Air conditioned oceanfront townhome in Kailua-Kona, Hawai'i. This two-story duplex accommodates up to six guests and offers panoramic 180° ocean views from nearly every room.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Enjoy comfort and convenience in this cozy studio at Kahana Falls Resort, just moments from the beach. Designed for romantic getaways or solo retreats, the unit features a queen Murphy bed, full kitchen, balcony, and modern bath. With an in-unit washer/dryer, smart TV, and access to resort pools, hot tubs, and BBQs, it's a perfect spot for relaxation and adventure in Lahaina.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to your ideal Big Island retreat in the picturesque Kona Palisades neighborhood. This listing features two separate, elegantly renovated studios, each designed with comfort and quality in mind, just steps from a refreshing saltwater pool.
2 bedroom
1 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Discover this beautifully appointed 11th-floor condo in Hawaii, offering a spacious 777 sqft layout with a private balcony to enjoy ocean views. The condo features tile and vinyl plank flooring throughout, providing a modern and easy-to-maintain living space.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this charming second-floor condo with stunning lanai views of Poipu Beach and the ocean. Bright and airy, the space features tropical beach decor and original artwork, creating a relaxed island atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover the comfort of Golf Villas situated along Kapalua's Bay Course fairway, offering partial ocean views and spacious living. Each villa features a fully equipped kitchen with stovetop, oven, microwave, refrigerator, coffeemaker, cookware, and tableware, perfect for preparing meals during your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover Hale Kai, a stunning fully furnished beachfront home nestled on the famous Ke Nui Road in Haleiwa, Hawaii. This two-story residence offers the perfect blend of comfort, convenience, and surf paradise. The main level features a spacious gathering area, one bedroom, and one and a half bathrooms, ideal for relaxing after a day on the waves. Registration Number: TA-149-360-7936-01, 590190470000, GE-149-360-7936-01
6 bedroom
5 bathroom
Sleeps 10
Hawaii> Haleiwa
Experience the ultimate in luxury and relaxation at the Westin Kaanapali Ocean Resort Villas (WKORV), set across twenty-six lush acres with stunning views of the Pacific Ocean and neighboring islands Lana'i and Moloka'i. This resort is a perfect destination for families, couples, or anyone seeking a memorable Maui getaway.
1 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to Leaf House by Gather, an exquisite five-bedroom, four-and-a-half-bathroom luxury retreat nestled in the picturesque neighborhood of Lanikai, Kailua, Hawaii. This custom-designed, three-story home offers an unmatched combination of sophistication, privacy, and island charm, making it an ideal destination for families or groups of up to ten guests seeking a memorable Hawaiian escape.Registration Number: TA-032-430-3360-01, GE-032-430-3360-01, 430020420000
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Kailua
Discover this beautifully remodeled ground-floor 1-bedroom, 1-bath condo nestled in the lush Kihei Resort, directly across from Maui’s stunning beaches. The condo offers a perfect blend of comfort and convenience, ideal for a peaceful tropical escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the best of Waikiki with three individually unique hotel rooms, each offering 355 sq ft of comfort and city views. Enjoy amenities like daily housekeeping, coffee maker, mini-fridge, and all linens provided, making your stay hassle-free.Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
This stunning single-level gated estate offers breathtaking ocean views and luxurious island living. The spacious sunken great room features sliding glass pocket doors that seamlessly connect indoor and outdoor spaces, perfect for relaxing or entertaining.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kamuela
Escape to paradise in Maui Kamaole M-101, a beautifully maintained condo perfect for a relaxing vacation. Located on the ground floor, this unit offers easy access to the pool and is just steps from the breathtaking Kamaole III Beach. The condo features two peaceful garden lanais, including a fully private one, providing a secluded retreat for couples or families alike.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover this spacious 600 sqft one-bedroom suite at Hilton Hawaiian Village in Waikiki, offering stunning city and lagoon views. The suite features a king-size bed, full bathroom with TOTO washlet, and a large living area with cable TV, PlayStation 3, and a queen sleeper sofa. Registration Number: TA-121-575-4240-01, GE-121-575-4240-01, 15206900
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu Oahu
Experience the perfect Hawaiian escape in this spacious and beautifully designed 1-bedroom ocean view suite located within a premier Maui beachfront resort. Spanning approximately 700 square feet, this unit offers a blend of modern comfort and tropical charm, making it an ideal retreat for families, couples, or solo travelers seeking relaxation and adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this charming Kailua-Kona condo, a perfect retreat for your Hawaiian vacation. This cozy unit offers a comfortable bedroom with a relaxing bed and a living area with a sofa bed, accommodating additional guests comfortably. Modern conveniences include a washing machine, air conditioning, and WiFi, ensuring a convenient and enjoyable stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover a cozy and convenient stay in Waikiki with this 300-square-foot hotel room, perfect for your Hawaiian getaway. The unit features two double beds, making it ideal for families or friends traveling together. Amenities include daily housekeeping, a coffee maker, mini-fridge, and all linens and bathroom essentials, so you can settle in comfortably without bringing a thing.Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 260220400000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Turtle House, a luxurious oceanfront retreat on the beautiful island of Oahu, available for stays of 30 nights or longer. This stunning property offers the perfect blend of relaxation and adventure, with expansive lawns, a spacious patio, and a deck that provides mesmerizing ocean views, unforgettable sunsets, and clear night skies perfect for stargazing. Registration Number: TA-128-801-0240-01, GE-129-119-4368-01, 840050160000
6 bedroom
3 bathroom
Sleeps 12
Hawaii> Waianae
Discover Coastal Bliss at Pismo BeachNestled on a scenic cliff overlooking the Pacific Ocean, our hotel offers a peaceful escape with inviting accommodations and delicious coastal cuisine. Unwind while enjoying breathtaking ocean vistas, or explore the area’s many attractions including world-class hiking trails, championship golf courses, and nearby wineries.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Legally permitted rentalRegistration Number: 260060020069, TA-173-623-0912-01, GE-173-623-0912-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Honokowai Palms A8, your ideal Maui vacation condo offering a perfect blend of comfort, convenience, and stunning views. Located in the heart of Honokowai, Lahaina, this cozy condo is just steps from beautiful beaches, vibrant local markets, and a variety of dining options.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the glamour of this stylish and upscale resort in Honolulu, Hawaii, just a block from Waikiki Beach. The Wyndham at Waikiki Beach Walk offers spacious one- and two-bedroom suites designed for comfort and convenience.Registration Number: 497982172676, GE-205-397-5670-02, TA-151-396-1128-04
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
