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 14050, 50
Kaanapali Alii 394 offers a luxurious ocean-view experience on Maui's famed Kaanapali Beach. Located on the 9th floor, this spacious two-bedroom condo boasts breathtaking panoramic views of the Pacific Ocean. The open floor plan combines comfort and elegance, featuring modern furnishings, a fully equipped kitchen with granite countertops, and a seamless flow into the dining and living areas.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the comfort and convenience of a spacious 2-bedroom condo at Marriott Maui Ocean Club, perfect for families or groups of up to 8 guests. This rental offers options at the Molokai, Maui, Lanai, Lahaina, and Napili Towers, each with unique amenities to enhance your stay.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Discover this inviting Honolulu condo featuring two bedrooms with comfortable queen beds and a cozy living room with a sofa bed. The space is ideal for families or groups, providing all the essentials for a relaxing stay. Enjoy modern amenities including a washing machine, dryer, high-speed WiFi, and air conditioning, ensuring comfort and convenience during your visit. Registration Number: 260120070002, TA-197-570-8160-01, GE-197-570-8160-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Welcome to Hale Moana (House by the Ocean) nestled within the stunning Kauai Beach Resort! This 5th-floor, renovated suite offers breathtaking panoramic ocean views visible from both queen beds and the private lanai. The resort sits on a lush 25-acre beachfront property, providing easy access to Kauai's North and South Shores, with convenient proximity to Lihue Airport.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lihue
This stunning two-story estate is situated within the gated Ko Olina Kai Resort Community, offering over 2000 sq. ft. of elegant living space. Perfect for vacationers or business travelers, it combines comfort and style with first-class furnishings and timeless décor. Registration Number: GE-168-344-3712-01, 910560050020, TA-168-344-3712-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Discover the beauty of Kailua-Kona with a stay at our charming one-bedroom, one-bath Ohana vacation rental. This cozy coastal retreat offers a peaceful escape for couples seeking an island getaway. Situated in a serene neighborhood, you'll enjoy the gentle ocean breezes and stunning sunsets from your private lanai.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the best of Maui in this fully remodeled luxury condo, completed in March 2024. Featuring two bedrooms with king-sized beds and plush linens, two full bathrooms stocked with complimentary toiletries, and a spacious, open-plan living area, this condo offers a perfect blend of comfort and style.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
"
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this charming studio located on the peaceful shores of Po'pu Beach. Spanning 403 sqft, it offers a comfortable king-size bed, a living area with a pull-out queen sofa, and a fully equipped kitchenette including a 2-burner cooktop, microwave, dishwasher, and full-size refrigerator.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kauai
Experience the vibrant heart of Waikiki from this beautifully updated studio apartment. Located on a high floor, it offers partial ocean and mountain views, providing a serene backdrop after your adventures. The unit features a newly remodeled kitchen with a microwave/convection oven and induction stovetop, making meal prep effortless with stocked spices and modern appliances. Registration Number: TA-017-589-2480-01, GE-017-589-2480-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this beautifully appointed Hawaii condo offering stunning golf course views and luxurious amenities. The unit features a spacious master bedroom with a king bed and a private bath with a large soaking tub and free-standing shower. The second bedroom also has a comfortable king bed, making it ideal for families or friends.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Discover this stunning luxury villa located on the 4th floor of the Beach Tower in Kapolei, just steps from the ocean at Lagoon 2. Recently upgraded in late 2024, this elegant property offers a perfect blend of serenity and convenience. The villa features three spacious bedrooms and three bathrooms, comfortably accommodating up to 8 guests. Registration Number: TA-114-042-5728-01, GE-114-042-5728-01, 190570090196
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Discover a luxurious stay at Marriott Waiohai Beach Club with this beautiful 2-bedroom condo that comfortably sleeps up to 8 guests. Located on the pristine beaches of Hawaii, this oceanview property offers breathtaking vistas and direct access to sandy shores.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Welcome to Breezy Palms, your charming, modernized studio right in the heart of downtown Kailua-Kona! This beautifully updated retreat combines comfort and convenience, making it the perfect base for your Hawaiian vacation. Located just steps away from vibrant shops, delectable restaurants, and scenic waterfront, you'll have easy access to explore everything this captivating town offers.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> KAILUA KONA
Experience the ultimate Hawaiian vacation at Westin Ka'anapali Ocean Resort Villas, a luxurious oceanfront retreat perched on 26 lush acres with breathtaking views of the Pacific Ocean, Lana'i, and Moloka'i islands. This spacious 1-bedroom, 1-bath villa offers a full kitchen, dining area, and a comfortable living room featuring a fold-out queen sofa that sleeps two.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Discover a bright and stylish 2-bedroom condo in the heart of Kapaa Town, Kauai. Newly remodeled with brand-new furniture and decor, this space offers comfort and convenience for your island getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
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 start your Hawaiian adventure in comfort and style.Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
★Free Wi-fi, Free Parking, No Resort Fees★
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this luxurious 12th-floor condo offering stunning balcony views and modern comforts. The condo features carpet and tile flooring throughout, creating a cozy and stylish atmosphere. The fully equipped kitchen includes a fridge, stove/oven, microwave, cookware, and tableware, making meal preparation effortless.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Hale Makolea 23, your perfect Kona Coast retreat. This inviting two-bedroom, two-bath condo is set within a lush tropical landscape, embodying the relaxed spirit of old Hawaiʻi. Located just steps from Kahaluʻu Beach Park, famous for its excellent snorkeling, vibrant marine life, and gentle surf, this residence offers an ideal base for exploring the island’s natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Kona View Estate offers an exclusive luxury vacation experience on the scenic slopes of Hualalai, overlooking Kailua-Kona. The estate spans 3 acres of beautifully remodeled grounds, featuring top-of-the-line amenities and a rich coffee farming history.
1 bedroom
1 bathroom
Sleeps 8
Hawaii> Holualoa
Welcome to your tropical escape in the heart of Lanikai, across the street from one of the most breathtaking beaches in all of world. This licensed short-term rental offers comfort, style, and space. Additional bedrooms are available to sleep up to 16 guests!Registration Number: TA-015-067-6992-01, 1990/NUC-1752, GE-015-067-6992-01, 430080300000
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
Welcome to Moana Vista, your exclusive waterfront haven nestled between Hauula and Punaluu on Oahu's mesmerizing shores. This beachfront paradise boasts a wide array of outdoor amenities, including a beachfront gym, a covered 10-chair gazebo equipped with electricity and lighting, numerous hammocks and patio chairs perfect for relaxing, an outdoor BBQ grill, and three newly-added beachfront hot tubs—one of which is a spacious 7-person spa. Registration Number: TA-002-506-5984-01, GE-002-506-5984-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Hauula
Experience this beautifully remodeled private studio located on Niu Street, offering breathtaking views of the ocean, Diamond Head, and the city skyline. Situated within walking distance to Waikiki's renowned beaches, the Convention Center, Ala Moana shopping center, and a variety of dining options, this cozy space is perfect for both leisure and business travelers.Registration Number: 260140320037, TA-119-707-2384-01 PERMIT NO 73151, GE-119-707-2384-01, TA-119-707-2384-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the ultimate Hawaiian getaway in this beautifully appointed Kaanapali Royal condo, nestled in a tranquil, gated complex on Maui. This tropical paradise offers stunning views of the golf course and ocean from a spacious second-floor unit, accessible by just five steps.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience this beautifully appointed oceanfront home nestled in Kalapaki Bay, offering an ideal blend of comfort and natural beauty. The residence features soaring 12-foot ceilings and expansive wall-to-wall glass sliders that open to a spacious private lanai, providing seamless indoor-outdoor living and stunning bay views.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lihue
Experience the perfect Hawaiian escape in this newly renovated 2-bedroom, 2-bathroom condo situated in the heart of downtown Kailua-Kona at Kona Plaza. The spacious layout features cozy Queen-sized beds in both bedrooms, ensuring restful nights after your daily adventures.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover the charm of island living at Ko Olina Kai 1105F by Gather, a delightful ground-floor 2-bedroom, 2-bathroom condo nestled in the sought-after Ko Olina Resort community. This inviting vacation rental embodies the perfect Hawaii getaway, offering a blend of comfort, style, and convenience.Registration Number: 910560050174, GE-045-720-5248-01, TA-045-720-5248-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Reconnect with loved ones in this spacious family-friendly condo located right across from the Makai Golf Course. The unit features a master bedroom with a king bed, a loft with two full beds, and a comfortable sectional couch, making it ideal for families or groups.
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Princeville
Maile Lei Hale offers an ideal Hawaiian vacation experience with its spacious layout and unique island-inspired decor. This 4-bedroom, 3-bath home provides comfort and convenience, featuring a full kitchen, a refreshing pool, an outdoor shower, a TV room, and a kitchenette for added flexibility. Registration Number: 1370120300000, TA-153-803-4896-01, GE-153-803-4896-01
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Honolulu
Escape to Ridge Villas Fairway View, a comfortable 1-bedroom villa perched above Kapalua Bay Course. This spacious 1200-1500 sqft retreat offers breathtaking fairway views and modern amenities for a perfect getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Summary:
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Escape the hustle and bustle of daily life and unwind in our charming Kailua-Kona Ohana, a delightful 1-bedroom, 1-bathroom vacation home on the beautiful Big Island of Hawaii. This coastal retreat offers a peaceful haven ideal for couples or solo travelers seeking an authentic island experience.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the tranquility of Kanaloa at Kona, a beautiful oceanfront condominium nestled on 16 acres of lush, secluded landscape overlooking Keahuou Bay. This spacious 3-bedroom unit offers a perfect blend of comfort and natural beauty, ideal for families, couples, and outdoor enthusiasts.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Kaanapali Alii 414 offers a luxurious ocean-view vacation experience in Maui's premier Kaanapali Beach area. This spacious ground-floor condo comfortably hosts up to six guests, featuring two bedrooms, a den, and two bathrooms. The well-designed layout provides easy access to the resort’s stunning pools, beachfront grills, and the soft sands just steps from your lanai, allowing you to walk directly onto the beach in 30 seconds.
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Unwind in Honolulu at this welcoming studio nestled on the 8th floor of The Palms. The modern, comfortable space includes a cozy living area with a queen bed and a sofa bed, making it ideal for a peaceful getaway. Guests can enjoy the convenience of amenities like a refreshing pool, reliable WiFi, air conditioning, and a hair dryer, ensuring a comfortable stay. Registration Number: 260120030173, GE-208-244-0704-01, TA-208-244-0704-01
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Kahana Manor 205 offers a delightful retreat with its recent upgrades and scenic partial ocean views. This spacious 2-bedroom, 2-bathroom condo comfortably hosts up to 6 guests, making it perfect for families or friends seeking a relaxed Maui getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Indulge in the vibrant energy and natural beauty of Oahu with a stay at The Modern Honolulu. This stylish, upscale property offers a sleek aesthetic with custom furniture, oversized showers, and terrazzo countertops, perfectly reflecting contemporary elegance. Registration Number: TA-151-395-1558-04, GE-205-637-5678-02, 497388072676
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience timeless charm at The Historic Wailuku Inn, a beautifully restored estate over 100 years old nestled in the heart of Wailuku town. This boutique property seamlessly blends vintage elegance with modern comforts, offering an authentic Maui experience in a charming, intimate setting.
10 bedroom
10 bathroom
Sleeps 20
Hawaii> Wailuku
Located along the scenic Ala Wai Canal, this charming resort offers a unique blend of European-style gardens and the vibrant tropical ambiance of Oahu. Guests can unwind in the outdoor pool, enjoy the serene gardens, or relax in the sauna, creating a peaceful retreat. Registration Number: 540050360006, TA-212-767-6928-01, GE-212-767-6928-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
5 bathroom condo. The bedrooms feature 1 king bed, 2nd bedroom, 2 queen beds, 3rd bedroom 1 queen bed ensuring a pleasant night's sleep for all guests. 2 master bathrooms equipped with hair dryers and showers, getting ready for the day is a breeze.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the ultimate Hawaiian getaway in this stunning Kohala Ranch estate, set on 6 acres with breathtaking ocean and mountain vistas. This newly upgraded home offers a perfect blend of privacy and luxury, ideal for couples and families seeking a relaxing retreat on the Big Island's scenic Kohala Coast.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kamuela
Welcome to Ranch Beach Estate, a hidden gem just steps from Kailua's pristine white sand beaches. Nestled on a coveted loop, this luxurious home offers easy access to local cafes, boutiques, and farmers' markets. A manicured grass driveway with fragrant plumeria and a salty breeze invites you into a private, fully-fenced tropical oasis. Registration Number: 430130190000, TA-129-806-1824-01, GE-129-806-1824-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Step into a world of relaxation, surrounded by swaying palm trees and vibrant hibiscus blooms. Your spacious suite features a full kitchen, separate living room, and private lanai. Refresh yourself in our sparkling outdoor pools, perfect for relaxation or family fun.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Get ready to relax in this 3-bedroom villa in Lahaina. This awesome property features 1 king bed, 2 queen bed, a living room with a sofa bed, heating, WiFi, and a fitness room. With 2 bathrooms, a washing machine, hair dryer, and iron, guests will have everything they need for an enjoyable stay.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the ultimate island retreat in this charming second-floor end unit at Poipu Beach. The condo features a lanai overlooking the lush lawns and the sparkling ocean, perfect for relaxing with a view. Walk-up access offers a quiet, private atmosphere, free from elevators.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to North Shore Beachfront Retreat by Gather, a stunning oceanfront property located in Haleiwa, Hawaii. This spacious 5-bedroom, 4-bathroom home offers a perfect blend of classic island charm and modern comfort, making it an ideal destination for families, friends, or surf enthusiasts seeking a tranquil escape.Registration Number: 610040700000, GE-106-180-8128-01, TA-106-180-8128-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Haleiwa
Welcome to Maui Haven 5201, a stunning, privately-managed ocean view villa within the prestigious Montage Kapalua Bay Resort. 5-bath residence offers 3,532 square feet of luxurious living space, comfortably accommodating up to 8 guests. Recently undergoing extensive renovations, including new furniture, carpets, and modern updates, this villa combines tropical elegance with contemporary comfort.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kapalua
This luxurious Niu Beach Estate offers a private, modern retreat on Oahu's southern shores. With five bedrooms and five and a half bathrooms, this beautifully remodeled plantation-style home blends contemporary design with tropical elegance.Registration Number: TA-067-638-2208-01, GE-067-638-2208-01, 370010390000
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Honolulu
Escape to Maui and stay in this beautifully remodeled 1-bedroom, 1-bath condo located in the heart of Kihei. This island oasis offers convenience and comfort, with everything you need just steps from your door.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
