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 9650, 50
Escape to this charming ground floor two-bedroom condo at Pono Kai, offering stunning ocean views and direct access to a mile-long sandy beach. Privately owned and meticulously cared for, this island retreat provides a warm, inviting atmosphere with a thoughtfully decorated interior.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapaa
Ali'i Point #5 offers a serene oceanfront escape in a secure, gated community of 16 seaside homes. This spacious residence features central A/C, a comfortable living area, and a modern kitchen. The main floor includes a master king bedroom with an en suite bathroom, a living room, dining space, half bath, and large glass sliders opening to an expansive oceanfront lanai.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience the tranquility of living in a treehouse with this beautifully renovated 1-bedroom, 1-bath deluxe condo at Kiahuna, Building 35. Located on the top floor, it offers vaulted ceilings and panoramic views of lush trees and vibrant tropical flowers, creating a peaceful retreat just steps from Poipu's pristine beach.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Experience a beautifully remodeled and thoughtfully updated 8th-floor suite at Diamond Head Beach Hotel, completed in October 2022 with stylish touches throughout. This condo offers breathtaking ocean views and an unbeatable location for a relaxing Hawaiian escape. Registration Number: TA-112-953-0880-01, GE-112-953-0880-01, 310320290036
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate beachfront retreat at Kona Magic Sands#309, a beautifully maintained top-floor condo offering direct oceanfront views and luxurious amenities. This meticulously furnished unit is ideal for 1 or 2 guests seeking a serene island escape.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Immerse yourself in the true Hawaiian spirit with this stunning 2-bedroom, 2-bath condo located in the Keauhou Country Club complex. The property boasts breathtaking ocean views, where palm trees sway in the trade winds and the soothing sounds of birds create a peaceful atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Discover the perfect Waikiki Beach getaway in this beautifully remodeled 1-bedroom, 1-bath condo located just steps from the world-famous Waikiki Beach. The unit comfortably sleeps four and offers stunning peek ocean views from a private lanai, ideal for relaxing after a day in the sun. Registration Number: GE-165-376-6656-01, TA-165-376-6656-01, 1260230560040
0 bedroom
0 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to the Kuhio Cabana, a beautifully renovated one-bedroom condo perched on the 28th floor offering breathtaking views of Kuhio Beach and the Pacific Ocean. This comfortable unit is perfect for singles, couples, friends, or small families seeking a convenient and scenic base for exploring Oahu. Registration Number: TA-091-508-3264-01, GE-091-508-3264-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Honu Hale, a delightful waterfront island cottage nestled on the pristine shores of Hawaii. This charming home offers a perfect blend of comfort and natural beauty, making it an ideal retreat for your Hawaiian vacation.Registration Number: GE-174-700-7488-01, TA-174-700-7488-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimanalo
Discover this inviting second-floor studio condo in Hawaii, offering stunning ocean and garden views. This cozy retreat is a little piece of paradise, featuring a new queen bed, full bathroom, and a fully equipped kitchen with a microwave, coffee maker, and dishes for four.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Welcome to your tropical paradise nestled in the heart of Makaha Valley. 5-bath vacation home offers a perfect retreat for families and friends looking to unwind in a serene and private environment amidst Hawaii's stunning landscapes. Spanning approximately 1,935 sqft, the home features a full kitchen equipped for home-cooked meals, high-speed WiFi for staying connected, and a sanitized, contactless self-check-in process for your convenience. Registration Number: GE-070-901-9136-01, 840291500017, TA-070-901-9136-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Waianae
Escape to Kapa'a Sands 12, a charming ground-floor studio nestled right on the ocean in Kauai. This boutique property offers a tranquil setting with lush tropical gardens, a swimming pool, and direct beach access. The peaceful acre grounds are perfect for relaxing while listening to the waves and trade winds.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaa
Discover the perfect oceanfront retreat at Poipu Makai C1, nestled on a bluff overlooking the vibrant Pacific Ocean on Kauai's south shore. This intimate complex features only 15 condos, ensuring privacy and tranquility for your vacation. The spacious 1,100 sq ft ground-floor condo offers a single-level layout with split-system air conditioning in the living room and bedrooms, providing cool comfort throughout.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
Experience the perfect Hawaiian getaway in this beautifully maintained 2-bedroom, 2-bath condo located in Waikiki Beach Tower at 2470 Kalakaua Ave. Boasting 1092 sq ft of interior living space and a spacious 171 sq ft ocean-view lanai, this condo offers unobstructed views of the Pacific Ocean, making every sunrise and sunset a memorable experience. Registration Number: TA-080-021-5040-01, GE-080-021-5040-01, 260230070058
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Discover the perfect Maui escape at this charming two-bedroom condo in Kihei. Unwind on one of two oceanview lanais and watch the sunset, possibly catching a green flash. Step outside for a swim at Kamaole II Beach Park, just across the street, or enjoy the vibrant nightlife, shopping, and dining options just minutes away.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the serenity of Kauai at Kaha Lani Resort #114, a charming beachfront condo offering breathtaking ocean views and modern comforts. This ground-floor end unit provides a peaceful retreat with a spacious 739 sq ft layout, ideal for families or couples seeking a relaxing getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
Welcome to Waikoloa Shores, your perfect island retreat on the sunny coast of Waikoloa Resort. This beautifully updated one-bedroom, one-and-a-half-bath condo offers a blend of comfort, style, and convenience. Upon entering, you'll be greeted by fresh decor, new furnishings, and stunning mountain vistas visible from the spacious lanai.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
Experience the ultimate Maui getaway in this beautifully updated ground floor condo located directly across from Kamaole I Beach in Kihei. Spanning approximately 700 sqft, this one-bedroom unit combines modern comfort with stunning ocean views and convenient access to Maui's finest attractions.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your perfect Maui getaway at our charming ground floor condo in the heart of South Kihei. Located in the desirable Kihei Kai Nani complex, Unit #144 in Building 11, this well-cared-for condo offers a comfortable and convenient base for exploring the beautiful island of Maui.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Escape to this stunning Kailua beachfront villa that offers direct access to the renowned Kailua Beach, perfect for a luxurious and relaxing vacation. The property features five spacious bedrooms, each equipped with private split-system A/C units, ensuring comfort during your stay. Registration Number: TA-112-883-7120-01, GE-112-883-7120-01, 430170600000
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Kailua
Discover this spacious and modern corner villa nestled in a serene location with stunning views of the golf course and mountains. The multi-level layout ensures privacy, with no units above or below, providing a quiet retreat. Located just outside the Kings and Queens Marketplaces, it's ideal for shopping, dining, and entertainment, all within minutes.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This beautifully remodeled 1-bedroom, 1-bathroom condo offers a luxurious, single-level floor plan with modern furnishings and appliances. Located on the cooler side of the building, it provides a tranquil retreat just across the street from the stunning Kamaole Beach 2.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Immerse yourself in the beauty of Poipu with this charming second-floor condo at Kiahuna Plantation 106. Nestled amidst 35 acres of lush gardens and tropical lawns, this ocean-view retreat combines historic charm with modern comforts. The condo features a vibrant tropical decor, an open floor plan, and a fully equipped kitchen with stunning ocean vistas, creating a breezy, inviting atmosphere.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Escape to this peaceful and beautifully appointed 2-bedroom, 2-bath condo at Koa Lagoon on South Maui, known for its dry and sunny climate. The spacious living area offers a breathtaking 180-degree view of the surf, swaying palms, and vibrant tropical flowers, creating a perfect setting for your island retreat.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to Hale 'Olu'Olu, a stunning 2-level condo nestled within Waikoloa Beach Resort, offering an ideal retreat for families and groups. This spacious 1800 sq ft home boasts breathtaking panoramic views of the golf course and majestic mountains, creating a tranquil tropical setting.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa
Welcome to Honua Kai Hokulani 110, a beautifully updated, bright, and spacious condo nestled in the heart of Ka'anapali on Maui. This inviting property offers a perfect blend of comfort and convenience, ideal for families, couples, or groups seeking a memorable island escape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the perfect Hawaiian getaway at Ilikai Hotel 838, a beautifully appointed condo located in the heart of Honolulu. This stylish retreat offers breathtaking views of the ocean and city from a furnished balcony, making it an ideal spot for relaxation and sightseeing. Registration Number: TA-115-768-5248-01, 260100070130, GE-115-768-5248-01
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
This exquisite second-floor villa located in the Konea Building at Honua Kai Resort offers an ideal blend of modern comfort and breathtaking ocean vistas. Spanning 1,235 square feet, this two-bedroom, two-bath retreat features large Nano doors that open to a generous 475-square-foot lanai, creating a seamless indoor-outdoor living experience with unobstructed views of the Pacific Ocean and lush resort grounds.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Hawaiian getaway at Kona Reef Oceanfront Condo D6, a beautifully located unit just steps from the pristine beaches of Kailua-Kona. This condo offers stunning ocean vistas and easy access to some of Hawaii's most famous spots, including Magic Sands Beach Park, Kahalu'u Beach Park, and Kaloko-Honokohau National Historical Park, all within five miles.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Discover the perfect Maui getaway at our carefully maintained Kihei Kai Nani condo, ideally located just a minute's walk from the stunning Kamaole 2 Beach Park. This end-unit condo offers a bright, airy living space with a wall of louvered windows that invite natural breezes, complemented by efficient ceiling fans and optional air conditioning for comfort.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the perfect blend of comfort and rustic charm in this delightful Hanalei home, ideal for romantic escapes or family vacations. Surrounded by tropical foliage, the property features spacious vaulted ceilings in the living area that create an open, airy atmosphere.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Hanalei
This stunning 1-bedroom condo in Kona offers breathtaking sunset ocean views and a prime location in the heart of town. The recently renovated kitchen features modern appliances, and the bathroom boasts a new walk-in shower, providing comfort and convenience for your stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Experience the ideal Hawaiian vacation in this cozy 2-bedroom, 2-bath condo just a short walk from Kahaluʻu Beach, one of Kona's top snorkeling spots. This unit offers a perfect blend of privacy and convenience, nestled in a quiet complex that respects residents’ peace.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Aloha and welcome to our private banana tree house, a unique and tranquil cottage offering stunning views of the ocean and lush tropical valleys. Situated on a 20-acre estate, this charming retreat overlooks an active organic banana orchard, providing a peaceful and immersive Hawaiian experience.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kapaau
Discover this exceptional vacation house nestled on Hawaii's Big Island, just 33 miles south of Kailua-Kona near the charming village of Milolii. This home offers breathtaking ocean vistas and easy access to a variety of attractions including Volcano National Park, pristine beaches, ancient ruins, and Kona plantations.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Captain Cook
This stunning four-bedroom home in the peaceful Princeville community offers a perfect blend of comfort and natural beauty. Situated on a large lot adjacent to the 12th hole of Makai Golf Course, it boasts panoramic mountain and waterfall views from the lanai.
4 bedroom
3 bathroom
Sleeps 9
Hawaii> Princeville
Kihei Akahi A-102 offers a stunning oceanfront townhouse experience on Maui's sunniest coast. Fully renovated with modern upgrades, this upscale 2-bedroom unit features two spacious lanais, totaling 224 sq. , perfect for outdoor relaxation and sunset viewing.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Kaleialoha 415 is a beautifully appointed 1-bedroom, 1-bathroom corner condo on the 4th floor, offering unobstructed ocean views and a serene setting. Please note, this unit does not have A/C but features excellent natural light and a refreshing breeze.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Poipu Shores 104C offers a luxurious, fully oceanfront, two-story townhome on Kauai's stunning south shore. With over 1200 sq ft of space, this condo features two lanais, complete air conditioning, and exquisite decor, making it an ideal tropical retreat.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Koloa
Escape to Kahala Poipu Kai 312, a beautifully maintained ground-floor end unit offering stunning views and easy access to the beach. This two-bedroom, two-bathroom condo comfortably sleeps up to four guests and features an updated kitchen, tile floors, and a spacious lanai where you can enjoy the scenic surroundings.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Poipu
Aloha from Midway Vacations! Our local hospitality team is committed to making your Hawaiian getaway memorable, with professionally cleaned, well-maintained properties available 24/7. This inviting 1-bedroom condo at Waikiki Sunset Resort is perfect for families or couples seeking a true island escape. Registration Number: GE-202-106-0608-01, TA-202-106-0608-01, NUC #: 90/TVU-1287/1990/NUC-1454, 260280110184
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience stunning ocean views from this spacious vacation rental. The master bedroom offers a luxurious king bed, closet, and an ensuite bath with a walk-in shower. The second bedroom also features a king bed and ample closet space. The living room is perfect for relaxation, with ocean vistas, a queen sofa bed, a full sofa bed, two folding single beds, a smart TV, and a dedicated workspace with free Wi-Fi. Registration Number: GE-175-896-8832-01, TA-175-896-8832-01, 260100070000
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Honolulu
Welcome to your perfect Maui getaway in Unit #303 at Kihei Beach. This thoughtfully renovated condo offers a home-away-from-home atmosphere with modern touches and vibrant local art. The fully equipped kitchen makes meal prep effortless, whether you're enjoying a morning coffee on the private lanai with sweeping ocean views or cooking a sunset dinner.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your perfect Kihei escape! This charming condo offers stunning ocean glimpses from the lanai, where you can unwind and enjoy breathtaking sunsets. Located just a short walk to Charlie Young and Kamaole Beaches, it provides easy access to Maui's top sandy spots for swimming, sunbathing, and water activities.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Escape to a slice of Hawaiian paradise with this beautifully appointed 2-bedroom, 2-bath Lahaina condo. Located in the Kahana Villa community, this penthouse offers stunning ocean and mountain views from the private balcony, perfect for relaxing with a glass of wine after a day in the sun.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Come and unwind in this beautifully decorated 1-bedroom condo ideally located just steps from the pristine Kona beach. This charming unit offers a perfect blend of comfort and convenience, making it an excellent choice for families or couples seeking a relaxing Hawaiian getaway.
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Kailua-Kona
Escape to Vista Waikoloa D302, a spacious and modern two-bedroom condo nestled in the gated Waikoloa Beach Resort on Hawaii's stunning Kohala Coast. Perfect for families or couples seeking a relaxing getaway, this third-floor unit offers elevator access and all the comforts of home.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Beach Resort
Welcome to your ideal Kauai getaway! This spacious 2-bedroom condo offers stunning ocean views, just a short 5-minute walk to the beach and a vibrant area filled with shopping and dining options. Inside, you'll find a comfortable, well-maintained unit featuring three air conditioners—one in each bedroom and one in the living room—ensuring cool comfort during your stay.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Lihue, Kauai
Experience the perfect Maui getaway in this spacious and recently updated 2-bedroom, 2-bathroom condo located above the stunning Keawakapu Beach. Spanning approximately 1500 sq. , this unit offers a blend of tropical charm and modern comfort, making it an ideal retreat for couples or families.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Escape to Maui Sunset A-322, a beautifully situated condo offering breathtaking ocean views and direct access to nearby beaches and Waipuilani Park. The spacious living area features sliding glass doors that bring the stunning outdoors inside, allowing you to enjoy the tropical scenery from every angle.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kihei
