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 15750, 50
Experience the vibrant heart of Waikiki from this beautifully decorated one-bedroom condo in Waikiki Banyan Tower 1, located just a short walk from Waikiki Beach and Kalakaua Street. Recently updated, the unit comfortably accommodates up to four guests with a plush king-size bed and a spacious living area separated by sliding doors. Registration Number: 1260250050169, GE-194-950-5536-02, TA-194-950-5536-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this luxurious 2-bedroom end-unit golf villa nestled within the prestigious Kapalua Resort on Maui's stunning west coast. This spacious retreat offers breathtaking panoramic views of the Pacific Ocean, Molokai, and lush Lanai, making it an ideal getaway for those seeking comfort, style, and natural beauty.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapalua
Live the beach lifestyle in this charming ground-floor 1-bedroom, 1-bath home located just across the street from Kailua Beach Park. The property offers a perfect blend of sandy shores, lush grassy areas, tall ironwood trees, and turquoise waters. Registration Number: TA-164-136-7040-01, GE-164-136-7040-01, 430090180000
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
This stunning estate located in the exclusive Puka Pa neighborhood is just a short 6-minute drive from the vibrant heart of Hualalai. Spread across nearly 5,000 sq ft, this residence offers five spacious bedroom suites that comfortably accommodate up to 10 guests in bright, contemporary luxury.
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Kailua Kona
Experience the stunning beauty of Kauai from the comfort of the Kauai Beach Club Parlor Suite. This inviting villa offers breathtaking ocean vistas and easy access to Kalapaki Beach, renowned as one of Hawaii's top swimming beaches. The resort features an array of amenities including a communal pool, relaxing hot tubs, and lush landscaping, perfect for unwinding after a day of exploring.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lihue
Located just steps from the beach in the heart of Wailea, Elua Village 107 is a beautifully appointed two-bedroom, two-bathroom ground-floor residence that offers the perfect blend of comfort and island elegance. With a spacious layout and tasteful décor, this vacation rental invites you to relax and unwind in true Hawaiian style.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Experience the ultimate Hawaiian escape in this spacious split-level 2-bedroom, 2-bath condo offering partial ocean views and modern comforts. The condo features a large lanai perfect for relaxing outdoors. Inside, you'll find a full kitchen equipped with stove, oven, microwave, refrigerator, dishwasher, coffeemaker, cookware, and tableware, making meal prep convenient during your stay.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Experience a relaxing stay in this charming second-floor end unit located in Poipu. The lanai offers stunning views of the twin bridges and the ocean, perfect for morning coffees or evening sunsets. The walk-up access provides a cozy, quiet retreat without an elevator, ideal for those seeking a peaceful escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to Casa by the Sea, a private Hawaiian retreat nestled in a lush tropical jungle just one block from ocean cliffs. This rustic yet modern house offers a unique blend of Mexican charm and Hawaiian vibes, providing an unforgettable island experience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
This beautiful oceanfront studio in Lahaina offers a perfect getaway in paradise. Managed by Marriott, this unit features a full balcony with furniture, providing stunning ocean views. The studio includes a fully equipped kitchen with a two-burner cooktop, convection microwave, small dishwasher, sink, and full-sized refrigerator, ideal for preparing meals during your stay.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
5-bathroom oceanfront retreat, featuring a guest house, panoramic Kona coastline views, and an expansive private pool. Spanning over 3,500 square feet, this two-story residence sits on a lush one-acre lot, offering privacy and breathtaking vistas of the ocean and tropical landscape from six spacious lanais.
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Kailua-Kona
Experience the beauty of Maui from this gorgeous second-floor ocean view studio at Napili Shores! Ideally located right on Napili Bay Beach, this condo offers stunning ocean vistas and easy access to some of the island's best spots. The resort features two immaculate swimming pools—one oceanfront with a heated saline system and a relaxing jacuzzi, and a sparkling freshwater pool—set amidst lush tropical landscaping.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> lahaina
Discover the vibrant allure of Waikiki from our comfortable hotel room, just steps from Kuhio Beach and near the iconic Diamond Head. Wake up to stunning ocean views from your private lanai and enjoy a blend of relaxation and adventure. Each 338 sq ft room is thoughtfully equipped with two queen beds, a mini-fridge, Keurig coffee maker, and all essentials to ensure a welcoming stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
5-bathroom estate offers breathtaking ocean views and unparalleled outdoor living in the exclusive Puukole neighborhood of Hualalai. Situated on the 13th hole of the Keolu golf course, the property provides captivating vistas of Maui and Kohala, especially during stunning sunsets.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua Kona
Escape to this charming 2-bedroom, 1-bath Wahiawa residence that comfortably accommodates up to four guests. The home features a fully equipped kitchen perfect for preparing meals, a modern bathroom with a walk-in shower, and air conditioning to keep you comfortable during your stay. Registration Number: TA-000-174-0160-14, 140-425-7792-01, 1740160140000, GE-140-425-7792-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Wahiawa
Experience the ultimate beachfront getaway in this charming Honolulu studio located at Sans Souci on the Gold Coast. 6 miles from Waikiki, this oceanfront unit offers stunning views of the ocean and easy access to some of Oahu's most famous attractions, including Diamond Head and Kaimana Beach. Registration Number: 310320070080, TA-006-192-7424-01, GE-006-192-7424-01
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Kukui'ula Bungalow 1A offers a perfect blend of luxury and tropical comfort on Kauai's south shore. This beautifully appointed one-bedroom residence spans 1,275 sq. , featuring high ceilings, expansive windows, and a cozy outdoor lanai with dining and lounging areas, perfect for relaxing or enjoying views of the private golf course.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Discover your perfect getaway at Waikiki Banyan, a charming one-bedroom, one-bathroom condo just a short stroll to the world-famous Waikiki Beach. This inviting space comfortably sleeps up to four guests, featuring two brand new full beds in the bedroom and a spacious queen bed in the living room, all installed in July 2022 for your comfort. Registration Number: 260250050273, TA-149-231-0016-01, GE-149-231-0016-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to tranquility at Mauna Kea Villa 11, a charming and private two-bedroom home offering breathtaking panoramic views of the ocean and nearby golf course. Perfectly situated at the end of a quiet cul de sac, this villa provides easy access to the esteemed Mauna Kea Beach Hotel and its stunning beaches, Kaunaʻoa Bay and Hapuna Beach, just a short walk or quick drive away.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
Stay at Kona Bali Kai 167 and enjoy stunning ocean views from this comfortable and well-appointed condo in Kailua-Kona. Upon entering, guests are greeted by floor-to-ceiling glass doors that showcase the beautiful blue waters. The tiled flooring and charming rattan furniture create a relaxed island vibe in the main living space.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to your perfect Maui escape at our charming 2-bedroom, 2-bathroom ocean-view condo in Kahana Villa. This two-story retreat offers a blend of comfort and stunning vistas, making it ideal for families, couples, or friends seeking a relaxing island getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This charming condo offers spectacular 360-degree views from a prime 6th-floor location, capturing the beauty of Maalaea Bay, West Maui Mountains, Kealia wetlands, and the bird sanctuary. Directly on Sugar Beach, you can walk for miles on pristine white sands, perfect for sunbathing or leisurely strolls.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Summary:
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lihue
Experience the comfort of Waikoloa Colony Villas, a gated community offering a spacious townhome with stunning garden views. This vacation retreat features a full-size kitchen equipped with refrigerator, stove/oven, microwave, dishwasher, rice cooker, blender, coffeemaker, cookware, and tableware, perfect for preparing delicious meals during your stay.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Unknown
Kaanapali Alii 125 offers a luxurious oceanfront retreat with spectacular views from every room. This spacious second-floor condo features two bedrooms and two bathrooms, comfortably accommodating up to six guests. The master suite includes a plush king bed, a flat-screen TV with cable, and a dedicated workspace, perfect for relaxing or catching up on work while enjoying the stunning ocean vistas.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the comfort and convenience of Vista Waikoloa A101, a beautifully located condo in the gated Waikoloa Beach Resort on Hawaii’s Kohala Coast. This first-floor, two-bedroom unit offers a perfect retreat for families or couples seeking relaxation and easy access to the island's attractions.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Beach Resort
Discover this beautifully remodeled 2-bedroom, 2-bath condo in Maui Kameole Resort, completed in early 2023. The property offers upscale touches, stunning ocean vistas, and a prime location just steps from Wailea and South Kihei's beaches. The open-concept space features two king-sized beds with luxurious linens and various pillows for added comfort, a fully equipped modern kitchen, and designer furnishings that create a welcoming, high-end atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea-Makena
Welcome to one of the most unique beachfront homes on Oahu’s north shore. Located directly on the breathtaking Laniakea beach, this location is well-known for spotting Hawaii’s endangered green sea turtles along the shore! Laniakea is also a lovely beach for swimming and snorkeling in the summer and is a surfer’s dream location with views of Himalayas, Lanis, Holtons, Jockos & Chuns.Registration Number: GE-172-762-5728-01, TA-172-762-5728-01, 610100010000
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Haleiwa
Experience the ultimate Hawaii escape at this charming 4-bedroom residence nestled in the North Fairways of Mauna Kea Beach Resort. The property offers breathtaking panoramic ocean views, with glimpses of Maui and the Kohala mountains, creating a picturesque backdrop for your stay.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kamuela
Experience the perfect Hawaiian getaway in this spacious and beautifully maintained two-bedroom townhouse nestled within the highly sought-after Waikoloa Beach Resort community. This inviting condo offers breathtaking mountain and expansive golf course vistas, creating a serene backdrop for your stay.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Spend some time at our magnificent hotel and treat yourself to a unique experience. Here is where you start your journey to indulgence and relaxation. Explore the excellent selection of amenities that are waiting for you, and get ready to be pampered. Registration Number: 260270180000, TA-207-956-5824-01, GE-207-956-5824-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Kukuiula Vacation Home #2, a delightful ocean view beach cottage nestled in the heart of Poipu's sought-after Kukuiula neighborhood. This single-story plantation-style residence beautifully blends vintage Hawaiian charm with modern luxury, creating a memorable retreat for your island getaway.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Koloa
Located in Lahaina, this charming hotel offers a peaceful and inviting retreat for guests seeking comfort and convenience. The property features a cozy living room equipped with two futon beds and two king beds, providing ample space for relaxation and restful sleep.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your perfect Maui getaway at Kahana Village 3, a beautifully maintained oceanfront condo offering breathtaking views and a host of complimentary activities valued up to $570 during your stay. This spacious 2-bedroom, 2-bathroom retreat comfortably accommodates up to 6 guests, making it ideal for families or friends seeking a relaxing beachside escape.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this charming 1-bedroom, 1-bath vacation rental nestled in Waianae, Hawaii, perfect for both short visits and extended stays. The property offers a cozy living area with ceiling fans and a dedicated workstation, ideal for remote work. The modern kitchen features a stove, oven, refrigerator, microwave, Keurig coffee maker with coffee provided, and all necessary dishware and cooking basics. Registration Number: GE-155-565-7216-01, TA-155-565-7216-01, 1850230480000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waianae
Welcome to your perfect Maui retreat at Maui Kamaole M205! This beautifully renovated 2-bedroom, 2-bathroom condo combines modern elegance with island charm. The updated kitchen and bathrooms feature a sleek, contemporary style, creating a comfortable and inviting atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to our dreamy Kona getaway! This newly refurbished, luxury beachy retreat is designed for relaxation and convenience in the heart of Kailua Kona. Great for the Sunsets! Walking distance from Kona's best restaurants and bars, it provides easy access to Ali'i Drive and all it offers, including beaches, snorkeling, sightseeing, shopping and eateries.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the stunning beauty of Kapalua's Bay Course from these spacious Golf Villas with fairway views. Each 1-bedroom unit offers a fully equipped kitchen, perfect for preparing your favorite meals. The bedroom features a comfortable king or queen bed, while the living area includes a queen sofa bed, accommodating up to four guests including children and infants.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect Kauai escape at Wyndham Bali Hai Villas in Princeville. This spacious 2-bedroom condo offers 1,149 sq ft of comfort, accommodating up to 6 guests with a king bed, queen or doubles, and a sofa sleeper. Enjoy modern conveniences like a full kitchen, washer/dryer, and balcony (availability varies).
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Experience the perfect blend of comfort and convenience at this stylish studio located on the 10th floor of a centrally situated hotel in Ala Moana, Honolulu. The unit offers stunning ocean and city views from a private lanai, making it an ideal retreat for travelers seeking luxury and accessibility.Registration Number: GE-125-401-6512-06, TA-125-401-6512-06, 1230380020298
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This private studio is located in the vibrant east area of Waikiki, offering a perfect retreat for solo travelers, couples, or small families. Situated just a 3-minute walk from the beach, it provides easy access to Honolulu’s top attractions including Kapiolani Park, Diamond Head, the Honolulu Zoo, and the Waikiki Aquarium. Registration Number: 260270200079, TA-099-018-1888-01, GE-099-018-1888-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your serene retreat at Kaanapali Alii 164! This beautiful two-bedroom, two-bath condo comfortably hosts up to six guests, making it ideal for families or friends seeking a peaceful escape. Step onto the furnished lanai to enjoy stunning views of the pool and ocean, framed by gently swaying palms—a perfect setting for morning coffee or sunset cocktails.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Napili Shores E135, a beautifully appointed ground-floor condo in the heart of Napili. This 1-bedroom, 1-bathroom retreat comfortably sleeps up to 4 guests, making it ideal for couples or small families seeking a relaxing tropical escape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Napili
Experience oceanfront serenity with stunning views, spacious rooms, full kitchen, private balcony, and in-room laundry—ideal for couples and families. Snorkel nearby, hike the Mahaulepu Trail, or spot turtles at Poipu Beach. Relax in our large outdoor pool.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Koloa
Experience the pinnacle of luxury living at Hualalai Resort with this stunning private residence. 5-bath home offers breathtaking panoramic ocean views and exceptional amenities for a memorable stay.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua Kona
Experience the comfort of this first-floor Hawaii condo with an oversized patio, ideal for relaxing outdoors. Inside, Vinyl plank flooring in the living room, dining area, and bedroom provides a modern touch, while the bathroom features updated granite counters and a walk-in shower with a grab bar for safety.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this spacious 1,025 square foot, two-bedroom, two-bath condo nestled within the iconic Hilton Hawaiian Village. Perfect for families seeking a blend of relaxation and adventure, this unit offers a comfortable and convenient retreat. The expansive living room invites you to unwind after a day spent enjoying the resort’s pools and nearby beaches. Registration Number: TA-137-910-0160-01, GE-137-910-0160-01, 26009001
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Recently renovated and beautifully furnished, this luxurious 2-bedroom, 2-bath single-level corner villa is nestled in the sought-after Waiulu Villa neighborhood of Hualalai. With 1,868 sq ft of living space, the villa offers a bright and airy atmosphere thanks to large windows that flood the space with natural light.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience a comfortable and stylish stay in this 3rd-floor condo offering a private balcony and elevator access to all levels. The fully equipped kitchen features granite countertops, a Wolf cooktop, convection oven, microwave, dishwasher, and a full-size Sub-zero refrigerator, perfect for preparing delicious meals.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Welcome to your dream escape on one of Hawaii’s most iconic beaches. This stunning 2-bedroom condo at Hapuna Beach Residences offers unparalleled ocean and beachfront views, all upgraded and modern decor, and direct access to the soft white sands of Hapuna Beach.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
