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 15800, 50
This beautiful 5 bedroom, 5 bath for 10 offering, is situated right above famous Anini Beach, with an 8 minute walk down the Anini Beach trail just steps away from the property. Short walk to the Princeville playground and restaurants and shops as well!
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Princeville
Welcome to this charming Lahaina townhome, your ideal base for exploring Maui. This inviting property offers three bedrooms, including two king beds and one queen bed, providing comfortable accommodations for up to six guests. The home features modern amenities such as split ductless AC, a fully equipped kitchen, high-speed Wi-Fi, and three smart TVs, ensuring a convenient and enjoyable stay.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
This charming studio condo in Waikiki offers breathtaking ocean views and modern amenities for a comfortable stay. Located on the 27th floor, it’s prime positioned in the west area of Waikiki, just a 5-10 minute walk to popular beaches like Kahanamoku Beach and Hilton Village Lagoon. Registration Number: 260110080083, TA-099-018-1888-01, GE-099-018-1888-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Aloha from Midway Vacations! We're here to make your Hawaiian getaway unforgettable with expertly maintained, professionally cleaned properties and 24/7 support. Welcome to your perfect Hawaiian retreat! This charming 1-bedroom condo located in Waikiki Banyan Tower 2 (Mauka) offers a thoughtfully designed space ideal for families and friends. Registration Number: 260250050515, TA-005-016-1664-01, GE-005-016-1664-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the vibrant city of Honolulu from this inviting ground-floor 1-bedroom apartment. The cozy living area includes a sofa bed, ideal for relaxing after a day of adventures. The bedroom offers a comfortable queen-sized bed to ensure restful nights. Registration Number: TA-110-588-3136-01, GE-110-588-3136-01, 127013040000
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Welcome to our stunning coastal retreat in Waikiki, a perfect blend of luxury and comfort with breathtaking ocean views. This newly remodeled beach house has been thoughtfully designed by a professional interior decorator to create a serene and stylish escape. Registration Number: TA-054-205-3888-01, GE-054-205-3888-01, 260100070306
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the charm of Maui at Kaanapali Shores 732, a stylish studio condo situated on the seventh floor with breathtaking partial ocean views. This tropical-inspired retreat comfortably sleeps up to four guests, featuring a luxurious Queen-size bed with premium linens and a flat-screen TV for relaxing evenings.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to this beautifully located ground floor condo at Waikomo Stream Villas in Poipu. The unit offers tranquil views of lush tropical grounds and the gentle Waikomo Stream, creating a peaceful retreat. Its prime location near Poipu Beach, just a 5-minute walk away, places you within easy reach of world-famous beaches, top-rated restaurants, boutique shops, and a variety of outdoor adventures.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Discover the ultimate Maui getaway at Maui Kaanapali Villas #515, a beautifully furnished one-bedroom ocean view condo located on the fifth floor with stunning vistas of the Pacific. This sophisticated suite combines tropical decor, wood accents, and artful touches to create a relaxing atmosphere.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Breath-taking Views of the Ocean! Enjoy dreamy ocean breezes, the sound of crashing waves and spectacular unobstructed ocean views from this direct beachfront 8th story penthouse condo! 3 Bedrooms, 2 bathrooms, 3 lanais (balconies)-2 with ocean views and 1 with mountain views. Registration Number: GE-140-087-5520-01, TA-140-087-5520-01
4 bedroom
2 bathroom
Sleeps 14
Hawaii> Hauula
Discover this beautifully remodeled 3-bedroom, 3-bathroom townhome in Puamana, just a 30-second walk from the beach and surf breaks. Located on the safe side of Puamana, untouched by recent fires, this coastal retreat offers stunning ocean views from both upstairs primary bedrooms and the open-concept living area downstairs.
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Lahaina
Welcome to Waikiki Shore, your ideal ocean-front escape in the heart of Waikiki! This beautifully appointed 1-bedroom, 1-bathroom condo offers breathtaking ocean views and direct access to the world-famous Waikiki Beach. Step into a spacious, comfortable unit equipped with modern amenities designed to enhance your Hawaiian vacation.Registration Number: GE-078-028-0832-01, 1260040120130, TA-078-028-0832-01, STR# 2628
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
- First floor end unit with lanai that walks out to the lawn
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Introducing Ocean Villa Emilie by Gather, a newly constructed architectural marvel situated along Kalanianaole Highway in Honolulu. This contemporary estate seamlessly combines sleek design, luxurious finishes, and expansive ocean and mountain vistas, offering an exceptional Hawaiian escape.Registration Number: 360020020002, GE-010-996-6336-02, TA-010-996-6336-01
5 bedroom
5 bathroom
Sleeps 11
Hawaii> Honolulu
Experience a relaxing getaway in this spacious Garden View 1-Bedroom Villa offering 1022 square feet of comfort. The villa features a full kitchen equipped with stovetop, oven, microwave, refrigerator, dishwasher, coffeemaker, toaster, cookware, and tableware, making it ideal for preparing meals during your stay.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Aloha! Discover your perfect Maui getaway at our beautiful Wailea Ekahi Villa, a spacious and private tropical retreat that combines comfort with convenience. Nestled above one of Maui's most stunning beaches, this villa offers an idyllic escape with the feel of a private oasis amidst 35 acres of lush gardens and open grassy spaces.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience breathtaking ocean views from our spacious One-Bedroom Ocean View Suite, ideally situated high above Downtown Honolulu. This newly renovated 798 sq ft suite offers a perfect mix of luxury and comfort, featuring stunning vistas of the ocean, harbor, and city skyline. Registration Number: TA-119-862-2720-01, GE-119-862-2720-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this beautifully renovated condo on the top floor, offering breathtaking mountain and city views from your private balcony. The space features a comfortable king-size bed, a cozy armchair, and modern amenities designed for relaxation and convenience.Registration Number: 766456789012, GE-654-456-7811-01, TA-321-546-8190-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the best of Maui at Marriott's Maui Ocean Club - Lahaina Villas. These spacious 2- and 3-bedroom villas feature fully equipped kitchens, upscale furnishings, and stunning ocean or mountain views. Ideal for families or groups, the villas sleep from four to ten guests, with options including king beds, queen sleeper sofas, and full-size beds.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the best of Kihei, Maui from The Blue Tide House, located within the popular Kihei Kai Nani condos! Located on the first floor near the pool. Just steps across the street from the island's beautiful beaches, you can easily enjoy the sun, sand, and surf.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover the vibrant heart of Honolulu with this stylish 2-bedroom, 2-bathroom apartment located on the 16th floor of Sky Ala Moana. The property offers a perfect blend of city living and natural beauty, with panoramic city views from a spacious private lanai. Registration Number: 230160480083, GE-092-554-1888-01, TA-092-554-1888-01
4 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Located within the prestigious North Fairways at Mauna Kea Resort, this charming 4-bedroom residence offers breathtaking panoramic ocean views along with views of Maui and the Kohala mountains. The open-plan living space features a spacious kitchen with stainless steel appliances, perfect for hosting gatherings or private chefs.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kamuela
Welcome to your dream vacation rental at Kaanapali Alii! This luxurious two-bedroom paradise offers a perfect blend of comfort, style, and breathtaking views for your Maui getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to your perfect Maui retreat in this beautifully maintained fifth-floor condo offering breathtaking ocean and Haleakala views from the private lanai. Recently upgraded in August 2024, the unit features a brand new split AC system with individual controls in both the living room and bedroom, ensuring a cool and comfortable stay regardless of the tropical heat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Kaanapali Shores 721 offers a cozy and stylish studio experience on Maui, accommodating up to 3 guests. Located on the seventh floor, this condo features an open-concept layout with tropical-inspired decor that evokes island serenity. The luxurious California King-size bed, complemented by premium linens, ensures restful nights, while a flat-screen TV provides entertainment options.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Immerse yourself in the natural beauty and comfort of Maui at Kaanapali Villas #237. This charming one-bedroom, two-bath loft offers stunning garden views and easy access to North Kaanapali Beach, making it an ideal retreat for couples or families seeking relaxation and adventure.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this private, fully furnished studio in the heart of Waikiki, perfect for solo travelers, friends, or couples. Located on the 8th floor of a condominium building just one minute walk from the iconic Waikiki Beach, this unit offers a convenient and comfortable stay. Registration Number: 260270310144, TA-099-018-1888-01, GE-099-018-1888-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Your adventure begins on the stunning Kona Coast with three unique oceanfront hotel rooms at this family-friendly Big Island resort. Each 420 sq ft room offers two double beds, daily housekeeping, a coffee maker, mini-fridge, and all linens and essentials, ensuring a comfortable stay.
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Kailua-Kona
Discover the stunning Ridge Villas Ocean View 1-Bedroom, perched above Kapalua Bay with breathtaking views of the fairway. This spacious 1200-1500 sqft villa offers a perfect retreat with a fully equipped kitchen including stovetop, oven, microwave, refrigerator, coffeemaker, cookware, and tableware.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This beautifully renovated 2-bedroom, 2-bath upper-floor corner villa at Hualalai offers breathtaking wrap-around views of Hualalai Mountain, the 5th hole of the Keolu Golf Course, and Punawai Pond. The expansive windows and 16-foot sliding pocket doors flood the space with natural light, creating a bright, airy, and welcoming atmosphere.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Discover this stunning split-level villa just steps from Oneloa Beach, offering breathtaking ocean views and luxurious comfort. The property features a spacious 1200-1500 sqft layout with a blend of carpeted living areas and tiled kitchen and bathroom floors.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Valley Isle 1204, a charming 1-bedroom, 1-bathroom condo perched on the 12th floor, offering spectacular direct oceanfront views from your private lanai. With ceiling fans throughout and no A/C, this unit is designed for comfort and natural airflow.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
5-bath corner-end unit at Waikiki Landmark's Makai Tower. This residence offers breathtaking city and partial ocean views, providing a stunning backdrop for your stay. The thoughtfully renovated kitchen features modern and contemporary finishes, including custom cabinets with sleek quartz countertops, high-end stainless steel appliances, and a fully stocked setup with cookware, silverware, and small appliances like a coffee maker and air fryer. Registration Number: GE-153-115-5968-01, TA-153-115-5968-01, 126014030116
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Hale o ka Nene by Gather, a luxurious six-bedroom, six-and-a-half-bathroom estate on the sunny South Shore of Kaua'i. Perfect for families or group getaways, this private retreat accommodates up to 15 guests and features panoramic golf course views, a private pool and spa, and seamless indoor-outdoor living.
6 bedroom
7 bathroom
Sleeps 15
Hawaii> Koloa
In the master bedroom is a queen bed, A/C, ceiling fan, and full bathroom. Bedroom 2 queen murphy bed, A/C, and ceiling fan. There is a/c in the living room, a ceiling fan, flat screen TV, and Blu-ray / DVD player. Also enjoy meals on the ocean view lanai!
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Enjoy a comfortable stay in this spacious, newly renovated studio with a king-size bed, located just steps from Waikiki Beach, Honolulu Zoo, and Kapiolani Park. The unit offers a modern kitchen with stainless steel appliances and ample space for meal preparation, making it ideal for a relaxing vacation. Registration Number: GE-143-330-3552-01, TA-143-330-3552-01, 260270200047
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
- 2nd floor condo with balcony
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Discover this beautifully appointed 2-bedroom, 2-bath condo with partial ocean views, perfect for up to 4 guests. The spacious living area features custom hardwood flooring and floor-to-ceiling windows that flood the space with natural light. The master bedroom offers a comfortable queen bed, while the guest bedroom also has a queen, making it ideal for families or friends traveling together. Registration Number: 260030140040, GE-190-547-3536-01, TA-190-547-3536-01
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to this spacious 2-bedroom, 2-bathroom loft condo at Maui Kamaole J209, your perfect Maui getaway. The open living space creates a welcoming atmosphere, while the large primary bedroom with a King bed offers comfort and privacy. The guest room, equipped with a Queen and Twin beds, comfortably sleeps up to 6 guests, making it ideal for families or friends traveling together.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the vibrant heart of Waikiki from this beautifully remodeled 1-bedroom condo located on the 22nd floor of Waikiki Banyan Tower 2. The unit comfortably accommodates up to four guests with two Queen-size beds and features a spacious private balcony offering partial ocean views, perfect for relaxing after a day at the beach. Registration Number: 1260250050642, GE-131-649-9456-02, TA-131-649-9456-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Maui getaway in Hokulani 223 at Honua Kai, a spacious condo featuring one of the largest ocean-facing lanais on the property. This unique residence offers direct access to a stunning, expansive lanai that’s exclusive to this floor, providing an unparalleled outdoor living experience with breathtaking views of the Pacific and neighboring islands.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautifully renovated 4th-floor penthouse condo in South Kihei, offering stunning views and a prime location just steps from the picturesque Kamaole I Beach Park. The condo features two bedrooms and one bathroom, with the main living area and owner's suite equipped with air conditioning for your comfort.
3 bedroom
1 bathroom
Sleeps 5
Hawaii> KIHEI
Welcome to Kaanapali Alii 156, where luxury seamlessly blends with spectacular ocean views in a prime corner location. This beautifully appointed condo offers expansive vistas of the beach, pools, lush tropical gardens, and the sparkling Pacific Ocean, making it an ideal retreat for travelers seeking comfort and beauty.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Wailea Point 2301, an exquisite ocean view residence located in the heart of Wailea, Maui. This beautifully renovated two-bedroom, two-bath condo offers the perfect blend of elegance, comfort, and stunning scenery. Situated on the ground floor with a spacious 1,584 sq.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Step into this cozy 435 sqft Hawaii studio designed for comfort and convenience. The kitchen includes a stovetop, microwave, refrigerator, coffeemaker, blender, rice cooker, cookware, and tableware, making meal prep easy during your stay. The space features two twin beds that can be converted into a king bed, plus a futon sofa, accommodating up to 2 adults and 2 children under 3 years old. Registration Number: TA-192-173-6704-02, GE-192-173-6704-02, 260040120061
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the beauty of Royal Mauian, an oceanfront retreat with unobstructed vistas of the crystal-clear Pacific. Beachfront to the renowned Kamaole Beach, indulge in breathtaking sunsets, snorkeling, refreshing swims, romantic beach strolls, and seasonal whale watching.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate Maui retreat in this luxurious oceanfront villa offering breathtaking panoramic views of Molokai. Spanning 1,300 square feet, Villa 34G4 is perfectly situated on the ground floor with easy exterior access, ensuring seamless relaxation and convenience.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapalua
Enjoy a cozy garden view studio featuring an open kitchen equipped with stove, oven, refrigerator, microwave, coffeemaker, toaster, blender, and tea kettle with complimentary tea. The space offers a comfortable king or queen bed, a single sofa bed, and a guest bathroom with either a shower or tub/shower combo.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
This private, ground-floor residence at Mauna Kea Residences offers a seamless indoor-outdoor island lifestyle with a spacious great room featuring sliding glass pocket doors that open to a wraparound lanai. The chef’s kitchen is fully equipped with high-end appliances from SubZero, Fisher Paykel, and Wolf, including a gas range, perfect for culinary enthusiasts.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
Welcome to your slice of paradise in Maui, Hawaii! Tired of crowded beaches? Say aloha to this charming beachfront condo located just steps from a pristine white sandy beach off the beaten path. This tranquil haven offers the perfect escape from the hustle and bustle, while still being conveniently close to all the essentials.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
