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 4000, 50
Come enjoy a fabulous time in an oceanfront property in Kailua-Kona, Hawaii. This 2-bedroom, 2-bath home is located at the premier Ali'i Villas complex. Recently remodeled from top to bottom, the decor features cool coastal tones that evoke a relaxing vibe.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to SunsetYards Beach House, a charming 2-bedroom retreat perched on Sunset Point offering stunning ocean vistas and tranquil natural scenery. This tropical haven is perfect for a relaxing escape on Oahu's North Shore. Located just a 7-minute drive from the world-famous Banzai Pipeline and only 15 minutes from Turtle Bay Beach, you’ll have easy access to top surf spots and pristine sands. Registration Number: GE-151-548-3136-01, TA-151-548-3136-01
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Haleiwa
Discover this stunning corner villa nestled inside the Beach Tower at Ko Olina, offering unrivaled proximity to the beach with just a 20-foot walk to the lagoon. This spacious, newly refurbished condo features a warm Koa wood design, top-of-the-line furnishings, and modern electronics, creating a perfect retreat for families or friends seeking luxury and comfort. Registration Number: TA-134-204-4160-01, GE-134-204-4160-01, 1910570090191
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Experience the perfect Maui getaway at Happy Pappy's Papaya Pad, a beautifully renovated condo just 300 feet from the sandy beach, ideal for water sports and beach walks.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate beachfront retreat in this beautifully remodeled 5th-floor condo on Kaanapali Beach. Suite 551 offers a spacious and comfortable stay with a king-size bed and a queen convertible sofa, accommodating up to 4 guests. The unit features a private wrap-around balcony with breathtaking ocean views, perfect for morning coffees or sunset dinners.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to the Sea of Love, Kona’s premier oceanfront vacation home, perfect for families and groups seeking a memorable Hawaiian getaway. This spacious 4-bedroom home sits on over a third of an acre, offering stunning views, a private pool, and a small sandy beach with a lush green belt.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua-Kona
Experience the ultimate Maui getaway at Maui Kai Unit 905, a spectacular oceanfront condo offering panoramic views of Lanai and Molokai from the 9th floor. The spacious 800 sq ft unit includes a fully stocked kitchen with stainless steel appliances and granite countertops, a plush king-size bed, a pull-out sofa, and two twin beds on the lanai that can be enclosed as a second bedroom.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Lahaina
Experience the ultimate Maui getaway in this magnificent 7th-floor end unit at Royal Kahana. Recently renovated from the studs up, this condo features new furnishings, modern barn doors, sleek ceilings, updated bathrooms, a gourmet kitchen, and stylish flooring throughout.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Kauai getaway at Hanalei Bay Resort, a luxurious beachfront retreat on the island's North Shore. This spacious 2-bedroom, 2-bathroom condo offers breathtaking ocean and mountain views, perfect for families or groups up to 8.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Princeville
Welcome to Puako Kai Hideaway, a beautifully renovated vacation home nestled in the charming Puako area of Hawaii. Ideally located just minutes from world-class beaches, surf spots, scuba diving, and snorkeling sites, this home offers the perfect base for your island adventures.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimea
Welcome to Sacred Peaks Getaway, a newly renovated and tastefully designed condominium that comfortably accommodates up to eight guests. This spacious vacation rental offers three bedrooms, providing ample space for your group. The guest bedroom downstairs features two cozy twin beds, ideal for children or additional guests.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa
Experience the perfect Hawaiian retreat at our charming Poipu Kai Regency condo, ideally situated on the greenbelt in Building 5, just steps from the beach trail. This light-filled, corner unit offers a comfortable and versatile living space with modern updates and thoughtful amenities.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Escape to Hawi Hideaway, a charming A-frame home tucked away in a peaceful cul-de-sac just steps from the historic town of Hawi. Surrounded by lush trees and a vibrant garden, this property offers a true Hawaiian retreat with the sounds of roosters and coqui frogs providing a natural soundtrack.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Hawi
" Recently renovated, this luxurious condo offers three private lanais, four bedrooms, five bathrooms, and a private den, comfortably sleeping up to 10 guests. The remodel features zoned air conditioning with hospital-grade HEPA filters and UV lights, ensuring a clean and healthy environment.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Makena, Maui
Experience luxury in this beautifully renovated condo on the golf course with breathtaking ocean views. The interior features high-end finishes, Hawaiian artwork, a custom kitchen, and modern appliances. Relax on the lanai with a private gas grill or dine inside at the elegant table.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the best of Maui at Noelani 112, a beautifully decorated studio condo located on the first floor of a boutique resort. This spacious unit comfortably accommodates up to 4 guests with a luxurious king-size bed and a queen sleeper sofa, making it ideal for couples or small families seeking a relaxing island escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Turn moments into memories at the iconic White Sand Village unit #301. This beautifully remodeled and refurnished condo offers everything a luxury traveler desires. Featuring a front-row view of Magic Sands Beach from a spacious corner unit with a large wrap-around lanai, this home is a slice of paradise.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Ho'okipa Rose! This peaceful, tropical 2-bedroom, 2-bath cottage offers a serene retreat in the quaint town of Haiku on Maui. Spanning 1000 sq. , the home is nestled on a private two-acre property, providing a tranquil escape from busy tourist areas.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Haiku-Pauwela
Welcome to your ultimate Maui getaway at the Kaanapali Alii Resort, where luxury meets breathtaking ocean vistas.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this beautifully remodeled two-bedroom, two-bath private condo located on the lush golf course of Kilauea. Designed with comfort and elegance, it offers an open living space filled with refreshing tradewind breezes and a spacious covered lanai perfect for relaxing and enjoying the scenery.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to your peaceful Hawaiian escape, a beautifully designed two-bedroom home nestled in a quiet, lush enclave along the coast. Crafted with high-end finishes and artistic touches, this retreat offers a perfect blend of comfort, natural beauty, and tranquility.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Pahoa
Welcome to your perfect Maui getaway at Sugar Beach Resort! This direct oceanfront condo offers breathtaking panoramic views of the ocean, beach, and beyond, providing a truly immersive Maui experience. With a prime location right on the sand, you'll enjoy 24/7 soothing ocean waves and the chance to witness whale watching during season from your own front row seat.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Ko Olina Beach Villas B608 by Gather, a stunning sixth-floor beachside retreat nestled within the tranquil Ko Olina Resort. This elegant two-bedroom, two-bathroom villa comfortably accommodates up to four guests seeking a peaceful Hawaiian escape. Registration Number: 910570090217, GE-116-164-6080-01, TA-116-164-6080-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Experience a vibrant slice of paradise at Kona Country Club Villas 305, managed by Kona Coast Vacations. This colorful condo offers breathtaking views of the ocean and the prestigious Kona Country Club golf course from almost every room, including the lanai, living area, kitchen, and primary bedroom.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover the ultimate Hawaiian getaway at Hapuna Beach Residences C22, a luxurious oceanfront condo in the iconic Mauna Kea Resort. Spanning 1,597 square feet of elegant interior space plus a generous 1,059 square foot outdoor lanai, this 2-bedroom, 2-bath unit offers breathtaking ocean, coastline, and sunset views from nearly every room.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Kamuela
This tandem pairing combines two adjacent 2BR/2BA partial oceanview suites at Honua Kai, creating a luxurious four-bedroom retreat with breathtaking views of the Pacific Ocean and the West Maui Mountains. Guests will appreciate the modern, fully-equipped kitchens in each suite, allowing culinary creativity and convenient meal prep.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Experience the comfort of this luxurious single-story condo nestled in the heart of Poipu, surrounded by the scenic Kiahuna Golf Course and offering breathtaking mountain vistas.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Discover this stunning high-end 4-bedroom plus bonus loft vacation home nestled in a secure gated community. Designed for comfort and entertainment, the property comfortably sleeps 12 and features a large landscaped yard with sweeping ocean views.
5 bedroom
7 bathroom
Sleeps 12
Hawaii> Kailua-Kona
Welcome to your spacious and elegant vacation rental, the perfect retreat for those seeking a relaxing getaway with beautiful views of the golf course. We look forward to welcoming you soon!
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Located within the prestigious Honua Kai Resort in the Konea Tower, this spacious 6th-floor studio offers spectacular ocean views of Kaanapali Beach. Perfect for a memorable Maui getaway, the condo combines elegance and comfort, featuring a luxurious king bed and a queen sleeper sofa, all topped with high-thread-count cotton linens for a restful sleep.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience the perfect Hawaiian retreat in this beautifully furnished oceanfront condo overlooking Kailua Bay. Location is unbeatable, with direct views through towering palms to watch dolphins, whales, parasailers, surfers, and breathtaking sunsets from your private lanai.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Experience the perfect Hawaiian retreat at Mauna Lani Maluhia #420, a luxurious vacation home nestled on lush grounds within the KaMilo resort community. Just half a mile from Puako Petroglyph Park, this stunning property offers a blend of comfort and convenience, with easy access to beautiful beaches, world-class snorkeling, golf courses, tennis centers, and local dining and shopping options.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Escape to paradise in this elegant third-floor condo at Maui Sunset, located in Kihei. The unit offers breathtaking ocean views right from your balcony, where you can enjoy the serene beauty of the Pacific. Nestled against lush landscaping and the largest pool in Kihei, this condo combines comfort and convenience for an unforgettable Hawaiian vacation.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
5-bath house nestled within a secluded gated community on Ali'i Drive. With only 16 homes in the community, you'll enjoy tranquility and privacy during your stay.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the ultimate Hawaiian getaway at Mana Kai #811, an oceanfront condo located on Keawakapu Beach in Kihei, Maui. This spacious 767 sq ft two-bedroom, two-bath unit offers breathtaking coastline and island views, perfect for making unforgettable memories.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Located on Kauai's lush North Shore, Anini is a paradise for travelers seeking the untouched natural beauty of Hawaii. The quiet waters of Anini Beach are protected by a large outer reef, making them ideal for swimming, snorkeling, fishing, kayaking, windsurfing, and sunbathing.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Kilauea
Kolea 9B is a beautifully redecorated villa showcasing breathtaking tropical views of the ocean, mountains, and lush palm trees. Its prime location provides easy access to the Kolea Beach Club, making it a perfect spot for relaxation and adventure.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Discover this stunning luxury 3-bedroom, 2-bathground floor villa located in the exclusive Kolea at Waikoloa Beach Resort on Hawaii's beautiful Kohala Coast. This airy, light-filled end-unit features sophisticated tropical Asian decor, numerous windows, and state-of-the-art amenities, creating a perfect retreat for families and travelers seeking comfort and style.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Waikoloa
Brought to you by Abita Luxury Rentals, Mauna Lani Terrace G201 offers a spacious, furnished one-bedroom condo with two full baths, situated on the second floor for stunning ocean, white water, and Maui views. The expansive lanai and wet bar are perfect for outdoor entertaining, complemented by floor-to-ceiling mahogany shutters, teak cabinets, 9’3” ceilings, and built-in cabinets in the great room, all within quiet concrete construction.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Waimea
Discover Wailea Ekahi #14D, a beautifully remodeled ocean view condo nestled in the heart of Wailea's Lower Village. This second-floor residence offers breathtaking panoramic vistas of the Pacific Ocean, making it an ideal retreat for those seeking tranquility and natural beauty.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
Welcome to Keauhou Kona Surf & Racquet Club #7-303! This exquisite top-floor condo offers a luxurious and spacious retreat with 1,384 sq ft of interior space, featuring 3 bedrooms and 2 bathrooms. The unit boasts vaulted ceilings, a large lanai with beautiful golf course views, and a thoughtfully designed interior with custom features throughout.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience absolute serenity at this beautifully renovated Maui condo, located in the luxurious Maui Kamaole complex on the south end of Kihei. This spacious, air-conditioned two-story unit comfortably sleeps six, making it ideal for family getaways or group vacations.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Hillside Perfection on the Greens at Golf Ridge Homes, your ideal fully furnished executive retreat in central Honolulu. 5-bath home offers the perfect blend of comfort, privacy, and stunning views, making it perfect for families, business travelers, or construction teams. Registration Number: 110120340002, TA-040-977-2032-01, GE-040-977-2032-01
5 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Waipouli Beach Resort H202, your perfect island retreat nestled in the heart of Kauai. This beautifully appointed condo offers a seamless blend of comfort and convenience, placing you within walking distance of pristine sands, rolling waves, vibrant shops, and delicious restaurants.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Nestled in an unbeatable location next to the iconic 1 Hotel Hanalei Bay, Hanalei Bay Villa #1 combines comfort, convenience, and natural beauty for an unforgettable island getaway. The villa's sunset views are renowned, with many claiming they are some of the most beautiful in the world.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to our stunning, newly renovated 2-bedroom, 2-bathroom beachfront home in Maui. This end-unit offers unobstructed panoramic views of Kaho’olawe Island, Molokini Crater, and the West Maui Mountains from the lanai, living room, and dining area.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to our charming Kona Tropical Retreat nestled in Alii Villas, offering the perfect mix of comfort and island living. Located on the second floor and shaded by lush palm trees, this rental provides a refreshing breeze and a serene garden view from your private lanai—ideal for morning coffees or sunset dinners.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Kai Cottage, a charming coastal retreat nestled in the quiet Pauka'a neighborhood just minutes north of downtown Hilo. Built in 1966 during the plantation era, this cottage is designed to maximize comfort and airflow, featuring elevated construction that takes advantage of Hawaii’s breezy trade winds, ventilation windows, and ceiling fans to keep you cool year-round.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hilo
Located in the highly sought-after Kolea community, this spacious 2-bedroom, 2-bath luxury penthouse offers stunning partial views of Anaeho'omalu Bay. Being an end-unit, it benefits from abundant natural light and cool breezes from three sides, creating a bright and airy ambiance.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to Fern Cottage, a charming and fully permitted vacation home nestled across from Volcano Village and just minutes from Hawaii Volcanoes National Park. Designed to be your perfect home away from home, this cozy yet spacious 900 sq ft house offers comfort and privacy in the lush rainforest environment of Hawai'i.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Volcano
