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 3300, 50
Escape to paradise at Puako Beach Condos Unit 210, a beautifully renovated 3-bedroom, 2-bathroom end unit located just steps from a stunning white sand beach. This spacious condo offers a perfect blend of comfort and convenience for families or groups of friends seeking a memorable Hawaiian vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimea
Escape to the serene beauty of Maui at Wailea Beach Villas Penthouse 411, a luxurious 1,900 sq. ft. oceanview residence on the fourth floor. This stunning two-bedroom, two-bathroom penthouse offers the perfect blend of comfort, privacy, and breathtaking views, making it an ideal retreat for families or groups of friends.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Welcome to our beautifully renovated 2-bedroom, 2-bath corner condo at Kamaole Sands in South Kihei. This spacious unit offers approximately 1,188 sq ft of comfortable living space on the 4th floor of Building #1, with a partial ocean view from the lanai.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the beauty of Maui from this stunning ocean-view 1-bedroom villa nestled on the pristine northwestern coast of the island. Spanning 900 sqft, this villa offers a fully-equipped kitchen, a cozy dining area, and a spacious living room with a pull-out sofa bed, perfect for family or friends.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to Hale Kai O'Kihei 310, a luxurious oceanfront condo that offers unmatched comfort and breathtaking views of Maui's coastline. This meticulously decorated retreat features a spacious bedroom with a plush queen bed and reclaimed redwood accents, creating a serene sanctuary after a day of island adventures.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the ultimate Maui getaway in this beautifully appointed condo offering panoramic views of West Maui Mountains and partial ocean vistas. Floor-to-ceiling windows in the living room and master suite open onto private lanais, letting you enjoy the scenery without leaving your room.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Kihei
The Ocean Villa offers an exclusive beachfront retreat in the gated Ocean Villas complex at Turtle Bay Resort, situated on the iconic North Shore of Oahu. Designed for up to 2 guests, this meticulously maintained villa combines luxury, privacy, and convenience in one stunning package.Registration Number: GE-156-544-8704-01, 1570010130017, TA-156-544-8704-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Kahuku
This exquisite Pacific Heights estate offers a luxurious Hawaiian retreat with breathtaking city and ocean views from Diamond Head to Ewa Beach on all three levels. The spacious main level features an open-plan design with soaring ceilings, a large living area, and a dining room that seats ten, perfect for entertaining. Registration Number: TA-106-241-0752-01, GE-106-241-0752-01, 220230440002
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
The Lotus Room at Maui Eco Retreat offers a peaceful sanctuary nestled in pristine landscapes on Hawaii’s North Shore. This ground-floor master bedroom features a stunning ocean view through floor-to-ceiling windows, creating a tranquil setting for relaxation and rejuvenation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Maui County
Escape to this luxurious 9th-floor suite in the heart of Kaanapali Beach, offering breathtaking unobstructed views of the Pacific Ocean, Black Rock, Molokai, and the West Maui Mountains. The beautifully renovated interior features modern vinyl plank flooring, a fully updated kitchen with upgraded cabinets, quartz countertops, and stainless steel appliances, making it perfect for preparing meals and refreshing drinks.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Hawaiian getaway at our beautifully renovated condo nestled in the gated community of Kuilima Estates near Turtle Bay. Designed with quality and comfort in mind, this unit offers a minimal yet luxurious feel with a mountain-inspired theme, featuring locally made linens and waffle print towels for a touch of authenticity.Registration Number: TA-164-934-4512-01, GE-164-934-4512-01, 570010270053
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Experience the ultimate Hawaiian escape in this beautifully appointed 3-bedroom villa at Hualalai Resort. Spanning over 2,500 square feet, the villa offers a luxurious and comfortable retreat with unobstructed ocean views from the primary bedroom's private lanai, where you can listen to the breaking waves.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience the charm of this spacious 2-bedroom, 2-bathroom townhouse just steps from Kepuhi Beach on beautiful Molokai. The generous 2000 sq ft layout offers a welcoming atmosphere resembling a beach home, perfect for couples seeking extra space or families up to five.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Maunaloa
Welcome to your peaceful Hawaiian escape! This spacious second-floor 2-bedroom, 2-bathroom condo in Waikoloa Village offers a perfect blend of comfort and natural beauty. Inside, enjoy a fully equipped kitchen with modern appliances, a cozy living area, and two inviting bedrooms with comfortable bedding, ceiling fans, and ample closet space—ideal for families, friends, or colleagues.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Escape to Naupaka Haleta, a charming Hawaiian-style vacation residence nestled on a spacious lot directly in front of a pristine sandy beach. This exquisite property offers breathtaking views of Lanikai Beach, turquoise waters, and the Mokulua Islands, making it an ideal retreat for those seeking tranquility and natural beauty. Registration Number: TA-151-566-1312-01, GE-151-566-1312-02
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lanikai
Discover this charming garden-level condo at The Plantation at Princeville, nestled on the scenic North Shore of Kauai. The property offers a perfect blend of comfort, natural beauty, and convenient location. Wake up each morning on your covered lanai, breathe in the island air, and enjoy breathtaking views of lush mountains.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
5-bath home nestled in Hapuna Estates within the Mauna Kea Resort on the Big Island. Enjoy breathtaking Pacific Ocean views from the covered lanai, where mornings start with the soothing sounds of chirping birds and evenings offer spectacular sunsets over the Kohala mountains.
5 bedroom
5 bathroom
Sleeps 14
Hawaii> Kamuela
Discover this beautifully remodeled corner unit at Colony Surf on Waikiki's Gold Coast, offering stunning 25-foot wide ocean vistas right from your window. Located on the sought-after 3rd floor, this modern haven was renovated in 2018 with all new cabinets, appliances, and fixtures, ensuring a fresh and comfortable stay. Registration Number: 131032010002, GE-072-845-0048-01, TA-072-845-0048-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to Hali'i Kai 10B, a beautifully managed ocean view condo nestled within Waikoloa Beach Resort on the Big Island. This ground-floor retreat offers breathtaking views of the ocean and signature golf course, perfect for a relaxing getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Experience the ultimate Hawaiian getaway at Hale E Komo Mai, a modern resort-quality home offering over 3,500 sq ft of luxurious living space. Nestled on a fully landscaped acre, this property comfortably sleeps up to 10 guests, making it ideal for families or groups seeking privacy and comfort.
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Keaau
Aloha and welcome to Wyndham Mauna Loa! This tranquil island retreat offers a perfect setting for rest and relaxation. 5-bath condo provides all the comforts needed for a memorable Hawaiian getaway. Inside, you'll find a full-size kitchen equipped with an oven, stove, microwave, dishwasher, pots, pans, glassware, silverware, and a refrigerator, making meal preparation easy and convenient.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Welcome to Kiahuna Plantation Beach House #179, a beautifully renovated Hawaiian retreat perfect for your island getaway. This second-floor condo offers an open concept kitchen with quartz counters, new cabinets, and stainless steel appliances, creating a modern and inviting space.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to Maui Kaanapali Villas, a stunning oceanfront resort nestled on 11 acres of lush tropical gardens and pristine beachfront. Our exclusive corner one-bedroom, two-bathroom unit on the 5th floor of the Aloha Tower offers breathtaking panoramic views of the ocean, visible from the living room, open kitchen, and bedroom.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautifully remodeled Maui condo offering breathtaking ocean and Haleakala views from the living room and lanai. The surf-style interior features custom flooring, sleek countertops, and fresh paint, creating a relaxed yet modern atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
The Cliffs at Hakalau offers a stunning oceanfront gated estate nestled on the Hamakua Coast, where guests can enjoy front-row views of dolphins, whales, and cruise ships sailing by. Feel the refreshing Tradewinds as they first touch the Hawaiian Islands, immersing you in the island’s natural beauty.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Hakalau
Nani O'Hana is a beautifully appointed 4-bedroom, 3-bathroom luxury residence nestled in the peaceful neighborhood of Poipu Kai. Located within the Lanai Villas subdivision of the Poipu Kai community, this home offers easy walking access to stunning beaches, tennis courts, a community pool, and nearby resorts like the Grand Hyatt.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Welcome to Hale Kai Aloha at Kona Onenalo #10! This stunning free-standing townhouse offers an authentic Hawaiian experience with modern comforts. Nestled in an exclusive gated community, it provides easy access to the ocean and beautiful White Sands Beach, just a five-minute walk away.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience the ultimate island escape with this one-bedroom villa at Hilton Hawaiian Village in Waikiki. Nestled within 22 acres of pristine beachfront, this resort offers spectacular amenities, including four swimming pools, a whirlpool spa, Mandara Spa, and Holistica Hawaii Health Center for ultimate relaxation. Registration Number: TA-000-785-4592-01, GE-000-785-4592-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this stunning 11th-floor oceanfront penthouse villa located in the prestigious Beach Villas at Ko Olina, known as the 'Place of Joy'. With 1466 sq ft of luxurious living space, it offers panoramic ocean views from your private lanai, perfect for morning coffees or sunset cocktails. Registration Number: 910570090124, TA-075-893-1456-01, GE-075-893-1456-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
The Fairways at Mauna Lani offers a luxurious, privately gated retreat nestled within the prestigious Mauna Lani Resort on the sunny Kohala Coast of the Big Island. 5-bath townhome boasts stunning views of the North Course's fairway, with beautifully decorated interiors designed by an interior stylist, blending comfort and elegance.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Kamuela
Discover this beautifully remodeled oceanfront condo in Maui's Ma'alaea community, perfect for family vacations or group getaways. The 2-bedroom, 2-bath space was renovated in 2021, featuring new floors, cabinets, counters, and a modern split air conditioning system to keep you comfortable.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
Welcome to Hukilau Beach, where the stunning blue waters of the Pacific Ocean meet the majestic Ko'olau mountains, offering an idyllic setting for your Hawaiian getaway. Hale Lani, meaning "House of Heaven," is a premier vacation rental nestled on one of the largest beachfront properties in the bay, spanning 2/3 acres and 4,200 square feet of luxurious living space. Registration Number: 000055003031, TA-048-589-8240-01, GE-048-589-8240-01
6 bedroom
5 bathroom
Sleeps 14
Hawaii> Laie
The property offers a peaceful retreat on a one-acre lot featuring a small mango orchard with 30 trees, and an array of fruit trees including lime, lemon, orange, and avocado. The beautifully manicured landscape creates a serene environment, ideal for relaxation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
5-bath vacation home. The property offers a covered lanai and outdoor dining area perfect for enjoying meals with stunning mountain vistas. Inside, vaulted ceilings, a formal dining room, smart TVs, and a dedicated workspace make it ideal for both relaxation and work.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
Experience the ultimate Maui getaway at Makena Surf G-201, a luxurious oceanfront condo on one of the island's most prestigious properties. Located on the second floor of the G building, this end-unit boasts panoramic views of a secluded cove and the expansive Pacific Ocean, providing a serene and private setting for your holiday.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
You can truly have it all at this stunning beachfront condo in Maalaea’s sought-after Kanai a Nalu resort. Situated in the sunniest, driest part of Maui, this property offers unparalleled proximity to the waves, with breathtaking panoramic views of the ocean, Haleakala volcano, and neighboring islands such as Lanai, Kahoolawe, and Molokini.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Wailuku
Escape to this charming 1-bedroom, 1-bath condo at Kiahuna Plantation, where tropical breezes and sunshine create an idyllic setting. The condo is thoughtfully furnished and offers access to a range of resort-style amenities including a sparkling pool, a fitness center, and direct beach access.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Escape to our secluded rainforest cabin located just 30 minutes from Hawaii Volcanoes National Park. Surrounded by lush greenery and bathed in rain—averaging 300 inches annually—our property offers a perfect retreat for nature lovers and adventurers alike.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Welcome to Holua Moana Hale, a beautifully remodeled oceanfront retreat nestled in the private community of Holua Kai on Hawaii's Big Island. This stunning two-story home offers breathtaking views of lush tropical landscapes and the sparkling Pacific Ocean, creating the perfect setting for a memorable getaway.
6 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua Kona
Experience the ultimate Hawaiian getaway in this stunning luxury oceanfront suite located on the Kalapaki Cliffs. From your private lanai, enjoy breathtaking views of Kalapaki Bay, the Haupu Mountain Range, and Nawiliwili Harbor, all just steps from pristine beaches and resort amenities.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lihue
Three story townhome corner unit located in Malia Kai condominiums w/private carport walking distance from just about anywhere in Kona! Share our private retreat and where you can work from Paradise with laptop workstation featuring a high end 32” screen and the fastest WiFi available in Kona!
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover this beautiful 2-bedroom, 3-bed condo offering a peaceful retreat with stunning island views. Recently updated with fresh furniture and decor, it provides a comfortable and welcoming atmosphere. Each building features a pool, tennis courts, and barbecue areas for outdoor enjoyment.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the beauty of Maui from Mana Kai Maui, a premier oceanfront resort located on the stunning Keawakapu Beach in Kihei. This beautifully appointed condo, unit 314, offers breathtaking views of South Maui's shoreline and the nearby Wailea resorts, making it an ideal retreat for travelers seeking luxury, comfort, and direct access to pristine beaches.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a luxurious stay in this stunning, fully remodeled 3-bedroom, 2-bathroom condo located in the desirable Grand Champions Villas in Wailea. The unit boasts all-new furnishings and modern upgrades, ensuring a comfortable and stylish retreat for your Maui vacation.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Wailea
Our commitment to you, our guests, is to provide a high level of satisfaction through attention to details, high quality amenities, and outstanding customer service. Please read on to discover what a special place our Ainamalu home truly is.
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Waikoloa
5-bath condo designed for comfort and elegance, accommodating up to four guests in the prestigious Kapalua resort. Upon entering, you'll find a beautifully curated living space that creates a warm, inviting atmosphere. The living room features a Queen sofa bed, providing extra sleeping options, and large sliding glass doors lead to a spacious lanai with breathtaking ocean views—ideal for morning coffee, al fresco dining, or enjoying spectacular sunsets over the Pacific.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Hanalei Bay Resort 7306! This spacious 1-bedroom condo with a sleeping loft provides breathtaking views of the ocean, mountains, and Kauai's famous North Shore sunsets. Located on the top floor, the condo offers some of the best vistas in the resort, allowing you to enjoy the scenic beauty from a Hawaiian-style living space.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to the Pineapple House, a charming and tranquil 2-bedroom, 1-bath cottage nestled amidst lush rainforest just 8 miles from Volcano Golf Course. This home seamlessly combines traditional Hawaiian charm with modern amenities, offering a cozy retreat for your Hawaiian adventure.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Welcome to Hale Mahina, a hidden gem nestled in Princeville offering a luxurious, single-level retreat with breathtaking views of the Nanolokama Mountains, Bali Hai, and the Makai Golf Course. This beautifully decorated home features Asian island decor, antiques, artwork, and Tommy Bahama furniture, creating a relaxing and elegant atmosphere.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
Welcome to this stunning dual-level luxury condo offering breathtaking ocean and sunset views, located just 10 minutes from Waikiki Beach. The Windsor Building features a beautiful lobby, fitness center, swimming pool, BBQ areas, putting green, and an incredible rooftop sky terrace with 360-degree vistas. Registration Number: TA-085-922-5160-01
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Honolulu
