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 15600, 50
Escape to this luxurious 1120 sq ft Ridge Villas Ocean View 1 Bedroom in Kapalua, nestled amidst lush gardens and close to pristine beaches. The villa offers a comfortable king or queen bed, a spacious living area with a queen sofabed, and a private balcony overlooking the ocean, perfect for enjoying stunning sunsets.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your perfect Waikiki getaway! This gorgeous tropical studio, located on the 5th floor of Marine Surf Waikiki, offers stunning east-facing views from a spacious lanai. Just a 3-minute walk to the world-famous Waikiki Beach, this corner unit provides an island-inspired, tastefully decorated space equipped with all the amenities for an unforgettable vacation.Registration Number: GE-193-411-2768-01, TA-193-411-2768-01, 260190010000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Nestled in KaMilo at Mauna Lani Resort, this three-bedroom, three-bathroom villa offers elegant island living with resort-style amenities. The open-concept design features vaulted ceilings, Cumaru wood flooring, and a gourmet kitchen with high-end appliances and a built-in wine fridge.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Maui Resort Rentals is proud to bring you this 2BR Partial Ocean View Suite at the Honua Kai Resort, newly launched on May 7th, 2025 to start taking reservations for check-ins on June 7th, 2025!
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to the serene island of Maui in this spacious 818 sqft suite, perfect for a relaxing vacation. The suite features a full kitchen, allowing you to prepare your favorite meals with ease. Step out onto your private lanai to enjoy stunning views and unwind with Maui wine or beers.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
Discover the vibrant charm of Waikiki from our beachfront hotel rooms, just steps from Kuhio Beach and Diamond Head. Wake up to breathtaking ocean views and immerse yourself in Hawaiian adventures. Each room offers thoughtful amenities such as mini-fridges, Keurig coffee makers, and private lanais, creating a welcoming and comfortable environment. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to the Seagrove Ohana House, your ideal retreat in the beautiful Ko Olina resort community. This spacious 1715 sq ft private two-story villa offers a perfect blend of luxury, comfort, and convenience for your Hawaiian vacation or corporate stay. Registration Number: TA-153-374-0544-01, GE-153-374-0544-01, 1910560040026
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Experience the comfort and luxury of our beautifully appointed One-Bedroom Ocean View suite at Ka'anapali Beach Club. This spacious 820 sq ft accommodation features a king-size bed in the master bedroom, a sofa bed with a Tempurpedic mattress in the living area, and a full bathroom with a garden bath and walk-in shower.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Maui
Discover this exceptional condo hotel room at Hanalei Bay Resort, perfectly positioned in a private setting with breathtaking views of Hanalei Bay, lush mountains, cascading waterfalls, and the iconic Bali Hai. Unique among resort units, this ground-floor condo requires no stairs, offering easy access and stunning vistas comparable to upper-level units due to its elevated location.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Discover this private, lower-floor condo offering stunning garden views from the spacious lanai, overlooking Moir Gardens. The condo features tiled flooring throughout with a cozy carpet in the bedroom, creating a warm and inviting atmosphere. The recently refreshed kitchen is fully equipped with a full-size glass top stove, oven, microwave, refrigerator, dishwasher, coffee maker, blender, toaster, cookware, and tableware, making meal preparation convenient.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the ultimate Hawaiian getaway in this expansive beachfront penthouse featuring 2 master bedrooms and 2 balconies with a private patio. The 478 sqft lanai is beautifully furnished with a 14-piece Kingsley Bate set, including chaise loungers, club chairs, ottomans, and a dining table for 7, perfect for relaxing and entertaining.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Welcome to the perfect stay for large groups looking for a beach home! We have room to fit your whole family! This is the perfect paradise getaway for those seeking a calm, private, and relaxing Hawaiian vacation. The sand and water are just steps away. Registration Number: TA-210-165-4741-01, 550030220000, GE-609-521-5816-01
10 bedroom
6 bathroom
Sleeps 34
Hawaii> Laie
Experience Maui in style at Papakea H301, a charming 1-bedroom, 1-bathroom condo that comfortably sleeps 4. This condo boasts stunning ocean views from its private lanai, perfect for relaxing after a day on the sun-kissed beaches. Inside, you'll find a luxuriously remodeled kitchen featuring full-size stainless steel appliances, striking granite countertops, and all the essentials for preparing delicious meals.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautiful estate in picturesque Kula. This cozy retreat features a 1 bedroom with a comfortable Four Seasons Signature Plush Mattress, a lovely living room with a queen sofa bed, and a modern bathroom with a bidet and shower. Guests can enjoy amenities like WiFi, smart TV, and AC during their stay.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Makawao
Experience the comfort of Bay Villas Gold Oceanfront, featuring a spacious 1-bedroom unit with a king bed and a queen sofa bed, accommodating up to 4 guests including children and infants. Located close to Oneloa Beach, this villa offers stunning ocean views and a private balcony for relaxing mornings or evenings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Kaanapali Alii 463, a stunning one-bedroom residence boasting panoramic views of the lush West Maui Mountains from the largest lanai on the property. Wake up each morning to breathtaking sunrises over the mountain range while enjoying a freshly brewed island coffee.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Nestled high in the cliffs of Princeville, this Kauai resort blends natural and man-made pleasures into magnificent harmony. Take the opportunity to enjoy a meditative retreat at the on-site spa, or use the resort as a basecamp for your many Hawaiian adventures.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Immerse yourself in luxurious island living with this stunning condo at Sky Ala Moana, offering breathtaking ocean views and sophisticated comfort.Registration Number: 230160480300, GE-048-722-0736-01, TA-048-722-0736-01
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to this peaceful villa at a 16-acre resort on Maui's North Kaanapali Beach.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kaanapali
Experience the ultimate Maui getaway in this beautifully renovated oceanfront condo. The spacious layout offers breathtaking views of the West Maui Mountains and endless blue waters from the living room, kitchen, and private lanai, providing your front-row seat to paradise.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
This charming condo offers a perfect blend of comfort and convenience, situated just across from Kapiolani Park and close to the Waikiki Shell. Nestled in a quiet neighborhood beneath the iconic Diamond Head, it provides a serene retreat with easy access to vibrant Waikiki activities.Registration Number: TA-182-747-5456-01, GE-182-747-5456-01, 310260310035
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Immerse yourself in the vibrant allure of Waikiki from our beachfront hotel rooms, just steps from Kuhio Beach and Diamond Head. Each room offers a comfortable, thoughtfully equipped space featuring a mini-fridge and Keurig coffee maker to start your day right. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Find your bliss in this luxurious space, where unequaled comfort provides an intimate refuge. Nicknamed "The Garden Isle" for its expanses of lush rainforest, Kauai has bedazzled generations with its miles of solitary beaches and profusion of aquatic wonders.
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Lihue
Experience the ultimate island retreat in our luxurious Kapalua Ocean View Villas. Nestled amidst lush gardens and close to pristine beaches, these fully-equipped 2-bedroom villas offer a perfect blend of comfort and style. Each villa spans 1750 sq ft and features a spacious living area with a queen sofabed, a fully equipped kitchen, and two bedrooms—one with a king bed and the other with twin beds—ideal for families or groups.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate relaxation at this stunning 4-bedroom retreat in Ko Olina, Oahu. Nestled within a master-planned community renowned for its scenic lagoons and pristine beaches, this property offers a perfect blend of luxury and comfort.Registration Number: TA-074-197-1456-01, GE-074-197-1456-01, 1910560050008
4 bedroom
4 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to Mana Kai at Waimanalo by Gather, a stunning beachfront property on Hawaii's East Coast offering a serene escape with four spacious bedrooms. This coastal haven is ideal for families or groups seeking tranquility, luxury, and spectacular natural beauty.Registration Number: 410020180000, GE-164-021-9648-01, TA-164-021-9648-01
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Waimanalo
Discover a charming retreat in Princeville with this well-appointed condo. The inviting bedroom offers restful nights, while the spacious living room with a cozy sofa bed provides a perfect spot to unwind after exploring the beautiful surroundings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Ekolu 404 offers a spacious and comfortable retreat on Maui’s beautiful Wailea coast. This second-floor condo features two bedrooms and two bathrooms, accommodating up to six guests comfortably. The master bedroom is furnished with a king bed, a split AC system, and an ensuite bathroom with a walk-in shower and a large vanity.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Step into the warmth of Hawaiian hospitality at Hawaiian Retreat at Vista Waikoloa, a beautifully decorated 2-bedroom, 2-bath condo nestled in the heart of Waikoloa Beach Resort. The interior features traditional Hawaiian decor, with rich wood accents and tropical touches that create a welcoming, island-inspired ambiance.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Discover the comfort and elegance of two luxury villas at Club Wyndham Bali Hai Resort in Princeville, HI. Each spacious suite accommodates up to 8 guests, perfect for families or groups seeking a relaxing island getaway. Enjoy access to a wide range of resort amenities including outdoor swimming pools, hot tubs, a children's pool, fitness center, fire pit, shuffleboard, and a barbecue area, all designed to enhance your vacation experience.
7 bedroom
5 bathroom
Sleeps 14
Hawaii> Princeville
Step into this beautifully renovated beachfront condo at Sugar Beach Resort, offering an elegant blend of modern design and island charm. The interiors have been thoughtfully upgraded with custom touches, providing a luxurious retreat in Maui. Inside, you'll find a sleek chef's kitchen featuring quartz countertops, a stylish glass backsplash, custom cabinetry, and stainless steel appliances, perfect for preparing island-inspired meals.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover the perfect blend of comfort and natural beauty at our secluded suites on Kauai's North Shore. Nestled on a private bluff, these one- and two-bedroom accommodations offer breathtaking views of the Pacific Ocean, where the horizon dances with clouds, whales, turtles, and crashing waves.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the perfect tropical getaway at Wailea Elua Village, where this elegant 2-bedroom, 2-bath condo offers a blend of comfort and convenience. Spanning 1,363 sq. , this ground-floor residence features a garden view with glimpses of the ocean, creating a serene setting just steps from Wailea's renowned beaches and luxurious resorts.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Experience the perfect Hawaiian getaway in this charming 2-bedroom condo located in the heart of Waikiki. The unit comfortably sleeps up to 6 guests, making it ideal for families or groups of friends. From the private balcony, enjoy breathtaking views of the mountains and canal, creating a serene atmosphere after a day exploring the island. Registration Number: GE-012-053-7536-01, 260170030131, TA-012-053-7536-01
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Experience the vibrant spirit of Waikiki from this stylish 1-bedroom condo on the 18th floor of Aston Waikiki Sunset. The open-plan living, dining, and kitchen area seamlessly connect to a spacious private balcony, perfect for sipping morning coffee or watching breathtaking sunsets over Diamond Head, city skylines, and partial ocean views. Registration Number: TA-182-695-7312-01, GE-182-695-7312-01, 1260280110158, STR # 2537
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Enjoy relaxing beachfront at Haliʻi Kai in Waikoloa Beach Resort. This delightful upstairs condo offers a Master bedroom with a Cal King bed, walk in closet, TV and private en-suite bathroom. Guest room offers 2 queen beds, TV and large mirrored wardrobe closet.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Maui Vista #1-218 offers a comfortable and refreshed one-bedroom, one-bathroom condo situated on the second floor of Building 1 with stunning ocean views. The condo features bright modern furnishings throughout, creating a welcoming atmosphere for guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this charming one-bedroom Waikiki apartment offering breathtaking Diamond Head views from your private lanai. Located just one block from the world-famous Waikiki Beach, this unit combines comfort, convenience, and a prime location for your vacation.Registration Number: TA-078-158-3360-01, 260270270012, GE-078-158-3360-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hanalei Bay Resort unit 7305/7306. Looking for enough space for the whole family to relax in scenic comfort? This 2br 2ba condo comes with a with a sleeping loft, offering truly unforgettable views of the ocean, the mountains, and Kauai's incredible North Shore sunsets.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Discover this beautifully updated 2-story villa nestled on the second floor with easy access via 8 stairs from the parking area. The spacious 2400 sqft (223 sqm) layout features tile floors throughout, creating a sleek and clean environment.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to this luxurious 2-bedroom apartment in Keauhou, perfect for a peaceful getaway in Hawaii. Each bedroom features a king-size bed, a private bathroom with a bathtub, and individual temperature-controlled AC units, ensuring maximum comfort.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Escape to Paradise Park Hale, a spacious and peaceful two-bedroom home nestled in a quiet coastal community of Hawaii's Sunbelt region. This charming retreat offers a perfect blend of tropical weather, sunshine, and ocean breezes, making it an ideal destination for families and travelers seeking tranquility and comfort.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
Discover this beautifully renovated 1-bedroom, 1-bathroom condo located on the 27th floor of Waikiki Banyan, offering stunning partial ocean views from the lanai and bedroom. The unit comfortably accommodates up to 6 guests with two full-size beds in the bedroom and a queen sofa bed in the living room, making it ideal for families or groups.Registration Number: TA-149-231-0016-01, 260250050714, GE-149-231-0016-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
5-bathroom gated estate offers a luxurious coastal retreat with breathtaking ocean vistas. Spanning 6,400 sq ft, the home features a seamless blend of modern design and natural finishes, creating an elegant yet inviting atmosphere for up to 12 guests.
6 bedroom
7 bathroom
Sleeps 12
Hawaii> Kailua Kona
Experience the comfort of this beautifully renovated 1-bedroom apartment in Honolulu, completed in 2024. The unit features a queen-sized bed in the bedroom and a sofa bed in the living room, providing flexible sleeping options for guests. The modern bathroom offers a refreshing shower, and the apartment is fully equipped with essential amenities including WiFi, air conditioning, and a washing machine for added convenience. Registration Number: GE-112-393-0112-01, TA-112-393-0112-01, 270180150000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the beauty and tranquility of Maui at Maui Banyan P103, a charming 2-bedroom, 2-bathroom condo perfect for your island getaway. Located just steps from the pristine Kamaole Beach Park II, this ground-floor unit offers both convenience and comfort for up to six guests.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience an unforgettable stay in our luxurious 3-bedroom Presidential Suite at Bali Hai Resort, perfectly situated on Kauai’s North Shore among pristine beaches and lush landscapes. Available for an 11-night holiday from December 23rd through January 3rd, this spacious suite offers all the comforts of home with a touch of elegance.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
Welcome to Canoe House and Beach Walker at Champion Ridge by Gather, two exceptional luxury estates nestled within the prestigious Mauna Lani Resort on the Big Island of Hawaii. These adjacent homes are perfect for large families or groups traveling together, offering a seamless blend of privacy, spaciousness, and breathtaking natural beauty.
8 bedroom
9 bathroom
Sleeps 16
Hawaii> Kamuela
Experience a comfortable stay in this 12th-floor Hawaii condo offering a spacious balcony and modern amenities. The condo features tile and vinyl plank flooring throughout, creating a sleek and clean environment. The fully equipped kitchen includes a refrigerator, stove/oven, microwave, cookware, and tableware, perfect for preparing meals during your stay.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience the comfort of Bay Villas Ocean View 1 Bedroom, located close to Oneloa Beach. This spacious villa offers 1200-1500 sqft of living space, perfect for up to 4 guests including children and infants.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
