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 15350, 50
Welcome to your perfect Hawaiian getaway in this stylish studio located on the 24th floor of Ala Moana Hotel, offering stunning city views and convenient access to Honolulu's top attractions.Registration Number: GE-113-709-0560-04, TA-113-709-0560-04, 1230380020838
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Hale Wailele – The Waterfall House
2 bedroom
2 bathroom
Sleeps 2
Hawaii> Princeville
Immerse yourself in the vibrant island life at our Kauai resort, perfectly situated on the stunning shores of Hawaii's most lush island. This 452 sq ft Preferred room features two comfortable Queen beds, a private balcony with breathtaking ocean views, a cozy sitting area, and a dining table—ideal for relaxing after a day of adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lihue
Experience the perfect Waikiki getaway in this beautifully remodeled corner studio located on the 35th floor of the Hawaiian Monarch. Boasting breathtaking views of Diamond Head, the Ala Wai canal, the ocean, mountains, and the vibrant cityscape, this condo offers a luxurious and convenient stay for all travelers. Registration Number: TA-109-150-0544-01, GE-109-150-0544-01, 26014032
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Gather your loved ones and unwind in this spacious 2-bedroom condo at Maui Schooner Resort in Kihei. With a full kitchen, A/C, and a private balcony or patio, it’s perfect for families or friends. Enjoy nearby beaches, a refreshing pool, hot tubs, and BBQ areas.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Kihei
The two-bedroom cottages at The Lodge at Kukuiula on Kauai provide a luxurious retreat with access to an award-winning private club. Enjoy world-class amenities including an 18-hole Tom Weiskopf golf course, a sprawling spa and wellness complex, a 10-acre organic farm, and multiple pools.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Experience this beautifully remodeled ground-floor condo in North Kaanapali offering stunning garden views and easy access to lush green spaces. Step outside onto the soft grass and enjoy a leisurely walk past serene koi ponds and well-maintained putting greens leading to the pool, Jacuzzi, and the sparkling ocean.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Located in the exclusive 1BR interior courtyard facing suite at Honua Kai Resort, Hokulani 513 offers breathtaking views of the turquoise Pacific Ocean and the neighboring island of Molokai from its 5th-floor vantage point. Step onto your spacious private lanai overlooking the lush resort grounds and pools, perfect for relaxing under the Maui sun.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
1 Bedroom Resort View with Den
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
3 Bedroom Piilani Suite
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Lahaina
Discover this beautifully renovated 6th floor condo located on the south side of the building, close to elevators and parking for your convenience. The private balcony offers a perfect spot to enjoy tropical breezes and scenic views. Inside, the kitchen boasts Corian countertops, a stovetop, double ovens, refrigerator, microwave, dishwasher, coffeemaker, blender, cookware, and tableware, making meal prep a breeze.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
5 miles south of Kailua-Kona on the Big Island offers a perfect blend of comfort, luxury, and natural beauty. With spectacular ocean views from two lanais, guests can relax while watching the surf, enjoying sunsets, and possibly spotting marine life like turtles, dolphins, or even whales.
5 bedroom
6 bathroom
Sleeps 14
Hawaii> Kailua-Kona
Experience breathtaking views of the beach and Diamond Head from this beautifully upgraded corner suite. The unit features an electric cook-top, elegant stone flooring, a full bathroom, and a wall-mounted SMART HDTV with Roku for your entertainment. Registration Number: 260270310074, TA-163-777-9456-01, GE-163-777-9456-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Tropical furnishings, island-inspired wall art, and blonde wood flooring create a welcoming and relaxed atmosphere in the Islands at Mauna Lani M1. This spacious two-bedroom, two-and-a-half-bathroom split-level townhouse offers 2,100 square feet of comfort, making it ideal for families or groups of friends seeking a memorable Hawaii escape.
4 bedroom
3 bathroom
Sleeps 5
Hawaii> Kamuela
Experience the best of Waikiki in this privately-owned 1-bedroom, 2-bath suite at Imperial Resort, located right in the heart of Waikiki Beach Walk. Just steps from the beach, shopping, and dining options, this well-appointed suite combines the comforts of home with luxurious resort amenities. Registration Number: GE-116-561-7664-01, TA-116-561-7664-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
This 6th-floor 2-bedroom, 2-bath condo at the iconic Ilikai Hotel offers stunning city and ocean views on the quieter side of Waikiki. Located just a 5-minute walk from Waikiki Beach and close to shopping, dining, and outdoor activities, this modern and airy space is perfect for a relaxing Hawaiian getaway.Registration Number: 260100070064, TA-028-881-5616-01, GE-028-881-5616-01
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Discover this spacious 2-bedroom, 2-bathroom villa at Westin Kaanapali Ocean Resort Villas, perfect for families and couples seeking privacy and comfort. The unit features two full king bedrooms, each with ensuite bathrooms, providing perfect privacy for two couples.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Retreat to this oceanview 1-bedroom condo at Maui Schooner Resort in Kihei. Enjoy modern comforts like air conditioning, Wi-Fi, a full kitchenette, and a private balcony or patio. Whether you're sipping coffee with sunrise views or relaxing after beachside adventures, this condo is perfect for couples or small families.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Enjoy a relaxing stay in this 3rd-floor condo located in Hawaii, offering a spacious 777 sqft layout with a balcony overlooking the surroundings. The condo features stylish tile and vinyl plank flooring throughout, creating a modern and easy-to-maintain environment.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Immerse yourself in the natural beauty of Hawaii with our custom minivan camper located in Kailua. Perfect for solo travelers or couples, this mobile retreat offers the comfort of home amidst stunning island scenery. The van features a plush 8-inch memory foam mattress with fresh linens, ensuring restful sleep after days filled with adventure. Registration Number: TA-304-451-9023-02, GE-223-011-9008-16, 99981440
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Escape to this charming Kihei Maui condo, offering a perfect blend of comfort and convenience for your Hawaiian vacation. The ground-floor outdoor lanai provides easy access to the swimming pool, just steps away, ideal for relaxing mornings or al fresco dining amidst lush tropical surroundings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this charming one-bedroom condo just a few blocks from the iconic Waikiki Beach. Renovated in Spring 2023, it offers a fully stocked kitchenette for preparing meals, a brand new walk-in shower, and a comfortable separate bedroom with a new King-size bed. Registration Number: 1260240240121, GE-012-140-5440-01, TA-012-140-5440-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this inviting Kihei condo, your perfect home-away-from-home in Maui. The newly-furnished bedroom features a plush king bed, a cozy LazyBoy chair, and a 40" smart TV for relaxing evenings. The spacious living room boasts a 50" wall-mounted smart TV, an oversized plush sofa, and a charming rattan chair—ideal for lounging or unwinding after your adventures.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kihei
Escape to Hale Kamaole 241, a cozy second-floor condo in South Kihei, Maui. This 1-bedroom, 1-bath unit comfortably accommodates up to 2 guests, offering a perfect blend of comfort and convenience.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover this inviting partial ocean view studio in Hawaii, offering a spacious 660-880 sq ft layout with a choice of a king or queen bed, plus a sofa sleeper for extra guests.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover this fantastic vacation rental in the vibrant center of Waikiki, offering great value and convenient amenities. Situated close to bus stops, shopping centers, parks, and popular tourist attractions, this property provides the ideal base for exploring Honolulu. Registration Number: 12303405, TA-432-654-8900-01, GE-099-371-2670-01
0 bedroom
1 bathroom
Sleeps 7
Hawaii> Honolulu
Discover the serene beauty of Moloka'i from a charming Polynesian-style hotel room nestled along Kamiloloa Beach. Set within a peaceful village, this 244 sq ft unit offers a comfortable king bed, a private balcony with garden views, and all essential amenities including a microwave, fridge, coffee/tea maker, and flat-screen TV.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kaunakakai
Konea 338 offers a serene and upscale Maui getaway, featuring a spacious one-bedroom suite with stunning Pacific Ocean views and lush resort grounds. The suite’s upgraded interior provides a blend of comfort and elegance, with large glass doors that open onto a generous lanai, allowing guests to immerse themselves in Maui’s warm breezes and tranquil surroundings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect Hawaiian retreat at Mauna Lani N3, where East Asian-inspired design seamlessly blends with tropical Hawaiian accents. 5-bathroom condo offers an inviting and stylish atmosphere from the moment you step inside. The interior features elegant wood flooring, pristine white-on-white furniture, and a striking floor-to-ceiling hand-carved Indonesian wood panel that sets a sophisticated tone.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Discover this charming 3-bedroom home in Hilo, ideal for a cozy and comfortable stay. The property features two queen beds and one king bed, offering plenty of space for families or groups. Inside, you'll find amenities such as an iron, washing machine, WiFi, and a hair dryer, ensuring your stay is convenient and relaxing.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Hilo
Welcome to Kai Mele, a charming beachfront retreat nestled on the beautiful shores of Kailua, Oahu. This quaint 3-bedroom, 2-bathroom home offers cozy comfort and stunning views of Kailua Bay, making it an ideal destination for a relaxing island escape.Registration Number: 430130320000, TA-160-294-7072-01, GE-160-294-7072-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
Welcome to Makena Surf G-103, a luxurious beachfront condo nestled in the exclusive Makena area of Maui. This beautifully updated ground-floor unit offers easy access and modern comforts, making it perfect for a relaxing Hawaiian getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Makena, Maui
Welcome to this spacious Garden View 1-Bedroom condo, offering 880 sqft of comfortable living space and a private balcony to enjoy the lush surroundings. Inside, you'll find a full kitchen equipped with a stove, oven, microwave, refrigerator, dishwasher, coffeemaker, cookware, and tableware, making meal preparation convenient during your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to your dream Hawaiian escape! This charming 4-bedroom home is located just four houses from the stunning Kailua Beach, offering the perfect blend of comfort, convenience, and natural beauty.Registration Number: GE-100-709-7344-01, TA-100-709-7344-01, 143015028000
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
5-bathroom home nestled in the scenic town of Koloa on Kauai's South Shore. Built in 2024, this contemporary residence offers breathtaking ocean and golf course vistas, blending modern elegance with the island's natural beauty. The spacious primary suite opens directly to the lanai, providing spectacular ocean views to wake up to each morning.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
Discover the charm of Waimea with this inviting 3-bedroom, 2-bath home set on a lush one-acre parcel. Surrounded by the tranquil beauty of the Kohala Mountains, this property offers a perfect blend of relaxation and outdoor fun. The expansive yard is ideal for volleyball and other outdoor sports, providing endless entertainment for family and friends.
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Waimea
Experience the perfect blend of comfort and island charm in this beautifully appointed sixth-floor condo at Castle Bamboo Waikiki Hotel. Designed with a welcoming island aesthetic, the unit features a spacious open-concept layout with handsome hardwood flooring throughout. Registration Number: TA-182-803-8656-01, GE-182-803-8656-01, 260230670043
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
This exceptional 4-bedroom deluxe oceanview suite combines two neighboring 7th-floor residences for over 2100 sq ft of luxurious living space. Each suite features spacious layouts, high-end finishes, and panoramic views of the Pacific Ocean visible from almost every room.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Experience the ultimate Hawaiian retreat in this exclusive private estate situated on two expansive acres of tropical paradise. The property is fully fenced and gated, offering privacy and security for a relaxing escape. Ideal for long-term stays, it requires a minimum of 30 consecutive days, making it perfect for those seeking an extended island experience.Registration Number: GE-086-083-2256-01, 16813260, TA-086-083-2256-01
7 bedroom
5 bathroom
Sleeps 15
Hawaii> Waialua
Welcome to Aloha Soul Beachfront, a stunning oceanfront condo located in the Pat's at Punalu'u community. This 1-bedroom, 1-bathroom residence offers 800 square feet of interior space and a spacious 128-square-foot covered lanai, perfect for enjoying Hawaii's beautiful weather. Registration Number: TA-009-233-8688-01, GE-009-233-8688-01, 1530080020025
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Immerse yourself in the luxurious comfort of OM Villas at Marriott Maui Ocean Club Towers, ideally situated on the stunning shores of Kaanapali Beach. This tropical haven offers spacious accommodations perfect for a memorable Hawaiian escape. Reservations are available in weekly (7-night) stays with arrivals and departures on Fridays, Saturdays, and Sundays, providing flexible planning for your holiday.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
This beautifully appointed upper-level residence titled “You had me at Aloha” offers a perfect blend of comfort and style with breathtaking ocean, mountain, and golf course views. The spacious great room features a sleeper sofa and sliding glass doors that seamlessly connect indoor living with the outdoor island breeze, making it ideal for relaxing and entertaining.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
Enjoy breathtaking oceanfront views at this luxurious Hyatt Maui property nestled on 40 acres of pristine beach. The resort offers endless activities including snorkeling, scuba diving, hiking, fishing, kayaking, jet skiing, and boogie boarding, ensuring there's fun for everyone.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Residence #226, a charming two-bedroom garden view retreat nestled within lush tropical landscapes. This beautifully decorated condo offers a peaceful escape suitable for families or groups seeking relaxation in paradise.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Aloha! Welcome to this stylish and centrally located studio at Ala Moana Hotel, offering stunning city views and convenient access to Honolulu's top attractions. Located on the 24th floor, this modern unit provides a comfortable retreat for travelers seeking both leisure and business stays.Registration Number: GE-074-856-1920-01, TA-074-856-1920-01, 1230380020831
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Make an inquiry to plan your 2024 or 2025 Hawaiian vacation at The Westin Princeville Ocean Villas. Availability is checked in real-time, and bookings for 2025 open 12 months in advance. Stay durations are typically 7 nights, with unit options varying from studios to two-bedroom villas based on availability.
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Princeville, Kauai
Immerse yourself in the lush beauty of Kauai at Wyndham Ka Eo Kai, a charming resort nestled amidst winding paths waterfalls streams and vibrant gardens overlooking Hanalei Bay. This tranquil oasis offers a perfect retreat from busy tourist spots, providing a peaceful paradise for families and friends alike.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to our beautifully renovated 1-bedroom condo located in Kahana, Maui, directly on the stunning shoreline of the Pacific Ocean. Situated within the Valley Isle Resort, this oceanfront retreat offers an ideal setting for a memorable Maui vacation with unobstructed ocean vistas and easy access to local attractions.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Located in the heart of Koloa, this charming air-conditioned condo offers a perfect retreat for your Hawaiian vacation. The one-bedroom sanctuary features a plush California king bed, ample storage, and a brand-new mango wood bedroom set, creating a cozy and modern space for restful nights.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Welcome to your perfect Kauai retreat at Anini Beach, one of the few homes directly in the beach neighborhood with County-permitted status. Located just across the road from a pristine, flat sandy beach, this charming home offers easy access to calm lagoon-like waters created by the North Shore's famous coral reef, ideal for swimming year-round.
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Kilauea
