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 15700, 50
Discover this charming Maui condo just 500 feet from Charlie Young Beach, offering a perfect blend of comfort and convenience for your island getaway. Located in Building One on the second floor, the unit boasts a private balcony with pool views, ideal for relaxing after a day in paradise.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Kona Pacific C416, a charming and well-appointed 1-bedroom, 2-bathroom condominium nestled in the heart of Kailua-Kona. This inviting ocean-view retreat comfortably hosts up to four guests, making it an ideal choice for couples, families, or friends seeking a blend of relaxation and adventure on the Big Island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Enjoy a peaceful stay at this 1-bedroom house in Hakalau. The bedroom features a comfortable king bed, 1 bathrooms come equipped with a bathtub and hair dryer. This welcoming property also offers amenities like a washing machine, iron, and WiFi.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Hakalau
Discover the ideal island retreat in this beautifully remodeled studio at Kona Bali Kai, perfectly situated on the ground floor with stunning ocean views. The unit features contemporary furnishings, a very well-equipped kitchen with granite countertops, and a gorgeous bathroom designed for comfort and style.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the vibrant heart of Honolulu in this spacious and modern condo located at Alohana Executive Centre. Perfect for both business travelers and visitors, this suite offers a comfortable king-size bedroom, a cozy living area with a sleeper sofa and flat-screen TV, and a large dining table. Registration Number: GE-188-381-5936-01, TA-188-381-5936-01, 1210120040483
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Dolphin Bay Estate offers a luxurious escape on the beautiful Big Island of Hawaii, nestled in the charming town of Captain Cook. This exquisite property combines tranquility, elegance, and convenience, making it an ideal retreat for memorable Hawaiian holidays.
3 bedroom
3 bathroom
Sleeps 11
Hawaii> Captain Cook
Welcome to Kona Isle B25, your perfect Hawaiian retreat managed by Kona Coast Vacations. This beautifully decorated condo offers breathtaking partial ocean views and a comfortable, open-concept living space designed for relaxation and convenience.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Feel the island�s Aloha in this cozy and stylish Waikiki studio! Located just steps from world-famous Waikiki Beach, this fully furnished space offers comfort and convenience for your Hawaiian escape. Situated in Kuhio Village Towers, the renovated corner unit boasts two balconies—one with a bistro table for al fresco dining, the other with a lounge chair for relaxing. Registration Number: TA-151-260-2112-01, STR: 1765 Expires 07/12/2025, GE-151-260-2112-01, 1260230500053
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the ultimate Hawaiian retreat at Makena Surf A204, a spacious and luxurious oceanfront condo offering breathtaking views and direct access to Po'olenalena Beach. This 1,600 sq ft second-floor residence features an open-concept design with a generous great room that seamlessly connects living, dining, and kitchen areas, all opening onto a 335 sq ft private lanai perfect for enjoying Maui's famous sunsets.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Discover the vibrant allure of Waikiki from our beachfront hotel room, just steps from Kuhio Beach and Diamond Head. Wake up to stunning ocean vistas and immerse yourself in Hawaiian adventures. Each spacious 338 sq ft unit features a comfortable king bed, private lanai, mini-fridge, Keurig coffee maker, and all essential amenities to ensure a cozy stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Discover this inviting 2nd floor condo situated on the north side of the building, offering easy access to the elevator and parking. The private covered balcony and open sundeck provide perfect spots for relaxation and outdoor enjoyment. Interior features include tile floors throughout, a recently renovated kitchen with new cabinets, countertops, stovetop, oven, refrigerator, microwave, dishwasher, coffeemaker, blender, cookware, and tableware, making meal preparation a breeze.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience a comfortable and stylish stay in this first-floor villa at Kapalua Golf Villas. The home offers two stairs between the parking area and front door, providing convenient access. The updated open kitchen features granite counters and includes modern appliances such as a fridge with an ice maker, stove/oven, microwave, dishwasher, cookware, and tableware.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Located in the heart of Waikiki, Wyndham Beach Walk offers a spacious 1-bedroom lower level deluxe suite perfect for a relaxing Hawaiian vacation. Just one block from Waikiki Beach and half a block from the vibrant Kalakaua Avenue, you'll enjoy easy access to the sun, surf, shopping, and dining. Registration Number: TA-049-063-3728-01, 1260020270088, GE-049-063-3728-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honoloulu
Experience comfort and convenience in this spacious 1575 sqft condo featuring a master bedroom with a king bed and a guest bedroom with two twin beds. The living area includes a sofa, providing ample space for relaxation. The fully equipped kitchen offers a stovetop, oven, microwave, refrigerator, dishwasher, coffeemaker, blender, rice cooker, cookware, and tableware, making meal preparation effortless. Registration Number: 260040120163, TA-195-600-3840-01, GE-195-600-3840-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Discover this stunning, fully renovated oceanfront condo nestled in the heart of Mauna Lani Resort. This contemporary one-bedroom unit offers a spacious living area, a private patio overlooking historic fishponds, and breathtaking views of the Pacific Ocean, making it an ideal retreat for couples seeking tranquility and luxury.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kamuela
Discover this cozy Waikiki studio, ideal for romantic escapes or small family vacations. Located less than 100 yards from Waikiki Beach, it offers easy access to surf, swim, and sunbathing. The space features a comfortable king-sized bed, a convertible sofa/futon, and a window AC system to keep you cool. Registration Number: 260270200010, GE-116-269-2096-01, TA-116-269-2096-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This third-floor corner condo in North Kaanapali offers a bright and spacious retreat perfect for your Maui vacation. Sunlight floods in through the large windows, creating a warm and inviting atmosphere. The living area features a comfortable L-shaped couch ideal for family gatherings and entertainment on the flat-screen TV.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the perfect Hawaiian escape at Marriott's Kaua'i Beach Club, ideally located along Kalapaki Beach amidst the lush landscapes of Kauai. This resort offers elegant guest rooms, villas, and parlors inspired by a royal Hawaiian estate, featuring oceanfront or garden views, pillowtop bedding, and complimentary Wi-Fi.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
Welcome to our beautifully maintained, fully furnished ground-floor villa located in the exclusive Waikoloa Beach Resort on the Big Island of Hawaii. This inviting corner unit offers 1,403 square feet of comfortable living space, perfect for families or groups of up to 6 guests.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Discover Coastal Bliss at Pismo BeachNestled on a cliff overlooking the Pacific Ocean, our hotel provides a peaceful retreat with inviting accommodations and delicious coastal cuisine. Revel in breathtaking views, explore world-class hiking trails, enjoy championship golf, and visit nearby wineries for a complete coastal experience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Kuhio Shores is located at 5050 Lawai Road, Poipu 96756 Unit 114
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Poipu
Experience the best of island living in this thoughtfully designed Hawaii vacation unit, just a minute's walk from the ocean and Queen's Beach. Perfectly positioned between vibrant city life and tranquil nature, it offers a cozy retreat with a queen bed and a foldable full-sized futon, accommodating both adults and children comfortably. Registration Number: 260270200042, 0224, GE-153-272-4224-01, TA-153-272-4224-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Breath of Life, your tranquil escape nestled in the scenic Makaha Valley. Located at the end of a quiet cul-de-sac, this charming retreat offers breathtaking views of the ocean and mountains, making it an ideal sanctuary for relaxation and rejuvenation.Registration Number: TA-201-782-2208-01, 1840291500087, GE-201-782-2208-01
4 bedroom
2 bathroom
Sleeps 9
Hawaii> Waianae
Experience the perfect Hawaiian escape at Plantation Hale F9, located in the heart of Kapaa Town. This charming condo offers a blend of comfort and style, just steps from the beach and surrounded by lush tropical grounds. The unit features recent updates including air-conditioning, granite countertops, a remodeled bathroom, and modern furnishings.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kapaa
Escape to paradise in this charming one-bedroom apartment on the beautiful island of Hawaii! Located just minutes from stunning beaches, vibrant shopping districts, and lively entertainment options, this cozy retreat is ideal for a couple seeking a relaxing getaway.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Aloha from Midway Vacations! Our team of local hospitality experts is dedicated to turning your Hawaiian getaway into a reality. When you stay at one of our properties, you can count on a professionally cleaned and meticulously maintained experience.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Hawaiian getaway in this beautifully appointed 1-bedroom condo located on the famed Ka'anapali Beach. Designed with warm wood tones and tropical accents, this unit offers a cozy yet luxurious atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Valley Isle 901, a beautifully appointed 1-bedroom, 1-bath condo offering breathtaking ocean vistas from your private lanai. The space is thoughtfully designed with ceiling fans throughout and a split A/C system to keep you comfortable during your stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the charm of Lahaina with a stay at Kahana Falls Resort, offering a blend of comfort and tropical beauty for an unforgettable Maui vacation. This resort features a stunning free-form swimming pool surrounded by lush waterfalls, creating a peaceful oasis to unwind after a day of exploring.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Located on the stunning shores of Ka'anapali Beach, Marriott Ocean Club offers a perfect family-friendly escape surrounded by Hawaii's breathtaking scenery. This listing features two separate 3-bedroom, 3-bath villas, each accommodating families or groups seeking spacious comfort.
8 bedroom
6 bathroom
Sleeps 24
Hawaii> Lahaina
Discover the vibrant charm of Waikiki from our beachfront hotel room, just steps away from Kuhio Beach and Diamond Head. Wake up to stunning ocean views and immerse yourself in Hawaiian adventures. Each room is thoughtfully equipped with amenities like a mini-fridge, Keurig coffee maker, and all linens, 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
Escape to the stunning Big Island at White Sands Village in Kailua-Kona, a perfect blend of relaxation and adventure. This inviting 2-bedroom home offers modern comforts, tropical decor, and a prime location near pristine beaches and vibrant local attractions.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua Kona
E komo mai (welcome) to your Hawaiian paradise located on the eighth floor of the prestigious Kaanapali Alii Resort. This elegant two-bedroom, two-bath suite comfortably sleeps six guests and offers a unique blend of stunning moana (ocean) and breathtaking mauna (mountain) views, allowing you to experience the best of Kaanapali without leaving your retreat.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this charming 1-bedroom condo in Koloa, perfect for a relaxing getaway. The living area includes a sofa bed, ideal for extra guests. The fully equipped kitchen ensures convenience, while amenities like WiFi, air conditioning, an iron, and a hairdryer add comfort.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to our inviting townhome located in the prestigious Ko Olina Kai Golf Estates. This spacious property features three comfortable bedrooms, each providing a cozy retreat after a day of exploring the beautiful Oahu surroundings. With three bathrooms, there's ample space for everyone to enjoy privacy and convenience. Registration Number: 910560050243, GE-910-560-0502-43, TA-142-227-1488-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
5-bathroom retreat that seamlessly combines comfort, luxury, and sustainability in an idyllic setting.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Koloa
Welcome to Maui Kamaole M210, a beautiful split-level condo offering modern comfort and tropical charm. Located in a sought-after resort in Kihei, this 2-bedroom, 2-bath unit comfortably hosts up to 6 guests. The bright, beach-inspired decor creates a fresh, airy atmosphere throughout the condo, which features split A/C for added comfort.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to the breathtaking Park Lane 3300, a luxurious residence nestled in Building 3 of the prestigious Park Lane at Ala Moana. 5-bathroom condo comfortably accommodates six guests, offering an ideal blend of elegance and comfort.Registration Number: 230380130073, TA-199-598-2336-01, GE-199-598-2336-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Honolulu
This oceanfront resort offers breathtaking views of the Pacific Ocean and surrounding mountains, making it an ideal destination for families seeking a memorable Hawaiian getaway. Located on Kauai's South Shore, The Point at Poipu Resort is managed by Hilton Vacation Club and boasts extensive amenities designed for relaxation and fun.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Discover this beautifully remodeled one-bedroom, one-bathroom condo nestled in the tranquil Napili Ridge complex on Maui's west side. The unit features a fresh, modern design with a fully equipped kitchen boasting new stainless-steel appliances, ideal for preparing anything from gourmet meals to casual picnics.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this inviting 4th-floor unit at Castle Bamboo Waikiki Hotel, featuring large sliding glass doors that fill the space with natural sunlight and lead to a furnished private lanai. This climate-controlled retreat offers a cozy living area with a large sofa and a comfortable Queen bed, perfect for relaxing after exploring the vibrant city of Waikiki. Registration Number: TA-147-997-0816-01, GE-147-997-0816-01, 260230670030
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Welcome to your cozy private studio in the heart of Waikiki, ideally located just a 3-minute walk from the beautiful beach. This fully furnished condominium offers a comfortable space for friends, couples, or families looking to explore Honolulu. Registration Number: 260270200065, TA-099-018-1888-01, GE-099-018-1888-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the ultimate Hawaiian getaway at this luxurious condo in Mauna Lani Point. 5-bath end-unit offers breathtaking views of Mauna Kea and lush tropical landscapes. Designed with comfort and style in mind, the open-concept living area features island-inspired furnishings, central air conditioning, and ceiling fans throughout to keep you cool.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Experience breathtaking ocean and city views from this high-level corner unit at Koko Resorts in Waikiki. The spacious 1-bedroom, 1-bath condo features a retractable window wall that brings the outdoors inside, offering unobstructed vistas of the sparkling blue ocean, sky, and Honolulu skyline by night. Registration Number: 260250050360, TA-114-740-4288-01, GE-114-740-4288-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Perched above the sparkling Pacific Ocean on Kauai's sunny South Shore, this boutique condo at Sunset Kahili offers breathtaking ocean views and a tranquil retreat in the heart of Poipu. The thoughtfully designed unit features tasteful tropical decor and furnishings, creating a warm and inviting atmosphere that instantly feels like home.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Po'ipu
Experience comfort and convenience in this spacious 7th-floor condo offering a private balcony and modern amenities. The 777 sqft unit features a fully equipped kitchen with a fridge, stove/oven, microwave, dishwasher, cookware, and tableware, making meal preparation effortless.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Golden Goose Home-base! Stay with us we are thrilled to Host your Hawaiian Adventure.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Captain Cook
Experience the tranquility of this spacious second-floor villa offering breathtaking views of the ocean, golf course, and neighboring islands of Lanai and Molokai. The villa features two comfortable lanais—one accessible from the living room and another from the master bedroom—perfect for dining or relaxing while enjoying stunning sunsets.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Kaanapali Alii 214, a stunning one-bedroom, two-bath garden view residence perfect for your Maui vacation. This condo features a spacious primary bedroom with a king-sized bed, wall-mounted smart TV, and direct access to a large garden view lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate tropical retreat at Hilton Hawaiian Village Waikiki Beach Resort in our spacious all-suite hotel. Each suite offers 1 to 3 bedrooms, a full kitchen, living and dining areas, and private balconies with breathtaking views of the ocean, mountains, or Waikiki skyline. Registration Number: 126004012000, GE-179-756-0320-02, TA-179-756-0320-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu Oahu
