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 11750, 50
Discover the elegance of Wailea Elua Village 2008, a luxurious oceanview condo situated along Maui's southern coast. This beautifully appointed one-bedroom, two-bathroom residence offers a perfect blend of comfort and natural beauty. The open-concept living area is tastefully furnished and bathed in natural light, with large sliding doors that open to a private lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to Hale Lanikai in Ko Olina, your perfect Hawaiian retreat. This beautifully appointed condo features a private balcony overlooking lush gardens, a serene lap pool, and the sparkling Pacific Ocean. The space offers a sleek kitchen, cozy lounge areas, and beach-inspired decor, creating a bright, airy, and modern ambiance ideal for relaxing or entertaining.Registration Number: GE-157-987-7376-01, TA-157-987-7376-01, 910570090127
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Experience breathtaking ocean vistas from this legally permitted vacation condo at 2947 Kalakaua Ave #201A. The spacious layout includes a large bedroom with two comfortable Queen beds fitted with high-grade memory foam mattresses, ensuring a restful sleep. Registration Number: TA-182-683-0848-01, GE-182-683-0848-01, 310320290007
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
Experience stunning oceanfront living in this beautifully appointed resort condo. The unit features a private lanai with direct views of the ocean, perfect for relaxing or dining outdoors. Inside, enjoy a fully equipped kitchen and a washer/dryer for convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Relax on the balcony of this spacious 7th-floor suite at Ilikai Hotel, offering breathtaking views of the Duke Kahanamoku Lagoon and the Pacific Ocean. The suite features a comfortable Queen bed and a Queen sofa bed, comfortably accommodating up to 4 guests. Registration Number: 260100070097, GE-128-100-9664-01, TA-128-100-9664-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Come visit the charming Westin Nanea Villa located on the stunning Kaanapali Coast of Maui. This spacious two-bedroom unit offers all the comforts of home, including a full kitchen, living room, dining area, and a balcony or patio to enjoy the scenic views.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Located directly on the water, Makana Kai, meaning gift of the sea, has been a cherished family-owned property for over 40 years, offering a warm, well-furnished retreat that feels like visiting a beloved home. Recently renovated, the house features a modern kitchen with new appliances, comfortable beds, fans, stylish furnishings, and a rebuilt Ipe lanai that extends the living space outdoors.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to Mai Tide, a stunning oceanfront guest home nestled on the North Shore of Oahu in Hau'ula. This beautiful property offers over 30 feet of direct ocean frontage, providing breathtaking sunrises and majestic mountain views that create a perfect tropical retreat. Registration Number: TA-199-128-8320-01, 540120440000, GE-199-128-8320-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hauula
Immerse yourself in the laid-back island lifestyle at this luxurious 1-bedroom, 1-bathroom condo located in the desirable Mahana at Kaanapali complex. This ground-floor corner unit comfortably accommodates up to 4 guests, offering a blend of modern elegance and Hawaiian charm.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This charming, brand-new vacation rental in 2024 offers a top-floor, corner unit at Kona Mansions, located in Building A—the best part of the development. The one-bedroom, one-bathroom unit provides a cozy yet comfortable space with stunning ocean views visible from the moment you step outside.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Stay in this cozy 550 sq-ft condo just a minute's walk from Honokowai Beach Park, perfect for beach lovers and water sports enthusiasts. The unit offers a comfortable living space with a sofa bed, a fully equipped kitchen, and modern amenities like internet, cable TV, and a DVD player.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Nani Kai Hale 301, a charming 1-bedroom, 2-bath condo nestled right on the stunning Pacific Ocean. This inviting space offers breathtaking views of Haleakala and Molokini from the air-conditioned living room, creating a perfect backdrop for your Hawaiian escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Discover the perfect Maui getaway at Maui Sunset, an oceanfront condo resort in sunny Kihei. This spacious 1-bedroom, 2-bath condo comfortably sleeps five with a sofa sleeper and wall bed, offering a cozy yet luxurious stay. The large private view deck provides breathtaking views of the ocean and lush tropical gardens, ideal for relaxing morning coffees or sunset evenings.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
5-bathroom condo showcasing plantation-style architecture. The property features two master bedrooms with ensuite bathrooms, modern upgrades including A/C, Marbau flooring, granite countertops, Bosch appliances, and a washer/dryer for convenience.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
Discover the true beauty of Molokai from this stunning oceanfront condo located in Kepuhi Beach Resort. Wake up to breathtaking views of the North Pacific, with the sound of gentle waves and a refreshing breeze setting the perfect atmosphere for your island escape.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maunaloa
Experience the perfect Maui escape at #315 Hale Ono Loa, a fully equipped beach condo designed for comfort and relaxation. This inviting unit features a spacious, huge lanai with breathtaking direct ocean and outer island views, ideal for watching stunning sunsets and playful whales cavorting in the distance.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Imagine waking up to the gentle sway of palm trees and the soothing sounds of the ocean, creating a perfect tropical escape. The Westin Kaanapali Ocean Villas offers an extraordinary vacation experience with stunning views of the Pacific and access to a variety of cultural and outdoor activities.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina, Maui
This beautifully remodeled studio offers stunning mountain, city, and partial ocean views from the 20th floor, providing a perfect island getaway for two. Located in The Hawaiian Monarch, a secure building with modern amenities, the unit features a queen-sized bed, smart TV, air conditioning, Wi-Fi, and a kitchenette equipped with a hot plate, microwave, mini fridge, and basic cookware. Registration Number: 1260140320370, TA-041-753-1904-01, GE-041-753-1904-03
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Aloha! Discover the luxurious Kaanapali Beach Club, now part of Hilton, offering spacious one-bedroom suites that sleep four with privacy. Upgrades to oceanview rooms are available seasonally and based on availability, providing breathtaking vistas of Lanai and Molokai.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> lahania
Escape to paradise with this beautifully renovated 4-bedroom villa at Villas at Mauna Kea #40, situated on Hawaii's stunning Big Island. Spanning 2,683 sq ft of elegant living space, the villa features a spacious 1,279 sq ft lanai perfect for outdoor lounging and dining.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Waimea
Relax in this spacious oceanfront 2-bedroom, 2-bath condo located in the renowned Keauhou resort area. The property offers beautifully landscaped grounds, covered parking close to your unit, and access to three swimming pools with spas, including one oceanfront.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the ultimate Maui vacation in this recently renovated oceanfront condo at Napili Point. The unit features all-new tile flooring, fully remodeled bathrooms, and modern kitchen amenities, creating a luxurious and comfortable atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Napili, Lahaina
Experience the comfort and beauty of this legally permitted vacation rental at 2947 Kalakaua Ave #802. This high-floor 1-bedroom condo offers breathtaking ocean views from the main living area and bedroom, allowing you to enjoy the surf, cityscape, park, and mountains all from the comfort of your home-away-from-home. Registration Number: TA-027-297-7920-01, GE-027-297-7920-01, 310320290035
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Immerse yourself in the warm aloha spirit at this charming studio apartment located at 134 Kapahulu Ave #520, Waikiki. This legally permitted rental offers a comfortable, well-furnished space perfect for your island escape.Registration Number: 260270310078, GE-046-744-7808-01, TA-046-744-7808-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Casa 204 is a beautifully remodeled second-floor condo that offers a perfect blend of comfort and Hawaiian charm. Updated in April 2019, the unit features brand-new flooring, modern furniture, a fully equipped kitchen, new bathrooms, fresh paint, and the latest appliances, ensuring a stay that’s both comfortable and stylish.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the perfect Kaua'i retreat in this beautifully renovated 2-bedroom condo at Makahuena. With stunning ocean views from your private lanai, this coastal escape offers a bright, open floor plan and modern comforts.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> KOLOA
Experience the beauty of Maui from Sugar Beach Resort 332, a charming beachfront condo offering stunning ocean views and direct access to five miles of pristine white-sand beach. This well-appointed one-bedroom unit is perfect for a relaxing island escape, featuring a private lanai, fully-equipped kitchen, free WiFi, and in-unit washer/dryer for your convenience.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the best of Maui in this beautifully appointed 1-bedroom, 1-bathroom suite at Honua Kai. Offering partial ocean views and majestic mountain vistas, this spacious condo is perfect for a relaxing getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect Maui vacation in this spacious 1-bedroom, 1-bathroom condo that comfortably sleeps up to 4 guests. Located at the AAA-rated Aston Kaanapali Shores, this oceanfront resort offers breathtaking views of the Pacific Ocean and the island of Molokai from your private lanai.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a truly unique and memorable escape at The Ohana House, a beautifully crafted off-grid tiny home nestled on the Kalapana lava beds near Kilauea Volcano. This cozy 2-bedroom retreat offers spectacular views of the volcano and expansive black lava fields, where you can witness stunning double rainbows and enjoy incredible stargazing under clear night skies.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahoa
Konea 734 is a luxurious 7th-floor two-bedroom suite situated in the heart of Honua Kai Resort on Maui. Its private corner location offers breathtaking views of the Pacific Ocean and the West Maui Mountains, creating a perfect setting for a memorable getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the best of Maui at Puamana 272-3, a charming oceanfront townhome offering breathtaking views and direct water access. This spacious two-story residence features over 1,700 square feet of tropical living space, perfect for relaxation and entertainment.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Discover Napili Puamala 128, a beautifully remodeled studio condo perfect for a relaxing Maui getaway. This ground-floor unit features an open-concept layout with modern furnishings, Hawaiian-inspired decor, and a spacious lanai. Accommodating up to 4 guests with a queen bed and a queen sofa sleeper, the condo offers comfort and convenience in a tranquil setting.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to Wailua Bay View 206, a charming beachfront condo on Kauai's Coconut Coast. This spacious one-bedroom unit comfortably accommodates up to four guests, making it ideal for families, friends, or solo travelers seeking a tropical retreat.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Step into this stunning fourth-floor condo at Kamaole Sands and be greeted by breathtaking ocean views from the spacious lanai. The large loft-style layout features vaulted ceilings and a unique spiral staircase, creating an open and airy atmosphere perfect for a relaxing Maui getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate Maui escape in Hokulani 636, a luxurious two-bedroom oceanview corner suite at Honua Kai resort. Spanning 1240 sq. , this spacious condo comfortably accommodates up to six guests with a king bed in each bedroom and a queen-sized convertible sofa in the living area.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Enjoy stunning ocean views from this beautifully furnished Maui condo, Hololani A603. The spacious living area opens to a private balcony where you can gaze out towards Molokai and Lanai, or spot whales during the season from December to April.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the beauty of Maui in this elegant Puamana 185-4, a spacious ocean view home designed for comfort and luxury. From every room, enjoy breathtaking vistas of the Pacific Ocean and lush greenbelt surroundings. The open-plan layout seamlessly connects living spaces, creating a welcoming atmosphere perfect for families or groups.
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Lahaina
Experience the laid-back island vibe in this inviting one-bedroom condo at Kona Bali Kai #171. The beachy decor and wicker furniture create a cozy, relaxing atmosphere. Cool off with ceiling fans and enjoy your favorite shows on cable TV. Stay connected with free WiFi, perfect for planning your Kona adventures or streaming music.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience breathtaking ocean vistas from this spacious ground-floor 2-bedroom, 2-bath condo nestled in a 4-star resort. The private oceanfront lanai offers perfect sunset views and a relaxing retreat. The condo features a fully equipped kitchen and washer/dryer for convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience this beautifully remodeled top-floor condominium located directly across from the renowned Kamaole Beach Park II. The unit boasts cherry cabinetry, quartz countertops, brushed nickel fixtures, and all-tile flooring, ensuring a modern and clean feel.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the pinnacle of oceanfront living at the Aston Mahana at Kaanapali, a luxurious resort nestled on Maui’s beautiful North Ka'anapali Beach. This spacious one-bedroom suite on the 10th floor offers breathtaking Pacific Ocean views and a tranquil setting perfect for a memorable getaway.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to this breathtaking Hawaiian retreat situated on the shores of paradise. This charming house offers spectacular ocean views and an authentic island living experience. Inside, you'll find modern furnishings, spacious interiors, and a fully equipped kitchen, making your stay comfortable and convenient.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Kailua-Kona
This charming condo in central North Kihei offers everything you need for a memorable Maui vacation. The fully equipped kitchen makes dining easy, while the large second-floor lanai provides a perfect spot for whale watching during season, enjoying Maui's spectacular sunsets, or relaxing with a drink and a good book.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Tucked away on 3 peaceful acres of native Hawaiian rainforest, this charming cedar cabin is just 2 miles from Volcanoes National Park, making it an ideal retreat for families and friends seeking tranquility and adventure.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Volcano
Aloha! Discover 'Hale Sweet Hale,' a charming Hawaiian-style oceanfront bungalow located on the southeastern coast of Hawaii's Big Island. This fully remodeled one-bedroom condo offers a perfect blend of comfort, modern amenities, and stunning natural vistas.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Experience breathtaking ocean views from this beautiful 1-bedroom condo located on the top floor of Kona Reef. As an end unit, it features windows in every room, allowing for ample natural light and refreshing breezes. The master bedroom offers comfort with a queen bed, while the living area includes a sleeper sofa, perfect for extra guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the best of Waikiki in this charming 1-bedroom, 1-bath suite located at Waikiki Shore #816 2161 Kalia Rd. Perched on the 8th floor, this homey condo offers breathtaking panoramic views of the Pacific Ocean and the lush greenery of Ft. Registration Number: 260040120074, GE-134-389-2480-01, TA-134-389-2480-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this cozy 480-square-foot beach condo ideally situated near popular water activities like sailing and scuba diving. Whether you're strolling to Kahana Beach in just 7 minutes or driving 4 minutes to Napili Bay, this location offers the perfect base for your Maui adventures.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
This beautifully renovated loft offers a perfect blend of comfort and modern style in a lush tropical setting. Upstairs, the spacious bedroom features a fully renovated ensuite bathroom and garden views, filling the space with natural sunlight.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
