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 6100, 50
Experience over 1500 sq ft of elegance in Alii 121, a sophisticated 2BR/2BA residence perfect for family gatherings and relaxation. The bright, open floor plan features an upgraded modern kitchen seamlessly connected to a spacious dining area and comfortable living space.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Lahaina
This beautifully renovated oceanfront condo is perched on the top/second floor at the very edge of the Pacific on Kauai's stunning north coast. Guests are treated to breathtaking, panoramic views of the Pacific Ocean from every room, providing a truly immersive experience with the soothing sounds of the ocean and the endless vista of the coast below.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover a serene escape at Maui Kaanapali Villas with this newly renovated studio offering stunning ocean views. The spacious condo features vaulted ceilings, a full modern kitchen, and a large lanai perfect for relaxing or dining outdoors. Designed to comfortably sleep up to four, it includes a plush king bed, a brand-new queen sofa sleeper, and a spacious living area with a large smart TV for entertainment.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Experience the ultimate Hawaiian getaway in our legally permitted 2-bedroom penthouse atop the Diamond Head Beach Hotel & Residences. This luxurious suite offers breathtaking 240° views of the ocean and the iconic Diamond Head crater, providing a truly mesmerizing backdrop for your vacation. Registration Number: 310320290056, GE-210-144-0512-01, TA-210-144-0512-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to our beautiful Maui Vista condo, a perfect retreat offering breathtaking ocean views from the top floor. This fully remodeled 2-bedroom, 2-bath unit comfortably sleeps 4-5 guests, ideal for families or couples seeking a luxurious yet relaxed vacation.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Escape to this beautifully decorated third-floor condo (#365) at The Whaler, offering everything you need for a memorable vacation. Inside, you'll find a comfortable Cal King bed praised by guests, a 50" LCD TV with HBO and Netflix, and a fully equipped kitchen perfect for preparing meals or snacks.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover the charm of this lovely one-bedroom, two-bath condo at Paniolo Hale B2 on Molokai's serene west end. The open beam ceilings and hardwood floors create a warm, inviting atmosphere. The ground floor features a spacious living area with treetop views, a large screened lanai perfect for relaxing outdoors, and a fully-equipped kitchen with newly tiled surfaces.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Maunaloa
Discover the perfect island getaway in this beautifully appointed second-floor condominium located just 300 feet from the stunning beaches of Poipu on Kauai's sunny South Shore. The unit offers breathtaking 180-degree views from the bedroom, encompassing ocean to mountain vistas, all while being lulled to sleep by the soothing sounds of waves and the gentle trade winds through nearby palm trees.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Welcome to Mauna Palm by KEVALA TERRA, your ideal Hawaii vacation home located within the prestigious Mauna Lani Resort. This spacious 3-bedroom, 2-bathroom condo offers a perfect blend of luxury, comfort, and convenience, making it an excellent choice for couples, families, or friends seeking a memorable getaway.
4 bedroom
2 bathroom
Sleeps 7
Hawaii> Waimea
Experience the charm of Malolo Cottage, a delightful Hawaiian-style studio just a short walk from Hanalei Bay Beach. The cottage features vintage Koa furniture, a comfortable Queen bed, and a double bed behind a folding screen, accommodating 2-4 guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hanalei
Konea 545 offers a luxurious two-bedroom ocean view suite in the desirable south-facing Konea interior of Honua Kai resort. Spanning 1315 square feet, this spacious condo comfortably accommodates up to six guests with two king-sized beds and a queen-sized convertible sofa.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to this luxurious Mauna Lani Village townhouse, professionally managed by Kona Coast Vacations. Nestled within a gated community on the Kohala Coast, this spacious retreat offers breathtaking golf course views and exclusive access to the Mauna Lani Beach Club.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kamuela
Located on Kauai's tranquil North Shore in Ha'ena, the Mermaid House is a distinctive vintage redwood home designed by renowned yacht racer Peggy Slater. This non-smoking, pet-free residence embodies a passion for the sea and Hawaiian culture, offering a unique and authentic tropical experience.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Haena
Aloha and welcome to Kona Hale's Garden Level, a beautifully remodeled retreat nestled among fragrant fruit trees, vibrant flowers, and lush tropical foliage. This spacious, turnkey property offers spectacular panoramic ocean views from its breezy elevation, perfect for relaxing after a day exploring Kona's renowned beaches and scenic coastlines.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the beauty of Kauai from this beautifully remodeled two-bedroom, two-bath beachfront condo at Kaha Lani, nestled on the island’s Coconut Coast. This unit offers spectacular panoramic views of miles of sandy beach and surf from the living room, dining area, master bedroom, and expansive lanai, providing a perfect setting for relaxation and memorable sunsets.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lihue
The Hokulani 420 at Honua Kai offers an expansive two-bedroom corner suite with breathtaking partial views of Maui's iconic blue Pacific Ocean and the majestic West Maui Mountains. Spanning 1240 sq. , this spacious residence comfortably hosts up to six guests, featuring a plush king bed in each bedroom and a queen-sized convertible sofa in the living area, providing ample space for relaxation and entertainment.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Casa de Emdeko 116, a beautifully renovated one-bedroom ground-floor condo nestled within a historic, gated oceanfront complex in Kailua-Kona. This charming, retro-inspired unit combines modern conveniences with the classic aloha vibe of the 1960s, offering a perfect retreat for your Hawaiian vacation.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Relax at Whispering Palms, a spacious ground-floor condo offering a true Hawaiian retreat. This 2-bedroom, 2-bath unit features two king beds that comfortably sleep four guests, with an option for a full sleeper sofa to accommodate up to six. Situated on an ocean bluff at the picturesque Alii Kai resort, the condo provides breathtaking sunrise and sunset views, along with gentle trade breezes.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Discover this exceptional tandem package at Honua Kai, where two adjacent two-bedroom corner suites are combined to create a spacious 4-bedroom retreat on Maui's beautiful North Beach. Spanning over 2,320 sq ft of interior space, this property offers breathtaking Pacific Ocean vistas from a massive 500+ sq ft balcony, perfect for sunset dinners or relaxing with loved ones.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Escape to Sunset Suzy's Tropical Retreat, a charming cottage nestled on a lush one-acre lot in Hawaiian Paradise Park. This private, secluded hideaway offers a peaceful environment surrounded by fruit trees, vibrant tropical birds, and the soothing sounds of nature.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Escape to this stunning oceanfront condo at Kona Bali Kai 401, professionally managed by Kona Coast Vacations. Located on the top floor at the southern end of the complex, this beautifully remodeled 3-bedroom, 2-bath unit offers breathtaking panoramic views of the Pacific Ocean and overlooks the famous Banyan's surf spot.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
This exquisite 3000-square-foot Hawaiian hale offers a blend of elegance and island-inspired design, set amidst a lush tropical garden with stunning views of the golf course and Kohala Mountains. The property provides complete privacy and comfort, featuring three bedrooms with king beds, four full bathrooms, and a versatile office that can convert into a fourth bedroom with a queen bed.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Welcome to the ultimate Maui vacation experience in Lahaina! This stunning oceanfront mansion offers unparalleled luxury and space, perfect for multi-generational family reunions, large gatherings, or corporate retreats. Spanning nearly 12,000 square feet, the home features 10 spacious master bedrooms, each with private bathrooms, large sitting areas, and high-end amenities.
11 bedroom
11 bathroom
Sleeps 32
Hawaii> lahina
Discover the beauty and tranquility of Hawaii at Mauna Lani Fairways 1101, a beautifully appointed two-story corner townhome within the prestigious Mauna Lani Resort. Known as KA NOHONA KAI LA'AKEA, meaning “seaside home of sacred light and happiness,” this residence offers an exceptional blend of luxury, comfort, and breathtaking views.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kamuela
Welcome to the Royal Kahili, your perfect home-away-from-home for a seasonal stay of 3-4 months. This beautifully renovated 2-bedroom, 2-bath condo offers a blend of comfort and style, making it an ideal retreat in paradise. Recent updates include a brand-new kitchen and guest bath, fresh paint, new baseboards, modern light fixtures, and ceiling fans, creating a bright and inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Kona Makai 6201, a charming two-bedroom, two-bathroom oceanfront condo where the wrap-around lanai provides some of the best views on the island. Located less than a mile from downtown Kailua-Kona, this unit offers easy access to the vibrant local scene, including restaurants, shops, historic sites, and cultural landmarks.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover the hidden gem of Waikiki Beach with this one-of-a-kind privately owned condo offering direct access to the world-famous sand and surf. Unlike other properties, this residence is not just near the beach—it is situated directly on it, providing breathtaking ocean vistas from every window and door.Registration Number: GE-003-752-0384-01, TA-003-752-0384-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
Immerse yourself in contemporary style at this private Pahoa escape, a home once featured in Dwell Magazine. The property boasts a sleek design with an expansive 500-foot deck perfect for dining and relaxing beneath the lush monkeypod trees. The open-concept living area, accessible via a charming walking bridge, offers a welcoming space to unwind while soaking in views of the surrounding nature.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Pahoa
Welcome to your ideal Kauai retreat at Prince Kuhio #112, perfectly situated on the sunny south shore. This charming one-bedroom ground-floor unit offers stunning open views of Prince Kuhio Memorial Park and the ocean, providing a serene backdrop for your vacation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
This beautifully renovated condo offers a perfect blend of comfort and luxury on the world-famous Kaanapali Beach. Recently upgraded with new tile floors, modern cabinetry, appliances, and stylish furnishings, this unit provides a contemporary and inviting atmosphere.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to our stunning oceanfront condo nestled on the sunny coast of Kihei, Maui. This recently remodeled, ground-floor unit offers breathtaking views of the ocean, West Maui Mountains, and mesmerizing sunsets. Perfect for couples, families, or seniors, the condo provides a comfortable and convenient retreat just steps from the beach.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This oceanfront residence in Hawaiian Paradise Park offers a perfect blend of privacy, comfort, and stunning natural beauty. Situated on a half-acre of lush landscaped land, the home is set just back from the cliffs, providing unobstructed views of the sapphire-blue Pacific Ocean and vibrant sunrises year-round.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Keaau
Experience the ultimate beachfront stay in Kihei with this beautifully furnished condo offering direct access to Kamaole 1 and 2 beaches. The property features a fully equipped kitchen, a spacious master bedroom with a king bed, and a second bedroom with a double and twin beds, making it perfect for families or groups.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
The Kona Cliff House at Pebble Beach offers an unmatched oceanfront experience on the Big Island. This fully renovated penthouse boasts spectacular 180-degree coastal views from every room, making it ideal for romantic getaways or adventure seekers.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
Our unit at Kihei Kai Nani Resort offers a peaceful retreat just across from the highly-rated Kamaole Beach II. This "Hotel" designated property features a quiet, ground-floor location in building 10, unit 166, with a spacious yard and garden view.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Kihei, Maui
Welcome to Hale Nalani, a stunning single-level vacation home nestled in the prestigious Princeville resort community on Kauai. 5-bath residence offers a perfect blend of privacy, comfort, and island charm. The walled courtyard provides a secluded oasis, ideal for relaxing by the 40-foot lap pool or enjoying outdoor dining under the tropical skies.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Experience the ultimate Hawaiian getaway in our luxurious two-story villa nestled within Waikoloa Beach Villas. Spanning 1,585 square feet, this spacious home offers privacy and comfort for your entire family or group. The main level boasts a bright, open living area with soaring two-story windows and vaulted ceilings, creating an airy and inviting atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Discover the comfort and beauty of Maui at the Maui Kamaole condo, a luxurious low-rise townhouse-style complex set on 23 tropical acres with stunning ocean views. This spacious 2-bedroom, 2-bathroom unit is ideal for families or two couples looking for a relaxing getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Kololia Living, a stunning and private 3-bedroom, 2-bath residence situated on a third of an acre in a peaceful neighborhood just minutes from Kona's beautiful beaches and scenic walking trails. The property boasts lush, tropical landscaping dotted with various fruit trees, creating a tranquil oasis.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover this beautifully updated 36th-floor end unit at Waikiki Banyan, offering breathtaking panoramic views of the Pacific Ocean, Waikiki, Diamond Head, and Kahala to Koko Head. ft. plus a private lanai, this modern air-conditioned condo features tasteful Hawaiian touches and all the comforts needed for a memorable stay.Registration Number: TA-088-347-5968-01, GE-088-347-5968-01, 260250050403
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Step into your own piece of paradise at this stunning oceanfront condo in Princeville’s coveted Pu'u Poa development. Featuring two bedrooms and two bathrooms, this residence blends thoughtful design with luxurious finishes that complement the breathtaking panoramic ocean views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
then there are "OCEAN VIEWS"! This stunning condo, unit #218 at the Kaha Lani Resort, is situated on Kauai's picturesque east shore, known as the 'Royal Coconut Coast'. Being the closest building to the ocean, this unit offers arguably the best panoramic views on the property, with nothing between your lanai and the vast ocean horizon.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
Experience the perfect blend of luxury and privacy at this spacious Kihei condo located within Maui Kamaole. Just minutes from the stunning Kamaole beaches, including the popular Kamaole Beach Park III across South Kihei Road, this property offers easy access to some of South Maui�s best swimming, snorkeling, and sunbathing spots.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to our air-conditioned Kauai Paradise condo, located on the stunning south shore in Poipu. Recently renovated in August 2015 with a complete kitchen and bathroom update, and installed air conditioning in September 2021, this unit offers comfort and modern amenities on Hawaii's Garden Isle.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Discover this charming and recently remodeled 1-bedroom condo at Kihei Bay Vista, Maui. Located just steps from the ocean, this quiet upstairs unit offers a spacious living area with vaulted ceilings and modern updates. The condo features a fully equipped kitchen with new appliances, custom cabinetry, and stunning granite countertops, perfect for preparing meals after a day on the beach.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Kamaole Sands #4409, a beautifully decorated 2-bedroom condo nestled on 15 tropical acres just across from Kamaole Beach Park III in Kihei. Step inside and be greeted by vibrant tropical accents that set the tone for your island escape.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> KIHEI
Welcome to your perfect Hawaiian retreat in Ekahi Village! This beautifully remodeled ground floor unit, renovated in 2015, offers a seamless blend of comfort and convenience. If you're seeking the shortest, most level walk to the beach, condo 21-C is an ideal choice, situated on a hillside with easy access.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover resort-style living in this beautifully maintained Kona condo set amidst lush tropical gardens. Located just an 8-minute walk from vibrant Kona town, it offers easy access to dining, shopping, and endless activities. Positioned across from Royal Kona Resorts and the renowned Don The Beachcomber bar, famed for stunning sunsets and Mai Tais, this location truly can't be beaten.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kailua-Kona
This stunning three-bedroom suite is perfectly positioned at Honua Kai, offering partial views of the Pacific Ocean and a lush tropical environment. Located just steps from Maui's pristine shoreline, it provides a luxurious and comfortable retreat for up to eight guests.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Step into a piece of Hawaiian history with this private villa once reserved for Hawaiian chiefs. Ample outdoor space offers a covered lanai, lush trees, and vibrant flowers, creating a serene tropical retreat. The scenic area behind the villa features open space filled with singing birds, ensuring privacy and tranquility.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Kailua Kona
