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 15150, 50
Villa Luana is a stunning retreat nestled against a lush Hawaiian mountainside, blending natural beauty with upscale luxury. This elegant home invites you to indulge in comfort, offering sweeping mountain vistas and serene glimpses of the Pacific Ocean. Registration Number: GE-097-116-5696-02, TA-097-116-5696-02, 39081092
4 bedroom
5 bathroom
Sleeps 12
Hawaii> Honolulu
Experience the ultimate Maui getaway by combining two beautifully renovated oceanfront condos at Napili Point Resort. Spanning over 2,165 sq. , these spacious units offer breathtaking panoramic views of the Pacific Ocean, Molokai, and Lanai. Each condo features modern upgrades, designer furnishings, and private lanais perfect for whale watching, sunset viewing, and outdoor dining.
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Enjoy an unobstructed million dollar ocean view, spectacular sunrises, and access to secluded 'Sealodge Beach' via the nearby trailhead.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Dolphin Bay Estate offers an enchanting escape on the pristine shores of Big Island, Hawaii. This luxurious property combines serenity, elegance, and convenience, making it the perfect Hawaiian retreat.
5 bedroom
5 bathroom
Sleeps 13
Hawaii> Captain Cook
Your Hawaiian dream vacation starts at this stunning Maui Banyan condo, nestled within a lush resort just a short walk from iconic Maui beaches. Set on the 5th floor, this spacious, climate-controlled unit offers breathtaking views of the ocean and mountains from the private lanai, ideal for morning coffees or sunset cocktails.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this spacious two-bedroom condo with a loft at Maui Vista, offering stunning ocean views and convenient access to pools and tennis courts. The master bedroom features a comfortable queen bed, while the loft has two twin beds, making it ideal for families or groups.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
5-bath villa nestled within the Hillside Villas of Ko Olina Resort. Designed for comfort and convenience, the property offers a flexible stay option where you can choose your preferred 30-day rental period without the need for a full month. Whether you're seeking a relaxing retreat or an adventurous holiday, this villa provides the perfect base for your island exploration.Registration Number: TA-158-299-4944-01, GE-158-299-4944-01, 1910560130142
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
This fabulous 7 bedrooms and 5 baths for 14 to 16 people, is situated right above famous Anini Beach, with an 8-10 minute walk down the Anini Beach trail just steps away from the townhome. Short walk to the Princeville playground and restaurants and shops as well!
6 bedroom
5 bathroom
Sleeps 16
Hawaii> Princeville
Hale Pau Hana #707 is a 2 bedroom, 2 bath unit on the 7th floor with a panoramic ocean view and central A/C throughout. It provides a fully-equipped kitchen, 2 flat-screen TVs, and free WIFI. Go to sleep in your comfortable cal king bed as you enjoy the sound of the waves.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Immerse yourself in authentic Hawaiian charm at The Nanea Ocean Villas, ideally located on Maui's North Ka'anapali Beach. These spacious three-bedroom villas offer the perfect blend of comfort and luxury, featuring modern amenities like stainless steel appliances, HD entertainment systems, and private lanais with breathtaking views of Maui.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
This stylish Honolulu apartment offers a comfortable and modern retreat for up to 2 adults and 2 children. Located on the 16th floor of the secure 909 Kapiolani building, this fully renovated unit provides stunning floor-to-ceiling windows with views of the city and ocean. Registration Number: 230030730078, TA-155-046-8608-01, GE-155-046-8608-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honoluu
Enjoy your Hawaiian vacation in this cozy 11th-floor condo at Castle Bamboo Waikiki Hotel. Located just blocks from the iconic Waikiki Beach, this unit offers a perfect blend of comfort and convenience. The open-concept floorplan features an island-inspired aesthetic with hardwood floors, creating a warm and inviting atmosphere. Registration Number: GE-197-905-6128-01, 260230670084, TA-197-905-6128-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to your perfect Hawaiian escape in Maui, where lush landscapes and luxury converge. Located in the heart of Kaanapali, this spacious two-bedroom, two-bath condo offers breathtaking views of the rolling fairways and the majestic West Maui Mountains.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Sunset Sanctuary is a stunning 4-bedroom, 4-bathroom home, perfectly perched to offer breathtaking views of the Pacific Ocean. Located on the beautiful island of Oahu, this home combines luxurious comfort with the laid-back charm of island living.Registration Number: 550100380000, TA-118-525-3376-01, GE-118-525-3376-01
5 bedroom
5 bathroom
Sleeps 14
Hawaii> Laie
Experience the perfect blend of contemporary comfort and tropical charm in this beautifully appointed two-bedroom, two-bath villa at Wailea Grand Champions. The open floor plan seamlessly connects the fully-equipped kitchen, dining area, and spacious living room, creating an inviting space for relaxation and entertainment.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Experience the vibrant energy of Waikiki from the heart of the Hilton Hawaiian Village Waikiki Beach Resort, where the Lagoon Tower Studio combines modern comfort with a prime tropical location. Perfect for couples or small families, this cozy retreat offers an ideal starting point for your Hawaiian adventure, complete with resort amenities and access to Oahu's top attractions.Registration Number: TA-005-837-4656-01, 260090010000, GE-005-837-4656-01
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Immerse yourself in Hawaiian luxury at Marriott’s Maui Ocean Club - Molokai, Maui, & Lanai Towers, a breathtaking resort on the golden shores of Ka’anapali Beach, where every moment feels like paradise.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This exclusive package combines four neighboring ground-floor residences at the prestigious Honua Kai Resort, creating an expansive 8-bedroom retreat perfect for large families or groups of up to 24 guests. The residences are strategically located for ultimate convenience, with two 2-bedroom units adjacent to a 3-bedroom residence in the interior courtyard, plus an additional 1-bedroom unit just across the hall from the 3-bedroom.
13 bedroom
8 bathroom
Sleeps 24
Hawaii> Lahaina
Relax at the pool or on the beach, hit the trails, or enjoy tons of daily activities and entertainment right on site–the choice is yours at this oceanfront property in Lahaina! Spend the day lounging on beach chairs or enjoying a private beach cabana.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This sunny 11th-floor studio at Ilikai Marina offers a perfect blend of comfort and convenience, situated at the edge of Waikiki with stunning city and mountain views. Spanning 427 sq. , the unit is bathed in natural light, creating a welcoming and airy atmosphere. Registration Number: TA-068-669-2864-01, 260100020122, GE-068-669-2864-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Explore this beautifully appointed 10th-floor condo featuring a private balcony with scenic views. Inside, you'll find tile and vinyl plank flooring throughout, creating a modern and clean aesthetic. The fully equipped kitchen includes a fridge, stove/oven, microwave, cookware, and tableware, making meal preparation a breeze.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your tropical paradise! This stunning three-level home boasts panoramic ocean views and tranquil surroundings, making it an ideal retreat for relaxation and nature lovers.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> LAUPAHOEHOE
The Kona Palm Retreat offers a private, gated Hawaiian estate just five minutes from Kailua Kona and the Pacific Ocean, perfect for a serene tropical getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover a relaxing getaway at our beachfront hotel featuring three separate units, each offering 312 sq ft of comfortable space with two double beds, a private balcony, and partial ocean views. The property boasts an outdoor swimming pool with a large sun deck, loungers, and umbrellas where you can soak up the sun, or unwind at the lush tropical garden. Registration Number: 260270350000, GE-025-315-0208-01, TA-025-315-0208-01
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Relax with the whole family at this peaceful Maui condo ideally situated in the heart of Kaanapali, just across from one of Maui’s most stunning beaches. This newly renovated, single-level ground-floor unit offers breathtaking views of the ocean, Lanai, and Molokai, providing a perfect setting for an unforgettable Hawaiian getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui getaway in a beautifully appointed one-bedroom villa at the exclusive Westin Nanea resort. Upon arrival, guests check in at the resort's front desk with their confirmation number, enjoying seamless access to all amenities and services.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Step into a nostalgic Hawaiian experience at Wyndham Kona Coast Resort, where the charm of the 1930s meets modern comfort. Located in Kailua-Kona, this resort offers spacious one-bedroom suites designed for comfort and relaxation. Each suite features a private bedroom with a king bed, a queen sleeper sofa in the living area, a fully equipped kitchen, and a washer/dryer for convenience.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to Sunset Point Hawaiian Beachfront by Gather, a stunning three-bedroom, one-and-a-half-bath home situated directly on the pristine Sunset Beach in North Shore Oahu. This residence is renowned as one of the area's finest, offering direct views of the famous Backyards surf break and providing an authentic island experience. Registration Number: TA-201-477-7344-01, GE-201-477-7344-01, 590010880000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haleiwa
★Maui Paradise: Your Spacious Beachfront Escape★
4 bedroom
3 bathroom
Sleeps 9
Hawaii> Lahaina
Seahorse Beach House is a hidden gem on Oahu’s east coast, offering a luxurious and peaceful retreat just steps from Kailua’s pristine white sand beaches. Located on one of Kailua’s most desirable loops, this home provides easy access to local cafes, boutiques, and farmers’ markets, making it an ideal base for exploring the vibrant town and surrounding natural beauty.Registration Number: 430130190000, TA-129-806-1824-01, GE-129-806-1824-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
Experience the perfect blend of tropical serenity and urban convenience in this charming 17th-floor ocean view studio at Ilikai Marina, situated on the quieter side of Waikiki. This upgraded, modern space offers a bright, airy atmosphere with beautiful ocean vibes, making it an ideal retreat for travelers seeking comfort and convenience.Registration Number: TA-170-318-0800-01, GE-170-318-0800-01, 260100020226
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience a warm and inviting stay in this newly renovated studio located on the 15th floor overlooking Fort DeRussy Beach Park. Situated directly across from the Hilton Hawaiian Village, this central Waikiki location offers easy access to the world-famous beaches of Waikiki and Ala Moana, both just a 10-minute walk away. Registration Number: TA-203-873-3312-01, GE-203-873-3312-01, 126007026013
0 bedroom
1 bathroom
Sleeps 2
Hawaii> honolulu
Immerse yourself in the breathtaking beauty of Maui from this luxurious 9th-floor interior-courtyard studio suite in Kaanapali. Suite 962 offers stunning views of the Pacific Ocean, the iconic Black Rock, neighboring Molokai island, and the lush West Maui Mountains, providing a perfect backdrop for your island getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Step into this stunning, newly built 6-bedroom luxury residence spanning 6,000 square feet, designed with the finest finishes and utmost comfort. The grand entrance welcomes you into an enchanting courtyard oasis, lush with greenery and complemented by the relaxing sound of cascading water.
6 bedroom
6 bathroom
Sleeps 12
Hawaii> Kailua-Kona
Experience the luxury of Hokulani 524, a beautifully appointed one-bedroom villa on the 5th floor of Hokulani Tower at Honua Kai Resort & Spa. This spacious suite offers stunning views of the West Maui Mountains and a resort-style ambiance, making it ideal for couples or small families seeking both comfort and convenience in Maui.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
This listing features two beautifully remodeled, spacious condos combined into one luxurious oceanfront retreat at Sands of Kahana. Covering over 3,400 sq. , the property offers panoramic views of Molokai and Lanai from two private lanais, making it ideal for families or groups seeking comfort and convenience in Maui.
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Welcome to your tropical paradise nestled on 26 lush acres of oceanfront property in Maui. This spacious 1-bedroom villa at Westin Kaanapali Ocean Resort Villas comfortably accommodates up to 4 guests, making it ideal for couples or small families seeking a luxurious and relaxing escape.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the serenity and beauty of Waikoloa Fairway Villas, perfectly situated on the pristine shores of King's Lake within the Waikoloa Beach Course. This charming island retreat offers a tranquil setting with easy access to a variety of amenities and outdoor activities.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Waikoloa Beach Resort
5-bath villa located within the prestigious gated community of Kauna'oa at Mauna Kea Resort. The beautifully designed pod-style home spans 2,538 sq ft of refined interior space, complemented by 1,060 sq ft of expansive lanais that invite seamless indoor-outdoor living.
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Waimea
Experience the ultimate Maui getaway at this charming oceanfront studio villa located within the Westin Kaanapali Ocean Resort Villas North. Available for the week of Oct 17-24, 2025, this cozy yet well-appointed space comfortably accommodates up to four guests with a plush King bed in the living area and a Queen Sofa Bed.
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the beauty of Hawaii's West Side at this charming vacation home, located just half a block from the beach. Wake up to breathtaking ocean views with vibrant shades of blue, turquoise, and green, and enjoy daily dolphin sightings that add a magical touch to your stay.Registration Number: 870170140000, TA-090-333-2864-01, GE-090-333-2864-01
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Nanakuli
Stay comfortably in Kihei at this inviting 2-bedroom condo designed for a relaxed Maui vacation. The unit includes a spacious living area with a sofa bed, perfect for small groups or families. The master bedroom offers a plush king bed, while the second bedroom is fitted with two twin beds, providing flexible sleeping arrangements.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Aloha and welcome to your own 1-bedroom oceanfront Maui condo on the sunny South Shore of Kihei! This beautifully appointed unit offers stunning ocean views from your private lanai, perfect for watching Maui's famous sunsets and listening to the soothing surf.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the vibrant energy of Waikiki from this stylish, centrally-located condo. Just two blocks from the famous Waikiki Beach, you'll enjoy stunning sunsets, refreshing ocean swims, and breathtaking ocean views. The property offers a secure building with 24-hour security, ensuring peace of mind during your stay. Registration Number: TA-053-288-7839-01, GE-054-288-7936-01, 1260190100000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this inviting ground floor condo in Hawaii, offering a spacious 473 sq ft lanai perfect for outdoor dining and relaxation. The unit features a cozy bedroom with a king bed, a master bathroom with a shower/tub combo, and a sunken living room with a queen sofa bed, accommodating up to four guests including children and infants.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Discover the perfect island retreat at Aqua Aloha Surf Waikiki, located just two blocks from the iconic Waikiki Beach. This deluxe studio offers a cozy and modern space with a private lanai and partial city views, ideal for couples or solo adventurers. Registration Number: TA-188-165-1712-01, GE-188-165-1712-01, 1260210160071
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Looking for the perfect family-friendly island escape? Hyatt Vacation Club at Kaanapali Beach on Maui combines luxury, adventure, and relaxation in a stunning oceanfront setting. Nestled along the renowned sands of Kaanapali Beach, this resort is your gateway to sun-filled days, water activities, and unforgettable family memories.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to this charming 1-bedroom, 1-bath condo perched on the top floor, offering breathtaking ocean views from the lanai where you can relax to the sound of rolling waves and watch surfers catch the perfect break. Shores of Maui is an intimate garden-style complex just steps from the vibrant beaches of South Kihei, making it an ideal location for your Maui getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Located on 16 acres of Maui's renowned North Ka'anapali Beach, The Westin Nanea Ocean Villas offers a perfect blend of relaxation and adventure. These spacious villas provide stunning ocean views and easy access to the beautiful beach, making it an ideal destination for families, couples, and solo travelers alike.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the ultimate Hawaiian retreat at Wailea Ekolu Village condo #1203, a beautifully appointed one-bedroom, two-bathroom residence offering breathtaking ocean and golf course views. This ground-floor sanctuary seamlessly blends indoor comfort with outdoor living, perfect for couples seeking relaxation and scenic beauty.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
