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 16000, 50
5-bath luxury estate located in Hualalai Resort. This designer-appointed home offers breathtaking white-water ocean views from every room, with a prime location just steps from the resort's beaches, restaurants, and world-class amenities. The residence features 3,185 square feet of spacious interior living and an additional 1,000 square feet of covered lanai, perfect for relaxing outdoors and enjoying the spectacular scenery.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover this private, third-floor end-unit condo in Poipu, offering stunning partial ocean views and views of Moir Gardens from a spacious lanai. The walk-up location provides a quiet retreat without elevators, perfect for a relaxing stay. The condo features 880 sqft of comfortable living space, with tiled flooring throughout and cozy carpeting in the bedrooms.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
5-bath luxury home nestled in the exclusive Kukui‘ula community on Kauai’s breathtaking southern coast. The name, meaning “The Great Sea,” perfectly captures the home’s serene coastal ambiance, reflected through soothing blue tones and elegant nautical touches throughout the interior.
5 bedroom
5 bathroom
Sleeps 11
Hawaii> Koloa
Welcome to Mauna Lani Point H102, a beautifully appointed ground-floor condo offering the perfect blend of luxury and comfort in a serene Hawaiian setting. Step outside onto your private lanai to enjoy breathtaking ocean and golf course views, gentle breezes, and spectacular sunsets that define the island experience.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
This beautiful Maui ocean view residence combines contemporary design with island charm, offering an inviting retreat for up to four guests. The spacious 1,544 sq ft layout features a one-bedroom plus sleeping den, providing both comfort and privacy.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This exquisite luxury plantation-style estate offers a perfect blend of comfort and elegance right on the beachfront of Kekaha, Kauai. Featuring three spacious bedrooms and three bathrooms, this two-story home is ideal for families or groups seeking a secluded tropical retreat.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kauai County
Shores of Maui 123 is a beautifully updated one-bedroom condo featuring an open-concept living space and tile flooring that flows seamlessly out to the private lanai. This ground-floor unit comfortably accommodates up to four guests, with a king bed in the bedroom and a queen sleeper sofa in the living area.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the perfect Maui retreat in this charming 1-bedroom resort located in Kihei. The spacious bedroom features a luxurious king bed, while the living room offers a sofa bed for additional guests. Enjoy modern amenities such as WiFi, an iron, and a washing machine, making your stay comfortable and convenient.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Discover the vibrant heart of Honolulu from this stylish 2-bedroom, 2-bath apartment located on the 43rd floor of Sky Ala Moana. This property offers stunning island views and a prime location close to top attractions and shopping. The expansive sky terrace is perfect for barbecues, family gatherings, or relaxing evening swims, with one of the largest decks in Honolulu. Registration Number: 230160480083, GE-092-554-1888-01, TA-092-554-1888-01
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Discover the vibrant allure of Waikiki from our beachfront hotel room, just steps from Kuhio Beach and Diamond Head. Wake up to breathtaking ocean views and explore local cuisine, shopping, and nightlife within easy reach. Each thoughtfully designed room offers comfort with amenities like a mini-fridge, Keurig coffee maker, and private lanai, ensuring a relaxing stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Come immerse yourself in the tropical paradise of Oahu at this premium Marriott Resort. The property offers luxurious amenities, including a lagoon-style family pool with jacuzzis, a slide, and grotto area, perfect for kids and adults alike. Relax at the reflection pool or take a stroll out to a private sandy beach and lagoon with crystal-clear waters. Registration Number: TA-199-820-7488-01, 910570130000, GE-199-820-7488-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Oahu
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Pahoa
Situated on the upper tier of the Kainani neighborhood within the stunning Kukui'ula Community, this beautifully designed, newly built custom home offers an unparalleled Hawaiian retreat. The residence showcases exquisite craftsmanship with imported teak flooring, custom cabinetry, elegant vanities, natural wood accents, stone features, lava rock elements, and carefully curated furniture and decor, creating a warm yet luxurious ambiance.
4 bedroom
5 bathroom
Sleeps 12
Hawaii> Koloa
Discover a charming and newly renovated 1-bedroom condo in the heart of Waikiki, Honolulu. This 11th-floor residence offers two queen beds and a pullout sofa, making it ideal for families or small groups. Relax on the balcony overlooking Diamond Head or enjoy the building's amenities such as WiFi, air conditioning, and a full kitchen. Registration Number: 123456789012, TA-175-953-2544-01, GE-175-953-2544-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this newly remodeled 2-bedroom condo in Maui, ideal for a relaxing island escape. The spacious unit features a comfortable king bed, a cozy queen bed, and a sofa bed in the living room, providing ample space for guests. The living area is inviting and perfect for unwinding after a day of exploring, with modern furnishings and a peaceful ambiance.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
This delightful studio in Southpoint of Naalehu offers a serene escape for guests seeking relaxation. Nestled on 23 acres of rolling grasslands, it provides a peaceful, private environment without neighbors, cocky frogs, or roosters to disturb your tranquility.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Naalehu
Discover a beachfront oasis in Waikiki with three uniquely appointed hotel rooms, each offering stunning ocean views and modern amenities. Perfect for families or romantic escapes, these rooms provide a comfortable and authentic Hawaiian experience.Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Experience the relaxed Maui lifestyle in comfort and style at this beautifully remodeled two-bedroom, two-bath condo, located just 126 steps from the golden sands of Kalepolepo Beach. Whether you're watching whales in the bay, snorkeling in warm waters, or taking in vibrant sunsets from the lanai, this is the perfect home base for your island adventure.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover the serene beauty of South Maui at Kihei Surfside #501, a luxurious top-floor oceanfront condo offering breathtaking 180° views of the Pacific Ocean. Perfectly situated in one of Maui's most sought-after beachfront resorts, this two-bedroom, two-bath retreat is ideal for those seeking a peaceful, scenic island escape just steps from the water.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei, Maui
Discover this spacious one-bedroom suite offering stunning partial ocean views from its balcony overlooking Ala Moana, Diamond Head, and Waikiki. The 600 sqft unit comfortably accommodates up to 4 guests, making it ideal for families or couples seeking a relaxing Hawaiian getaway.Registration Number: TA-137-910-0160-01, GE-137-910-0160-01, 26009001
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to Papakea C301, a beautifully appointed 1-bedroom, 1-bathroom condo perched on the third floor, offering stunning garden and ocean views from your private lanai. This spacious unit comfortably accommodates up to four guests with a plush California king bed in the bedroom and a queen sofa bed in the living area, perfect for families or small groups.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to this stunning Hawaiian-style executive home, your perfect retreat in paradise! Designed with comfort and convenience in mind, this spacious 4-bedroom residence offers a tranquil escape just minutes from the island's most popular destinations.Registration Number: TA-186-658-4064-02, GE-186-658-4064-04
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Mililani Mauka-Launani Valley
Welcome to your dream vacation at Sugar Beach in Maui! This fully renovated oceanfront penthouse condo offers breathtaking views and modern comforts on the island's longest sandy beach.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Maui Kaanapali Villas 110, a spacious ground-floor condo nestled in the heart of the Maui Kaanapali Villas Resort. This inviting suite offers a perfect blend of comfort, style, and convenience for up to six guests. The primary bedroom features a plush king-size bed, a flat-screen TV, and a private bathroom, providing a tranquil retreat after a day of exploring.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Surfside Haven, a charming vacation rental nestled in the heart of Kihei, Maui. Situated directly across from the iconic Cove Park, this inviting 2-bedroom, 2-bathroom unit provides the perfect blend of comfort, convenience, and island charm for your Hawaiian getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Located on the stunning shores of Ka'anapali Beach, Marriott Ocean Club offers a perfect family retreat surrounded by Hawaii's natural beauty. This listing features two separate 3-bedroom, 3-bath villas, ideal for larger groups or families traveling together.
8 bedroom
6 bathroom
Sleeps 24
Hawaii> Lahaina
Experience the ultimate Maui getaway in Hokulani 634 at Honua Kai, a beautifully upgraded two-bedroom suite offering stunning panoramic views of the lush West Maui Mountains from its prime corner location. The spacious layout comfortably accommodates up to six guests, with king-sized beds in each bedroom, a convertible sofa bed in the living room, and an optional split bed configuration for added flexibility.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate beachside retreat in this stunning 2200 sqft villa located closest to Oneloa Beach. Accessible via 16 stairs from the parking area, this first-floor residence offers easy access and breathtaking ocean views. The villa boasts elegant travertine tile floors throughout, complemented by an updated open kitchen featuring granite countertops, stainless steel appliances including a fridge with ice maker, stove/oven, microwave, dishwasher, cookware, tableware, and a unique chef’s table counter, making it ideal for culinary enthusiasts.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the perfect getaway at Waikiki Homaha in the historic Rosalei building. This beautifully renovated 1-bedroom, 1-bathroom condo offers a blend of modern luxury and Hawaiian charm. The stylish interior features an open-plan living area with Natuzzi leather furniture, a smart TV, and air conditioning, creating a comfortable haven after a day exploring Waikiki. Registration Number: GE-160-587-1104-01, 260170040043, TA-160-587-1104-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Immerse yourself in the luxury of this stunning townhome located in the desirable Kulalani at Mauna Lani community on the Big Island. This beautifully decorated, newly furnished two-story residence provides the perfect setting for a memorable Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Kamuela
Located in the heart of Waikoloa Beach Resort near multiple golf courses.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Experience the ultimate Hawaiian vacation in this spacious 2-bedroom, 2-bath suite at Hilton Hawaiian Village Waikiki Beach Resort. Spanning 1,220 sqft, this suite offers breathtaking partial ocean views and all the comforts of home.Registration Number: TA-121-575-4240-02, GE-121-575-4240-02, 126010007000
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to paradise in this beautifully appointed one-bedroom vacation rental nestled in the lush tropical gardens of Wailea Ekahi Village. Located on Maui’s sunny south shore, 26D is a tranquil second-floor unit that offers the perfect blend of privacy, comfort, and island charm—just a short stroll from world-renowned Keawakapu Beach.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to Dolphin House by Gather, a delightful beachfront retreat nestled in Laie, Hawaii. This charming three-bedroom, two-bathroom home comfortably hosts up to six guests and is just steps from a pristine, swimmable beach, providing a tranquil escape with breathtaking ocean vistas.Registration Number: TA-106-694-7072-01, 550032010000, GE-106-694-7072-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Laie
Bring the whole family to this 3 bedroom 3 1/2 bath clean ,updated beach condo. We are located in the Poipu Kai greenbelt just a short walk to the beach with access to two pools and a Jacuzzi. Keep cool with AC in all bedrooms and ample space to keep the whole family comfortable.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
This beautifully appointed, single-level oceanfront villa offers the ultimate in island living. Designed with elegance and comfort in mind, it features a sunken living room and expansive sliding glass pocket doors that seamlessly blend indoor and outdoor spaces.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Welcome to The Palm Shade Stay, your breezy home base on Kauai's lush Coconut Coast. Nestled among swaying palms at Plantation Hale, this freshly updated ground-floor condo features brand-new furnishings, fresh paint throughout, and a newly renovated bathroom—bringing modern comfort to your tropical escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Discover the perfect Hawaiian escape at Hawaiian King 212 Suite, nestled in the vibrant heart of Waikiki. This charming 1-bedroom condo offers a cozy, comfortable space ideal for 1-2 guests. Equipped with a queen bed, full kitchen, and modern amenities, it makes a great home base for exploring Honolulu. Registration Number: GE-112-181-4016-01, 260211020022, TA-112-181-4016-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Maui Kamaole F101 – your perfect island retreat! This beautifully appointed ground-floor condo features 1 bedroom and 2 full bathrooms, comfortably accommodating up to 4 guests. The dual-bathroom layout is ideal for families or couples traveling together.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Sealogde, one of North Shore's most secluded bluffs offering breathtaking oceanfront views from every room. This completely renovated corner unit boasts a private entrance, ensuring your privacy and tranquility. Inside, you'll find a spacious, open-concept living area filled with natural light, high-end furnishings, and modern touches like stainless steel appliances, recessed lighting, and rain shower bathrooms.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Escape to this wonderful cottage in picturesque Kula. This cozy retreat features 4 king bedrooms, 2 bathrooms with showers, and 2 additional living spaces with queen beds. Guests can enjoy the convenience of amenities like AC, WiFi, Smart TVs, Washer and Dryer, etc.
4 bedroom
2 bathroom
Sleeps 12
Hawaii> Makawao
Welcome to your newly renovated 1-bedroom, 1-bath condo at Waikiki Sunset in Honolulu, HI. This stylish apartment features granite countertops, fresh paint, and new flooring, creating a bright and modern living space. Located on the cooler side of the building, you can enjoy peaceful mountain views and a peek-a-boo view of Diamond Head from the living room. Registration Number: 260280110125, GE-077-050-2656-01, TA-077-050-2656-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience a memorable stay in this beachfront hotel in Waikiki, featuring two unique 360sf oceanview rooms with king beds, sofa beds, and all essentials for a comfortable vacation. Guests enjoy access to a sparkling outdoor pool, where loungers, umbrellas, and towels are provided, perfect for relaxing under the Hawaiian sun. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Kaanapali Alii 235 offers a luxurious ocean view vacation experience in Maui's prestigious Kaanapali resort area. This spacious 1,788 sq ft condo features a warm island-inspired design with breathtaking Pacific Ocean views visible from both the living room and private lanai.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Maui Kaanapali Villas #221 offers a cozy studio nestled in a lush tropical setting on N. Kaanapali Beach. Located in the Aloha Tower, this second-floor condo provides easy elevator access to the resort's amenities, including pools, gardens, and beachfront pathways.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Looking for the ultimate family escape in Hawaii? Kings Land by Hilton Grand Vacations offers a perfect blend of relaxation, adventure, and luxury on the stunning Big Island. Nestled along the picturesque Kohala Coast, this resort provides an ideal setting for memorable family holidays.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This beautifully renovated ground-floor Kapalua Bay Villa offers stunning ocean vistas from the upstairs master suite and main living area. The interior features custom cabinetry, granite countertops, and stainless steel appliances in the modern kitchen, making it perfect for a luxurious Maui escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautiful ocean view condo in Kahana Village, Maui, offering a tranquil retreat just minutes from top beaches and golf courses. The spacious living area features high vaulted ceilings, filled with natural light and soothing ocean sounds.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
