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 15250, 50
Experience island living in this spacious two-story condo located in Poipu Beach. The property features a bright and airy design with tropical accents, wood floors upstairs, and tile downstairs. The living areas are upstairs, offering stunning ocean views, while the bedrooms and baths are downstairs for privacy.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Welcome to Waikiki Banyan #3508-T1, your perfect Hawaiian escape! This fully-furnished 1-bedroom, 1-bath condo offers 533 sq ft of comfortable interior space plus a private lanai with stunning views of the Pacific Ocean and Waikiki cityscape. The living area features a queen-size sleeper sofa, a 46" Smart TV, tile flooring, and access to a bathroom with a walk-in shower. Registration Number: 260250050396, TA-160-591-6160-01, GE-160-591-6160-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this charming guest loft ideally situated in Kona, Hawaii, offering a perfect blend of comfort and convenience for your island adventure.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the luxury of Marriott's Ko Olina Beach Club, nestled on Oahu's stunning western coast. This upscale resort offers a tropical oasis with seven blue lagoons, lush palms, and native flora. Guests enjoy access to four pools, a pool bar, restaurants like Longboards Bar & Grill, a fitness center, and a marketplace for essentials. Registration Number: GE-205-587-5495-02, 497365472971, TA-151-395-6452-05
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
Hokulani 304 Ocean View 3 Bedroom 3 Bath
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Escape to our luxurious Kapalua villas nestled in lush gardens beside pristine beaches. These secluded sanctuaries provide tranquility and breathtaking ocean views from your private lanai. Each 1120 sq ft Ridge Villas Gold Ocean View one-bedroom features a choice of king or queen beds, a spacious living area with a queen sofabed, and a balcony overlooking the ocean.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Want to try an unforgettable experience where you can shed shame, build body confidence and connect with nature like never before? Give it a try at our body positive, non judgmental, family friendly wellness retreat center.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Volcano
Experience a bright and airy condo in Waikiki that offers stunning views from both the bedroom and living room. Unwind on the lanai while watching breathtaking sunsets with a glass of wine or enjoy a quiet breakfast at the small table, sipping Kona coffee and savoring a croissant. Registration Number: 123456789012, GE-066-684-6208-01, TA-066-684-6208-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Enjoy a luxurious stay in this beautifully appointed 1st-floor villa overlooking the 6th fairway of Kapalua Bay Golf Course. The property features a spacious layout with tile entry, kitchen, and bathrooms, complemented by plush carpets in the living and bedrooms.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This exceptional five-bedroom ground-floor suite offers a perfect retreat for large families or groups, combining three spacious neighboring suites into one expansive living space. Each suite features a semi-private lawn area, effectively doubling your outdoor space and providing direct access to the resort’s pools, beach, and BBQ zones, creating an ideal setting for gatherings and outdoor entertaining.
8 bedroom
5 bathroom
Sleeps 16
Hawaii> Lahaina
Discover the ultimate Maui getaway at Paradise Cove, an oceanfront retreat nestled in Napili on Maui’s scenic West shore. 5-bathroom home offers an exceptional blend of comfort, luxury, and natural beauty. Whether you’re seeking relaxation or adventure, Paradise Cove provides the perfect setting for your island escape.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Lahaina
This beautifully renovated studio apartment is located on the 27th floor of the Island Colony building, offering breathtaking views of Diamond Head and the Pacific Ocean. The unit has been upgraded with new floors, elegant cabinetry, a modern kitchen, and a fresh bathroom, ensuring a comfortable and stylish stay. Registration Number: TA-119-086-2848-03, GE-119-086-2848-03, 260210260403
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to your dream escape in Wailea, Maui. This immaculate one-bedroom condo offers breathtaking ocean views and modern luxury in a serene, lush setting. Nestled on the slopes of Wailea Ekolu, the condo is part of The Vistas Vacations, known for stylish and high-end properties.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea-Makena
Welcome to Wailea Ekolu 403, a beautifully appointed Maui vacation rental offering breathtaking ocean views, luxurious comfort, and an unbeatable location in the heart of Wailea. This spacious two-bedroom, two-bathroom condo is nestled on the lush fairways of the Wailea Blue Golf Course, capturing the essence of island living with its seamless indoor-outdoor design and panoramic vistas stretching across the Pacific Ocean toward Molokini and the outer islands.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Looking for a beachfront retreat in Waikiki? This hotel offers two separate rooms, each around 355 sq ft, with city views, double beds, and essential amenities. Located just minutes from Waikiki's best attractions, the property combines convenience with authentic Hawaiian hospitality. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Discover this inviting 2-bedroom condo in Kahuku, ideal for a relaxing Hawaii vacation. The property features a comfortable king bed and twin bed, perfect for families or couples. Modern amenities like high-speed WiFi, air conditioning, and a washing machine ensure a convenient stay. Registration Number: 1570010290021, TA-067-320-7808-01, GE-067-320-7808-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
The Islands at Mauna Lani A2 offers a stunning three-bedroom, two-story luxury condo with exceptional curb appeal and tropical charm. Upon arrival, you’ll pass through a private gate and stroll along a hand-laid brick pathway lined with vibrant hibiscus, lush greenery, and swaying palm trees, leading to elegant double-wide etched glass front doors.
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Kamuela
Discover your perfect island escape at Na Lani Ali’i, a spacious and sunlit condo nestled in Kauai's lush North Shore. Designed for both couples and families, this ground-floor retreat offers a blend of island charm and modern comforts. The open-concept living and dining area creates a welcoming space to relax, while the fully equipped kitchen with new appliances—including a dishwasher, oven with air fryer, microwave, toaster, and coffee maker—makes meal preparation easy.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
5-bath apartment perched on the 7th floor with elevator access. Enjoy peekaboo views of Diamond Head Crater and Waikiki Beach from your private lanai, perfect for relaxing with a morning coffee or evening sunset. The spacious living room features a large flat-screen TV, comfortable L-shaped sofa, and plenty of space to unwind. Registration Number: GE-150-451-5584-01, 260240140018, TA-150-451-5584-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate luxury retreat at Hale 'Ae Kai, a world-class Balinese-inspired estate set on 15 acres overlooking the famous Secret Beach. This exquisite property features four interconnected buildings surrounded by lush gardens and serene ponds, offering privacy and tranquility.
4 bedroom
7 bathroom
Sleeps 8
Hawaii> Kilauea
Welcome to Polynesian Shores 211 – your perfect island getaway! This spacious second-floor condo has 2 bedrooms and 2 bathrooms, comfortably accommodating up to 6 guests. The primary bedroom boasts a cozy king bed and a private ensuite. The second bedroom features two twin beds, and the living room offers a queen sleeper sofa for additional sleeping space.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the beauty of Kapalua with these charming Gold Golf Villas offering partial ocean views along the Bay Course fairway. Each villa features a spacious 1-bedroom layout with options for a king or queen bed, perfect for up to 4 guests including children and infants.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this charming 5th floor condo in Hawaii, offering a spacious 777 sq ft of comfortable living space with a private balcony on the north side of the building. The open kitchen has been recently updated and features granite counters, a stove, oven, refrigerator, microwave, coffeemaker, toaster, blender, tea kettle, and a complimentary tea starter kit, making meal prep convenient and enjoyable.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Step into the charm of old Hawaiian style with this newly decorated cottage nestled on one of Hawaii's top oceanfront locations along Ali'i Drive. This cozy yet spacious home combines traditional Hawaiian ambiance with modern updates, making it an ideal retreat for your Kona vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover this exquisite fully furnished two-bedroom, two-bath oceanfront condo in Honolulu that perfectly combines luxury and tranquility. The expansive open floor plan creates a seamless flow between living areas, offering a spacious environment ideal for both relaxation and entertaining. Registration Number: 13132317, GE-142-299-3408-01, TA-142-299-3408-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Maui Banyan T108AB, a beautifully designed ground-floor condo perfect for families seeking a relaxed Hawaiian getaway. This spacious two-bedroom, two-bathroom unit features a practical floor plan with a connecting door, making it ideal for groups or families traveling together.
3 bedroom
1 bathroom
Sleeps 8
Hawaii> Kihei
vrKCV - Palm Villas at Mauna Lani H23, Professionally Managed by Kona Coast Vacations
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
Located on the South Side of Kauai, Poipu Crater 15 is a beautiful, fully remodeled, and refurnished two-story townhouse offering the island experience you’re looking for! As owners we wanted our guests stay to be a truly unique experience so we designed, and improved it the way we would want to live in it.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
Experience the ultimate Maui escape in this gorgeous beachfront condo located in Valley Isle Resort, Kahana. This luxurious, remodeled corner unit offers breathtaking 180° ocean views from floor-to-ceiling windows and doors, creating an immersive experience of the Hawaiian coast.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this exceptional 2-bedroom oceanfront retreat in Poipu, Kauai, offering stunning sunset views and a relaxed island vibe. The spacious living area comfortably sleeps up to 8 guests, featuring a king bed, queen bed, and sofa bed, making it ideal for families or groups.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Poipu
Discover the perfect Maui retreat at Hale Kamaole 210, a charming ocean view condo in Kihei. This inviting 1-bedroom, 1-bath unit comfortably sleeps 4 guests and offers a blend of relaxation and adventure. Step onto the furnished outdoor lanai to enjoy stunning ocean vistas, ideal for sipping tropical drinks or unwinding in the island breeze.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
5-bathroom fully renovated estate villa offers a luxurious and spacious retreat. With high-end finishes, museum-quality art, and a flexible floor plan, it provides the ultimate resort experience for up to 8 guests. The main residence features a generous primary suite and two guest suites, each equipped with modern comforts and flat-screen TVs.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua Kona
5-bathroom retreat offers a perfect blend of comfort, elegance, and resort-style living. Situated in the prestigious Makani Kai neighborhood on an elevated homesite, the property provides breathtaking vistas of Mt. Waialeale, the Tom Weiskopf-designed golf course, and the sprawling Pacific Ocean, making it an ideal destination for families, groups, or anyone seeking privacy and relaxation.
4 bedroom
5 bathroom
Sleeps 12
Hawaii> Koloa
Welcome to this charming ground-floor condo nestled within the serene Makahuena Condo Complex in Koloa. com/1531902. This inviting retreat comfortably sleeps five, making it ideal for families or groups of friends seeking a relaxing Kauai getaway.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Koloa
Discover this fantastic Waikiki studio located on the 35th floor of the luxurious Island Colony community. The spacious unit features a private lanai overlooking the ocean, mountain, and Ala Wai Canal, providing breathtaking views. Designed to accommodate 2 guests, it offers a comfortable king bed, a sofa bed, and a fully equipped kitchenette with a hot plate, microwave, coffee maker, mini fridge, and basic cookware. Registration Number: GE-161-842-1248-01, TA-161-842-1248-01, 260210260549
1 bedroom
1 bathroom
Sleeps 2
Hawaii> HONOLULU
Discover the beautiful Golf Villas Ocean View 1-Bedroom retreat situated along Kapalua’s Bay Course fairway. This spacious villa offers 1200-1500 sqft of comfort, perfect for up to 4 guests including children and infants. Inside, you'll find a fully equipped kitchen with a stovetop, oven, microwave, refrigerator, coffee maker, cookware, and tableware, making meal preparation easy and convenient.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the peaceful island lifestyle at Noelani Kai by Gather, an upscale 2-bedroom, 2-bath townhome nestled in the prestigious Kauliani of Princeville. Surrounded by the stunning natural beauty of Kauai, this elegant retreat combines comfort with sophistication.
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Princeville
Papakea G202: Serene and spacious with spectacular ocean views. This 2 bedroom, 2 bathroom condo is the perfect Hawaii retreat.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience this spacious oceanfront 1-bedroom condo with an open kitchen layout, perfect for a relaxing getaway. The condo features a comfortable bedroom with a king or queen bed, a single sofa bed in the living area, and a private 84-square-foot lanai to enjoy breathtaking views.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to a peaceful Maui sanctuary with this unique offering of two adjacent, fully upgraded villas nestled within the prestigious Kapalua Golf Villas community. Perfectly suited for families or groups seeking comfort and style, each villa provides two bedrooms and two bathrooms, along with stunning views of the ocean and nearby golf courses.
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Welcome to Wailea Grand Champions 102, a luxurious 2-bedroom, 2-bathroom condo nestled amidst the lush fairways of one of Maui's premier championship golf courses. This spacious second-floor residence offers over 1,400 square feet of comfort and style, providing a perfect retreat for travelers seeking privacy, tranquility, and easy access to Wailea's vibrant attractions.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Discover this ideally located Waikiki condo just one block from the iconic Waikiki Beach, offering breathtaking views of Diamond Head, the ocean, and the Honolulu Zoo from a spacious balcony. The property features beach chairs available upon request, making it perfect for lounging and enjoying the island's beauty. Registration Number: TA-163-777-9456-01, GE-163-777-9456-01, 260270310027
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Welcome to Kukuiula Vacation Home 57, a luxurious ocean-view retreat nestled in the highly sought-after Kukuiula neighborhood of Poipu on Kauai. This stunning four-bedroom property combines island elegance with modern comfort, offering an idyllic tropical escape for families, friends, or couples seeking a luxurious getaway.
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Koloa
Come and stay at Kaanapali Shores, a beautiful 10-acre oceanfront resort on North Kaanapali Beach in Maui. This 5th-floor condo comfortably accommodates up to 6 guests with 2 bedrooms and 2 bathrooms. The master bedroom features a king-size bed, while the second bedroom offers two twin beds that can be converted into a king upon request.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to this charming 1-bedroom condo nestled within a peaceful community, blending comfort and recreation seamlessly. The spacious bedroom features a luxurious king-size bed, complemented by a pull-out couch, comfortably accommodating up to four guests. Registration Number: GE-140-652-6976-01, TA-140-652-6976-01, 570010270132
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Welcome to Sun Cottage, a stunning 3-bedroom, 3-bathroom detached home located in the prestigious Mauna Lani area of the Big Island. Brought to you by Paradise in Hawaii Luxury Homes, this property offers a perfect blend of sophistication, comfort, and island charm, making it an ideal retreat for families, friends, or special celebrations.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Maui Eldorado E106 is a cozy studio condo designed to comfortably host up to four guests, with a luxurious king bed and a queen sofa bed. The unit offers stunning garden views and easy access to the resort's inviting pools, perfect for unwinding after a day of exploring Maui.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the charm of Kailua-Kona with this beautifully remodeled Hawaiian-style beach bungalow, He'eia Haven Unit B. Located within walking distance to pristine beaches, shopping centers, world-class golf courses, and a variety of dining options, this home offers the perfect blend of comfort and convenience for your Hawaiian getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Aqua Aloha Surf Waikiki #1600 offers a charming and well-equipped deluxe penthouse studio ideally located just two blocks from the famous Waikiki Beach. The property features a private lanai with partial ocean and city views, creating the perfect setting for a relaxing island escape. Registration Number: 260210160205, TA-166-138-5216-01, GE-166-138-5216-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to Honolulu and enjoy this stylish apartment perfect for a relaxing getaway. The space offers a comfortable bedroom with a king bed and a cozy living room with an additional twin bed, ideal for singles or couples. The modern bathroom features a refreshing shower, and amenities like WiFi and air conditioning ensure a comfortable stay. Registration Number: TA-018-491-5456-01, 260270270013, GE-018-491-5456-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
