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 15300, 50
Experience the best of Maui with this luxurious 2-bedroom oceanfront suite at Marriott's Maui Ocean Club, available for April 15-22, 2023. This listing features the newer towers—Lahaina and Napili—known for their full kitchens, in-unit laundry, and upscale furnishings.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
This unique tandem offering features two neighboring studio suites on the 5th floor, each with breathtaking ocean views from different angles. One suite faces the interior courtyard, showcasing spectacular vistas of the blue Pacific, Black Rock, and neighboring Molokai.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Discover a luxurious tropical retreat set on twenty-six lush acres infused with authentic Hawaiian design elements. This 2-bedroom villa comfortably accommodates up to 6 guests with 2 king beds and a sofa bed, perfect for families or friends seeking an authentic Hawaiian experience.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Studio Resort View
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience comfort and convenience in this beautifully appointed villa. The fully equipped kitchen with stainless steel appliances, including a stovetop, oven, microwave, and refrigerator with ice maker, makes meal prep easy. The master bedroom features a plush king bed, ceiling fan, TV, and access to the lanai, perfect for relaxing outdoors.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Noelani 115 is a charming ground-floor one-bedroom, one-bath condo nestled within the desirable Noelani Resort in Maui. This beautifully remodeled unit offers stunning panoramic ocean views and direct access to a lush pool deck, making it an ideal retreat for up to four guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a stylish and comfortable stay in this beautifully updated 2nd-floor villa in Kapalua. The unit offers easy access via 8 stairs from designated covered parking and features a spacious 1125 sqft layout.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This oceanfront condo at KPS 826 offers a perfect Maui getaway with breathtaking Pacific views and modern comforts. Nestled in Lahaina, it features a spacious bedroom with a plush king bed and a sofa bed in the living area, comfortably sleeping up to four guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a cozy and convenient stay at this charming boutique property in Waikiki. Each 300 sqft room offers two double beds, a mini-fridge, microwave, wet bar, and daily housekeeping, making it ideal for families or couples. Enjoy access to a heated outdoor pool, sun terrace, and BBQ facilities, perfect for relaxing after a day of exploring. Registration Number: 260170280065, TA-030-278-8608-01, GE-030-278-8608-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience the best of Waikiki Beach from this ideally located studio with lagoon and pool access. Perfect for couples seeking a relaxing island retreat, the property offers easy parking options—valet for the first night or public covered parking just two blocks away. Registration Number: GE-094-944-7168-01, TA-094-944-7168-01, .126009001000
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This desirable 3-bedroom, 3-bath split-level villa offers breathtaking views of the Pacific Ocean and Hualalai Mountain. Located just 6 minutes by golf cart from the Four Seasons Resort, this beautifully decorated and well-maintained home provides a perfect retreat for up to 6 guests.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover the breathtaking beauty of Kaua'i from the comfort of the Westin Princeville Ocean Resort Villas. This elegant two-bedroom lockoff villa combines a spacious one-bedroom premium suite and a studio, offering versatile options for families or friends.
3 bedroom
1 bathroom
Sleeps 8
Hawaii> Princeville
This 2 bedroom 2 bath condo located on the ground floor is a remodel. With the remodel completed in December 2019, this condo is clean, modern and has an upscale beachy vibe. The stylish kitchen has grey modern cabinets and Calcutta quartz countertops.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to our beautiful 3-bedroom, 2-bath condo on Maui, where you can feel the refreshing ocean breeze and listen to the waves. The spacious wrap-around lanai offers stunning, unobstructed views of the ocean, making it ideal for relaxing and dining al fresco.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to 𝐅𝐢𝐧𝐝𝐢𝐧𝐠 𝐌𝐞𝐢𝐥𝐢! Reserve your Luxury Beachfront Escape at Poʻipū Beach.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Experience the perfect blend of relaxation and adventure at this beautiful Kauai retreat nestled in Princeville's serene landscape. The open-concept living space features contemporary furnishings with a modern island decor, vaulted ceilings, and seamless indoor/outdoor flow through glass sliders leading to a covered lanai.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Escape to Violet Sunset 2802, a beautifully renovated two-bedroom, two-bath oceanfront suite nestled against the stunning Kona coast. Spanning 1,639 square feet, this spacious condo offers a perfect blend of island charm and modern comforts, making it an ideal retreat for your Hawaii vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Escape to this stunning 2-bedroom Maui condo in Kihei, rated 5 stars on other platforms. Located just steps from one of Maui's most beautiful beaches, this property offers the perfect blend of comfort and convenience. Walk to nearby restaurants, shops, and enjoy the tropical surroundings.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Escape to Hyatt Ka'anapali Beach and indulge in luxurious villas offering one to three bedrooms, full kitchens, and private lanais. Each unit features warm wood tones complemented by tropical prints, creating a welcoming and elegant atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Hawaiian getaway in this newly listed luxurious ground-floor condo at Waikoloa Beach Resort. Spanning 1500 sq ft, this elegant retreat offers breathtaking ocean and golf course views from your expansive private lanai. Located just a short 10-minute walk from the ocean and vibrant local markets, the condo provides easy access to beaches, shopping, dining, and outdoor adventures.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Step outside your front door to enjoy breathtaking views of Diamond Head and Kapiolani Park, with sweeping vistas of the Waikiki coastline from your lanai. This 450 sqft oceanfront studio offers a comfortable twin bed, perfect for relaxing while soaking in the fresh Hawaiian air. Registration Number: GE-083-994-7776-01, TA-083-994-7776-01, 310320070059
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
This memorable condo is the perfect home base for your trip to Lahaina. With 2 bedrooms featuring 1 king bed and 1 queen bed, along with 2 bathrooms equipped with showers and hair dryers, this fantastic property offers a comfortable stay. Enjoy the convenience of WiFi, a fitness room, coin laundry, iron, hair dryer, and a washing machine.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Escape to paradise with this luxurious oceanfront 1-bedroom villa at The Westin Kaanapali Ocean Resort Villas. Spanning 900 sq. , the villa features breathtaking 240° ocean views from your private lanai, perfect for relaxing or watching spectacular sunsets.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This exquisite top floor of a large two-story, eight-bedroom villa offers 1,900 sq. ft. 5 baths. The expansive covered deck features sliding glass walls that open fully to breathtaking ocean views, creating a seamless indoor-outdoor living experience.
4 bedroom
4 bathroom
Sleeps 9
Hawaii> Kihei
Experience the beauty of Kapalua's Bay Course from this spacious 2-bedroom, 2-bath Golf Villas Gold. Situated along the fairway, the villa offers partial ocean views and a comfortable, fully equipped interior that makes for a perfect vacation retreat.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This listing features two adjacent charming plantation-style cottages at the International Colony Club, perfect for families or groups seeking a spacious, comfortable island escape. Located at the base of the prestigious Kaanapali Hillside, these fully equipped cottages provide a relaxing retreat close to Kahekili (Airport) Beach, renowned snorkeling spots, and Maui’s top attractions.
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Discover the perfect retreat in Honolulu with this charming studio that offers a comfortable living space, a queen bed, and a sofa bed for versatile sleeping options. Guests can unwind by the pool or relax on the deck, enjoying the warm Hawaiian sunshine. Registration Number: GE-091-897-7024-01, 260120030035, TA-091-897-7024-01
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Your cozy getaway awaits in Kailua-Kona at this fabulous apartment. 5 bathrooms with convenient amenities like a hair dryer and shower, and a variety of extras like WiFi and a washing machine. You're sure to make fantastic memories with a stay at our place.
1 bedroom
2 bathroom
Sleeps 3
Hawaii> Kailua-Kona
Experience a cozy and inviting studio apartment offering breathtaking mountain views, located just a short 5-minute walk from the beach. The neighborhood is friendly and convenient, with easy access to popular North Shore beaches, scenic hiking trails, and delicious local restaurants. Registration Number: TA-061-999-6160-01, GE-061-999-6160-01, 54019040
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Hauula
Discover the beauty of Honolulu from this inviting 2-bedroom resort. The spacious rooms include two king beds, ensuring a restful night's sleep, while the two bathrooms with showers add convenience. The cozy living room features a sofa bed, perfect for extra guests or relaxing after a day exploring the island. Registration Number: GE-179-756-0320-02, TA-179-756-0320-02
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Looking for an unforgettable beachfront escape in Waikiki? This property offers two distinct hotel rooms, each approximately 360 sq ft, featuring two double beds, club lounge access, and daily housekeeping. Located right on the beach, you'll be just minutes from Waikiki's top attractions, vibrant nightlife, and scenic spots. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience the ultimate oceanfront escape at Portlock Hale, a stylish retreat nestled in the heart of Portlock, Hawaii Kai. This private residence offers direct access to the breathtaking shores of Maunaloa Bay via its own private staircase, making it ideal for ocean lovers and adventure seekers alike. Registration Number: 390160090000, GE-072-291-2768-01, TA-072-291-2768-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
Welcome to the Wailupe Beachfront Getaway by Gather, where island serenity meets luxury. This stunning five-bedroom, six-and-a-half-bath oceanfront estate in Honolulu offers a perfect blend of comfort, style, and adventure for up to ten guests. Registration Number: TA-148-851-7632-01, 350220050000, GE-148-851-7632-01
5 bedroom
6 bathroom
Sleeps 12
Hawaii> Honolulu
Welcome to Hale Kaiolu, a cozy condo situated on the fifth floor of Kaiolu Sunrise in the lively heart of Waikiki. This one-bedroom, one-bathroom unit offers a relaxing retreat with a beachy aesthetic, perfect for extended stays or remote working. Registration Number: GE-121-319-6288-02, TA-121-319-6288-01, 1260170600031
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Come and experience the vibrant beauty of Waikiki in this spacious 532 sq ft 1-bedroom ocean view hotel room. This unit features a comfortable king bed, daily housekeeping, a coffee maker, and a mini-fridge, ensuring a convenient and relaxing stay. Registration Number: GE-107-900-1088-01, TA-107-900-1088-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Located on the stunning shores of Kaanapali Beach, Marriott Ocean Club offers the ideal family vacation experience in Maui. This listing features two separate 2BR/2BA villas, providing ample space, comfort, and privacy for your stay. Each villa is beautifully appointed, with a master bedroom featuring a king-size bed and a second bedroom with either a king or double beds, along with a sofa bed in the living area.
4 bedroom
4 bathroom
Sleeps 16
Hawaii> Lahaina
The Kona Hawaiian Resort offers a perfect New Year's getaway from December 28 to January 4th. Nestled on Ali'i Dr, the property is just steps from the ocean, providing stunning views and direct access to some of the best beaches for swimming, snorkeling, and surfing in Kailua-Kona. Registration Number: TA-000-785-4592-01, GE-000-785-4592-01, 540050050001
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
Experience the ultimate beachfront stay in Waikiki with this conveniently located hotel offering two unique rooms, each 758 sq ft, with king beds, lanais, and modern amenities. Guests can enjoy a range of on-site facilities including a swimming pool, spa, and multiple restaurants, perfect for both relaxing and exploring the vibrant Waikiki area. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to your ideal Hawaii getaway in this spacious 10th-floor condo offering stunning ocean views from the balcony. The condo features a fully updated open kitchen with solid surface counters, a central island with a stove, oven, and stainless steel refrigerator, plus a microwave, coffee maker, toaster, blender, tea kettle, and complimentary tea to start your mornings.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
He'eia Haven - Unit A offers a charming Hawaiian-inspired retreat in Kailua-Kona, perfect for families or couples seeking a relaxing getaway. The home features soaring cathedral ceilings and expansive floor-to-ceiling windows that flood the space with natural light, creating a bright and airy atmosphere throughout.
2 bedroom
4 bathroom
Sleeps 5
Hawaii> Kailua-Kona
Immerse yourself in the beauty of Waikiki with this charming condo located in one of the most desirable spots on the island. Featuring a comfortable queen-size bed and a fully equipped kitchen with a standard refrigerator, this unit provides all the conveniences of home for a memorable stay. Registration Number: 260270310081, GE-182-732-1856-01, TA-182-732-1856-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the perfect blend of Hawaiian luxury and stunning ocean vistas at Mauna Kea Villas, a beautifully appointed single-level residence just a short walk from Mauna Kea Resort. The villa features a spacious sunken living room with a wet bar and sliding glass doors that open to a private deck, complete with a pool and BBQ area ideal for relaxing or entertaining.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Discover the perfect beachfront escape in Waikiki with this trio of unique hotel rooms, ideal for families or romantic retreats. Each unit offers 360 sqft of comfort, featuring two double beds, club lounge access, daily housekeeping, a coffee maker, mini-fridge, and all linens and essentials provided for a hassle-free stay. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
This stunning property offers a luxurious, modern 4-bedroom, 4-bathroom retreat just steps from the calm waters of Lanikai Beach. Designed for comfort and privacy, it comfortably accommodates up to 8 guests seeking tranquility in one of Hawaii’s most exclusive neighborhoods.Registration Number: 430060440000, GE-053-385-4720-01, TA-053-385-4720-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Welcome to your perfect Maui getaway at the Kaanapali Beach Marriott Ocean Club. Located along the famous Kaanapali Beachwalk, this spacious one-bedroom villa offers breathtaking ocean and mountain views, modern comforts, and resort-style amenities.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui vacation in this recently renovated, fully furnished 2-bedroom/2-bathroom top-floor corner condo at Kana'i A Nalu. Located just steps from Sugar Beach, the longest pristine beach in Maui, this condo offers spectacular unobstructed ocean and outer island views from every room.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
Experience the best of Maui at Marriott's Maui Ocean Club - Lahaina Villas, a top-rated family-friendly resort offering spacious accommodations with stunning ocean or mountain views. 5-acre super pool complete with water slides, waterfalls, and a dedicated children's area, ensuring fun for guests of all ages.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Escape to the Big Island's White Sands Village, a delightful condo perfect for families or couples seeking a tropical retreat. This spacious home comfortably sleeps 4 across two private bedrooms, offering a blend of comfort and breathtaking ocean vistas from your private lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover this inviting 1st-floor condo in Hawaii, perfect for a relaxing beach escape. The unit features a spacious balcony and an updated open kitchen with granite counters, a white fridge, stove/oven, microwave, dishwasher, cookware, and tableware, making meal prep easy and enjoyable.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience the perfect Kauai vacation in this beautifully appointed condo at Hanalei Bay Resort. Featuring two bedrooms, two bathrooms, and private lanais, this air-conditioned unit offers stunning views of sailboats in Hanalei Bay during summer and waterfalls cascading down Namolokama during rainy season.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
