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 11700, 50
Experience the ultimate Maui vacation in this beautifully upgraded top-floor penthouse suite at Honua Kai. Boasting one of the best one-bedroom locations, Hokulani 818 offers stunning partial ocean views from the spacious balcony and primary bedroom, complemented by scenic vistas of the lush West Maui Mountains.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This charming one-bedroom apartment offers a peaceful retreat on a working farm in South Kona, Hawaii. Situated in a side-by-side duplex, the cottage provides a serene setting with lush foliage, ocean, and mountain views. The elevation ensures cool temperatures, ideal for relaxation.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Captain Cook
Experience the allure of Noelani 105, a beautifully decorated 1-bedroom, 1-bathroom condo nestled on Maui's scenic coast. This ground-floor retreat comfortably hosts up to four guests, featuring a luxurious king-size bed in the bedroom and a queen sleeper sofa in the living area.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a comfortable stay at the legally permitted Waikiki Grand Suite 408, offering breathtaking ocean and Diamond Head views. This suite features a convenient kitchenette with an electric cooktop, microwave, and a full-sized refrigerator, making it ideal for both short and extended visits. Registration Number: 260270310045, GE-049-687-5520-01, TA-049-687-5520-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Discover this beautifully updated fourth-floor oceanfront condo at Maui Sunset, ideally located on over 5 acres in sunny Kihei. The unit features new flooring throughout 2023, offering a fresh, modern feel. From your private 114 sq foot view deck, watch Maui's vibrant life unfold from dawn to dusk, taking in breathtaking ocean vistas.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Kihei
Discover this charming and legal vacation condo located in the heart of Waikiki, Honolulu. This 1-bedroom unit comfortably sleeps 5 guests, offering a queen bed, twin bed, and sofa bed, making it ideal for families or friends. The fully equipped kitchen allows for convenient cooking, helping you save on dining out. Registration Number: 260280110114, GE-111-582-2592-01, TA-111-582-2592-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
Experience the perfect island escape at Island Dream Getaway, a cozy one-bedroom, one-bathroom condo nestled within Pono Kai Resort in Kapaa, Kauai. Ideal for couples, small families, or solo travelers, this serene retreat offers a tranquil garden view and modern comforts.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa`a
The Whaler on Kaanapali Beach #1062 is a recently renovated studio condo offering spectacular ocean views and modern comforts. Spanning 530 sq ft, this unit is situated on the courtyard side of Tower II, providing a peaceful yet central location.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Welcome to Casa de Emdeko #B221, a beautifully maintained second-floor condo perfect for a relaxing getaway in Kona. This 780-square-foot retreat comfortably accommodates 2 to 4 guests seeking island charm and comfort. The spacious living area features a California king bed in the primary bedroom and a queen-sized sleeper sofa with a memory foam mattress in the living room, along with a large flat-screen TV and high-speed WiFi for entertainment.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the best of Kauai at Nihi Kai 601, a bright and welcoming oceanfront villa located within a lush tropical garden resort. This two-bedroom, two-bathroom home offers a perfect blend of comfort, style, and convenience, making it ideal for families, friends, or couples celebrating special moments.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Koloa
This sophisticated oceanview home located within Mauna Kea Resort offers a seamless blend of indoor-outdoor island living. The spacious great room features sliding glass pocket doors that open to a large lanai, perfect for entertaining and relaxing while enjoying breathtaking views of the ocean, golf courses, and mountains.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> KAMUELA
Malia Kai#5 offers a unique opportunity for your Hawaii vacation, providing a peaceful and private townhome experience in the vibrant heart of Kona Town. This well-appointed unit features no upstairs or downstairs neighbors, ensuring a quiet retreat.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
5-acre tropical resort nestled along Maui's east coast. This studio condo in the Niihau Building offers breathtaking views of sunrises over the ocean from your private lanai. The condo is thoughtfully equipped with a kitchenette featuring a sink, microwave, mini-fridge, and coffee maker, ideal for preparing beach snacks or light meals.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Discover the best location on South Maui at Pacific Shores B-118, a charming and convenient beachfront condo. Situated directly across from Charley Young Beach and just a block from the popular surf spot ''The Cove,'' this first-floor unit offers easy access and stunning ocean breezes without the need for stairs.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to your tropical paradise at Outrigger Kauai Beach Resort, nestled directly on Nukolii Beach on the Garden Isle's East shore. This full-service 4-star resort offers a perfect blend of local culture and upscale amenities across 25 lush tropical acres, centrally located between Kauai's North and South shores for easy exploration.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lihue
Discover the perfect Hawaiian retreat at Kona Mansions, where you can enjoy stunning ocean views from a well-appointed 1-bedroom condo. This spacious unit comfortably hosts up to 3 guests, making it ideal for couples or small families seeking a relaxing getaway on the Big Island.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Immerse yourself in the tranquility of Seabreeze Hawaii Kai, an inviting home designed for extended stays with a perfect blend of outdoor beauty and modern comfort. The lush tropical garden and charming stone path lead you to a serene backyard featuring a tranquil koi pond, ideal for reading or unwinding amidst nature's splendor. Registration Number: 390850230000, GE-201-662-2592-02, TA-201-662-2592-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Escape to this beautiful beachfront condo in Maui, offering breathtaking views of the Pacific Ocean and nearby islands. This two-story, 2-bedroom plus loft unit provides all the comforts of home, making it an ideal retreat for families or groups seeking a relaxing island getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover paradise at Poipu Kapili #60, a beautiful corner condo nestled by the sea. Floor-to-ceiling windows flood the space with natural light and stunning ocean vistas, creating a serene and inviting atmosphere. The condo features a fully equipped kitchen, recently renovated in summer 2021, perfect for preparing delicious meals after a day at the beach.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Koloa
Menehune Shores is an oceanfront property located in North Kihei, offering breathtaking views of the Pacific Ocean and easy access to a sandy beach. This spacious and comfortable condo is ideal for families or groups seeking a relaxing retreat.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei, Maui
Discover the perfect getaway at The Cliffs on Kauai's North Shore, a stunning resort perched 200 feet above the Pacific Ocean. This spacious, fully furnished condo offers breathtaking panoramic views of lush mountains and the endless blue sea, providing an ideal backdrop for your island escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the ultimate Maui getaway at Hale Kai O'Kihei 219, a stunning oceanfront condo boasting 180-degree views of the Pacific Ocean. This beautifully remodeled property, updated in 2015, offers a luxurious and comfortable stay with modern amenities and breathtaking scenery.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the comfort of this spacious ground floor condo at Kauhale Makai 122, offering stunning garden, partial ocean, and mountain views. With a large bedroom featuring a queen bed and a fully equipped kitchen, this unit comfortably sleeps four, making it ideal for couples or families with children.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Book your Maui getaway at the Wailea Grand Champions Villas, a spacious and nicely decorated second-floor 2-bedroom, 2-bath condo. Enjoy stunning ocean, tennis court, and tropical foliage views from the living area, lanai, and master bedroom. The condo features a fully stocked kitchen perfect for preparing meals, whether staying in or enjoying a big breakfast.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Immerse yourself in the beauty of Hawaii when you stay at Pali Ke Kua #4, a charming condo with a loft perfect for four guests. This tropical haven offers all the amenities needed for a memorable vacation, including a fully equipped kitchen, in-unit washer and dryer, and Hawaiian-themed decor that transports you straight to paradise.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience a tranquil escape in this beautifully designed private home nestled amidst lush tropical gardens. Crafted by renowned architect Vladimir Ossipoff, the residence features open-plan layouts that capitalize on refreshing trade winds and stunning views of the Koolau mountains.Registration Number: GE-119-399-0144-01, TA-119-399-0144-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kailua
Experience the ultimate Hawaiian getaway in this spacious oceanfront 2-bedroom, 2-bathroom condo located in the iconic Ilikai Hotel in Waikiki. Spanning 1,000 sq ft, this condo comfortably accommodates up to 6 guests, making it ideal for families, friends, or remote workers seeking a relaxing retreat.Registration Number: TA-025-776-1280-01, GE-025-776-1280-01, 123126107407
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Experience island living in Castle Waikiki Shore #506, a recently upgraded one-bedroom, one-bathroom suite offering stunning ocean views. This condo combines modern comfort with convenient access to Waikiki's vibrant attractions.Registration Number: TA-209-898-7008-01, GE-209-898-7008-01, 260040120032
1 bedroom
1 bathroom
Sleeps 4
Hawaii> HONOLULU
Discover the beautifully updated Kona Bali Kai #264, a charming second-floor studio condo nestled in Kailua-Kona. This thoughtfully furnished unit offers a perfect blend of comfort and convenience, just steps from the stunning Banyans Beach. The private lanai provides a peaceful retreat with ocean views framed by swaying palms—ideal for morning coffee or sunset cocktails.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover this charming two-story Hawaiian home nestled in a quiet, gated community in Kona. 5 bathrooms, it offers a perfect blend of comfort and island elegance. The lush lanai provides a private outdoor space where you can unwind with a drink or enjoy the serene surroundings.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
The Ocean Breeze is a beautifully furnished 1-bedroom, air-conditioned bungalow perched directly on the ocean, offering breathtaking views of the North Shore. It features a private deck perfect for relaxing, an outdoor shower for freshening up after beach activities, and a California King Bed with high thread count linens for restful nights. Registration Number: TA-064-440-0128-01, 550020820000, GE-064-440-0128-01
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Laie
Experience the ultimate Kauai getaway at Pali Ke Kua 213, a beautifully renovated condo perched above the blue Pacific Ocean in Princeville's charming community. This inviting residence combines beachy elegance with modern comfort, making it an ideal home base for exploring Kauai's natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to our tranquil oasis nestled in the heart of downtown Kailua Kona! This stylish condo is perched on a hill, offering spectacular ocean vistas and effortless access to the lively town. Step onto the private lanai and take in the stunning beauty of the Pacific Ocean, where you can watch vibrant sunsets or simply enjoy the gentle ocean breeze.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Welcome to your perfect getaway at Waikiki Palms! This stylish 1-bedroom condo offers stunning ocean and Diamond Head views, all at an unbeatable price. Inside, you'll find a spacious, clean, and modern space designed for comfort and relaxation. Registration Number: TA-064-911-8720-01, 260120030260, GE-064-911-8720-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
The Hale Kai 115 offers breathtaking ocean views and easy access to the pool, making it an ideal retreat for couples or small families seeking a Maui getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honokowai
Escape to Sealodge E-5, a charming oceanfront condominium nestled amidst lush tropical foliage and meticulously maintained grounds. Perched on the edge of a bluff, this property offers breathtaking panoramic views of the Pacific Ocean, creating a serene and picturesque setting for your vacation.
2 bedroom
2 bathroom
Sleeps 2
Hawaii> Princeville
Discover this relaxing oceanfront condo in Kona, offering spectacular direct ocean views and luxurious updates throughout. Inside, you'll find custom solid cherry wood cabinets and interior doors, gleaming granite countertops in both kitchen and bathrooms, and vessel bathroom sinks that add a touch of elegance.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kailua Kona
Discover the charming Ilikai #611, a cozy cottage-style studio located at 1777 Ala Moana Blvd in Honolulu. This 500 sq ft interior space features a semi-private bedroom area, a spacious balcony, and modern amenities designed for comfort and convenience. Registration Number: 260100070584, GE-126-723-7888-01, TA-126-723-7888-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Villa on 5th Ave in Paradise, a stunning newly built tropical retreat designed for comfort and relaxation. 5 bathroom house offers 2,800 square feet of coastal charm, perfect for families or groups up to 8 guests. The villa features a modern, fully equipped kitchen with stainless steel appliances, a large lanai for outdoor living, and beautiful floors throughout that add to its warm, inviting atmosphere.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Keaau
Welcome to your tropical paradise at Sea Village in Kailua-Kona, Hawaii! This charming one-bedroom, one-bath condo offers breathtaking ocean views from the living room and private lanai, creating an idyllic setting for relaxation. Located just minutes from downtown Kailua-Kona, you'll enjoy easy access to local shops, dining, and vibrant attractions. Registration Number: GE-087-513-4976-01, TA-087-513-4976-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua
Experience the beauty of Kauai from this charming upstairs 1BR/1BA condo in Poipu. The unit features lovely wood floors and a private lanai with ocean views, perfect for relaxing evenings. The fully equipped kitchen allows for convenient home-cooked meals, or enjoy nearby dining options like Kalapaki Joe's and Brennecke's Beach Broiler, both within walking distance.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Experience the best of Maui at Sugar Beach Resort 309, a beautifully appointed beachfront condo with breathtaking ocean views. Located directly on five miles of pristine white-sand beach, this condo offers a perfect retreat for relaxation and adventure.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Escape to the tranquil paradise of Maui at Kapalua Golf Villas 16V3, a beautifully appointed luxury villa nestled in the heart of Kapalua. This inviting retreat offers stunning golf course views, bright and airy interiors filled with natural light, and tasteful ocean-themed decor that creates a calming coastal ambiance.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the comfort and convenience of Hale Hui Kai, a beloved Maui beachfront condo community. This well-appointed unit features central air conditioning with window louvers, keeping noise out and cool air in for a restful stay. The master bedroom offers a luxurious King bed, while the second bedroom has two twin beds, making it ideal for families or groups.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover your ideal vacation escape in this beautifully furnished condo located on the 12th floor of the iconic Ilikai Hotel in Waikiki. Boasting spectacular views of the Pacific Ocean and the Ala Wai Yacht Harbor, this unit provides a perfect blend of comfort and convenience for your Hawaiian holiday.Registration Number: GE-214-178-8160-05, TA-214-178-8160-05
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Maui getaway at Kauhale Makai Penthouse 607, a stunning oceanfront condo offering breathtaking sunrises and sunsets from your private balcony. This luxurious penthouse features an open-concept living space with modern touches, including a well-equipped kitchen with granite countertops, a center island, and stainless steel appliances, perfect for family meals or entertaining.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
The Maui Sunset offers a top-floor suite that captures the essence of island living. Step onto the large private covered lanai deck after a day of exploring or unwind in the air-conditioned living room with ocean views. Situated in the popular B side building on the 5th floor, this spacious condominium provides a perfect retreat with a private bedroom and attached bathroom, plus a second bathroom with a shower down the hall.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the perfect getaway in this beautifully furnished tropical-themed home designed for comfort and relaxation. The spacious layout includes three bedrooms, a master with a king bed, a second bedroom with two full beds, and a third with a king bed, accommodating groups of up to 8 or more.
6 bedroom
3 bathroom
Sleeps 16
Hawaii> Orchidlands Estates
Welcome to your perfect Hawaiian getaway in this spacious 2-bedroom, 2-bathroom condo located on the 17th floor of the prestigious Ilikai Hotel. With breathtaking views of the Pacific Ocean, Duke Kahanamoku Lagoon, and Ala Wai Yacht Harbor, you'll experience the best of Waikiki from this corner unit. Registration Number: GE-109-740-0320-01, TA-109-740-0320-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Experience breathtaking ocean views from every room in this beautifully furnished condo at Hale Kai O Kihei. The private lanai offers stunning vistas of sunrise and sunset, perfect for unwinding after a day of exploring Maui. The resort features an inviting oceanfront pool, ideal for relaxing under the sun, along with bocce ball, shuffleboard, and barbecue facilities to enjoy outdoor fun with family and friends.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
