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 10900, 50
Welcome to this comfortable and modern 12th-floor 1-bedroom suite located at 2425 Kuhio Ave in Honolulu. Perfectly designed for your convenience and comfort, this spacious unit offers privacy with solid sliding dividers that can separate the living area from the bedroom, which features a cozy sofabed. Registration Number: 260230670092, GE-034-088-7552-01, TA-034-088-7552-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Alohana at Waikiki Shore #610 offers a luxurious and comfortable beachfront experience on Oahu. This spacious 1-bedroom, 1-bath condo is perfectly situated directly on Waikiki Beach, providing stunning panoramic views of the Pacific Ocean, iconic Diamond Head, and Fort DeRussy Park. Registration Number: GE-078-028-0832-01, 260040120046, TA-078-028-0832-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Enjoy breathtaking ocean and sunset views from this beautifully remodeled Waikiki condo located just steps from the iconic Waikiki Beach. The Ilikai Suites offer a luxurious, tastefully furnished experience with secure entry and complimentary Wi-Fi. Registration Number: GE-191-280-9472-01, TA-191-280-9472-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikiki
Discover this gorgeous direct oceanfront condo in Kapalua, renovated in July 2024, offering stunning views and luxurious comfort. Whether you're planning a romantic getaway or a family vacation, this spacious 2-bedroom, 2-bathroom condo provides the perfect retreat.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this spacious 2-bedroom, 2-bath condo located in the heart of Waikiki, ideal for up to 6 guests. The master bedroom features a plush King bed, while the guest bedroom offers two double beds, and a sofa bed provides additional sleeping space. Registration Number: GE-150-457-3440-01, TA-150-457-3440-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Discover this beautifully appointed 1-bedroom condo on the 43rd floor of Island Colony, offering breathtaking 180-degree panoramic ocean views from the living room and lanai. The space features a fully equipped contemporary kitchen seamlessly integrated into the sleek design of the unit. Registration Number: 260210260285, GE-060-827-0848-01, TA-060-827-0848-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Introducing Kihei Resort 110B, your perfect escape to tropical tranquility in Maui. This charming ground-floor 1-bedroom, 1-bathroom condo comfortably hosts up to four guests, making it an ideal choice for couples or small families seeking a peaceful Hawaiian retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this charming ground floor condo at Hale Hui Kai, perfectly positioned just 40 steps from the stunning Keawakapu Beach in south Maui. This ocean view unit offers a seamless blend of comfort and natural beauty, making it an ideal getaway for families, couples, or groups seeking a serene beachside retreat.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your perfect Hawaiian getaway in Poipu, just a short 7-minute walk to Shipwreck Beach and 15 minutes to Poipu Beach along the Green Belt Path. This spacious home offers three separate suites, ideal for extended families or groups seeking privacy and comfort.
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
Experience the ultimate Hawaiian getaway in this exquisite 26th-floor penthouse at the Ilikai Hotel, located at 1777 Ala Moana Blvd in Waikiki. Situated at the far western end of Waikiki, the property offers easy access to a variety of local attractions, including Waikiki Harbor, the Hilton Hawaiian Village, and Fort DeRussy Park. Registration Number: TA-151-555-2768-01, 1260100070546, GE-151-555-2768-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
5-bath townhome nestled in the beautiful Princeville area on Kauai's North Shore. Ideal for families and friends, it comfortably sleeps up to 8 guests, featuring two king beds, two twin beds, and a queen sleeper sofa in the den. The open and inviting floor plan includes a large kitchen with granite countertops, perfect for preparing memorable meals.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
Experience an unforgettable oceanfront escape in this beautifully appointed ground-floor condo located on Maui's stunning west coast. The residence is decorated with high-end art, furniture, and finishes that radiate elegance and sophistication, creating a luxurious retreat just steps from the crystal-clear waters of the Pacific Ocean.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Discover this charming Honolulu studio located at 445 Seaside Ave, offering breathtaking partial views of the ocean, mountains, city, canals, and rainbows. Designed for up to 4 guests, this condo provides a comfortable and convenient stay in the heart of Waikiki. Registration Number: 126021026558, TA-156-024-6784-01, GE-156-024-6784-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
This exceptional beachfront property offers breathtaking ocean views and direct access to a pristine, family-friendly beach. The spacious two-level house comfortably accommodates two families seeking privacy and comfort.Registration Number: TA-199-621-0176-01, GE-199-621-0176-01
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Waimanalo
Experience the perfect Maui vacation in this beautifully remodeled condo at Kaleialoha Resort. This 1-bedroom, 1-bath unit accommodates up to 4 guests with a queen bed and sofa sleeper, offering a comfortable and stylish retreat. The condo features tiled floors throughout, granite countertops, brand-new fixtures, and Hawaiian-inspired decor, creating a relaxing island ambiance.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Enjoy a luxurious stay at Noelani 305, a spacious and beautifully decorated three-bedroom, two-bathroom condo offering stunning ocean views and tropical finishes. The fully equipped kitchen features full-size appliances, granite countertops, and all essentials for preparing meals at home.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the perfect Hawaiian retreat at Ilikai #923, a beautifully renovated 500-square-foot studio located at the iconic Ilikai Hotel in Waikiki. This spacious, open-concept condo seamlessly combines comfort and convenience, offering a restful haven for up to four guests.Registration Number: TA-009-414-3488-01, 260100070623, GE-009-414-3488-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This beautifully furnished condo offers everything needed for a relaxing stay, whether short or long. Inside, you'll find a new, fully stocked kitchen with stainless steel appliances, pots, pans, blender, and all the comforts of home. The space is designed to feel like your own apartment, providing privacy and safety on the second floor of building four.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
This exceptional four-bedroom property combines a beautiful 3BR oceanfront suite with a spacious 1BR unit, accommodating up to 12 guests comfortably. Located in the prime Konea Tower, the suites offer breathtaking year-round sunsets over the Pacific and Maui's neighboring islands, Lanai and Molokai, viewed from a massive 600 sq ft wraparound balcony.
6 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Escape to a charming island retreat designed for romance and relaxation. This stylish 1-bedroom, 2-bath condo in Wailea offers a tranquil atmosphere perfect for couples seeking a peaceful getaway. Just a short stroll from beautiful beaches and close to fine dining and boutique shops, this property invites you to indulge in the ultimate Maui experience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
The Cliffs 4-207 at Princeville offers a beautifully furnished 1-bedroom, 2-bath condo with breathtaking views from two private lanais. Perched on a cliff above the Pacific Ocean, this retreat provides spectacular scenery year-round, including winter whale watching, playful dolphins, and vibrant birdlife.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Immerse yourself in the beauty of Hawaii with this beautifully upgraded 1-bedroom condo at Kona Reef. Located in the desirable C building, C-22 offers stunning views overlooking the pool and ocean, where dolphins, whales, and turtles frequently appear.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Escape to the beautiful east shore of Kauai at Castle Kaha Lani Resort #107, a recently renovated 739 sq ft corner suite that offers breathtaking floor-to-ceiling windows showcasing stunning sunrises, soothing trade winds, and the rhythmic crashing of waves.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> LIHUE
Welcome to Luana Kai D305, a beautifully redesigned oceanfront condo perfect for your Maui vacation. Located just steps from a lush green lawn leading to a pristine sandy beach, this top-floor unit offers spectacular panoramic views of Maalaea Bay, Molokini, Kahoolawe, Lanai, and Molokai Islands, as well as the West Maui Mountains.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the ultimate Maui getaway in Suite 218 at Honua Kai, a spacious and luxurious retreat offering breathtaking peekaboo views of the Pacific Ocean and West Maui Mountains from a large lanai. This suite is perfectly positioned to maximize southern exposure, providing warm morning sunshine and protection from the northeast trade winds, ensuring a comfortable and relaxing stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
5-bathroom Volcano home offers a perfect blend of comfort, privacy, and natural beauty. The property boasts an outdoor grilling and dining area, ideal for enjoying meals al fresco, along with a six-person hot tub nestled under a custom gazebo for year-round relaxation.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Volcano
Experience the ultimate island retreat at Wailea Elua 2109, a beautifully furnished 2-bedroom, 2-bath condo perched atop one of South Maui's most renowned snorkeling beaches. This hotel-zoned property remains unaffected by recent legislation, offering you a worry-free vacation with all the comforts of home.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover this charming Kihei vacation home, perfect for families, groups, or couples seeking a relaxing Maui retreat. The property offers a private pool, spacious living areas, and a fully equipped kitchen to cater to all your needs. Lounge by the pool or prepare a delicious meal on the lanai while soaking in the stunning Maui scenery.
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kihei
Imagine waking up to the soothing sounds of the ocean at Napili Shores E240, a beautifully remodeled condo nestled on Maui's northwest shore. Sip your coffee as the water fountain from the Koi Fish Pond provides a calming backdrop, complemented by the fragrant Plumeria flowers blooming just outside.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a serene Hawaiian escape in this beautifully renovated oceanfront condo at Sugar Beach, Kihei. The condo offers stunning ocean views from your private lanai, where you might even spot whales during season. Located in a charming, peaceful area away from the busy crowds yet still close to lively spots like Wailea, Lahaina, and Kaanaapali, it’s perfect for both relaxation and exploration.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate island getaway in this beautifully renovated second-story condo at Turtle Bay. The unit features a spacious 1-bedroom plus loft layout with two bathrooms, high ceilings, and tropical trade winds that keep the space fresh and inviting. Registration Number: 570010290151, TA-090-094-0800-01, GE-090-094-0800-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
Experience the best of Maui at this charming 1-bedroom condo in the Hale Kamaole complex, offering stunning ocean views from the private lanai. The spacious unit, just over 900 sq ft, comfortably sleeps four with a king-sized bed and a pullout sofa bed in the living room.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the beauty of Maui from this stunning Studio/1-bath condo at Maui Eldorado, located in sunny Kaanapali on Maui's west side. Recently renovated in April 2023, this condo offers a comfortable and fully equipped space for up to 4 guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to our cozy, air-conditioned Beachcomber Studio nestled within a lush quarter-acre tropical landscape, offering a serene retreat just a five-minute walk from the stunning sands of Poipu Beach. Located in the heart of Poipu, this area is renowned for its beautiful beaches, excellent snorkeling, boogie boarding, and surfing spots suitable for all levels.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Welcome to our Deluxe Oceanview Waikiki Condo, ideally situated at the beginning of Waikiki next to the iconic Hilton Hawaiian Village. From the privacy of your spacious lanai, indulge in breathtaking ocean views while sipping your favorite tropical drink. Registration Number: GE-064-575-2832-01, TA-064-575-2832-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Step into this spacious 1-bedroom, 2-bath condo at Kona Alii, ideally situated in the heart of downtown Kailua Kona Village. The unit boasts a beautifully remodeled kitchen with exotic solid granite countertops and stainless steel appliances, perfect for preparing meals or entertaining guests.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kailua-Kona
5-bath townhouse situated on the third and fourth floors. This beautifully decorated condo features three lanais offering spectacular ocean and mountain views, making it the perfect retreat for relaxation and adventure. The home boasts a gourmet kitchen with a brand-new Sub Zero freezer, ideal for preparing delicious meals.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Experience breathtaking panoramic views from this newly remodeled top-floor condo in Waikiki. Large wrap-around windows frame stunning ocean and mountain scenery, perfect for waking up to sunrises and rainbows over the mountains. Sip coffee on the lanai while watching surfers paddle out or catch the early morning waves. Registration Number: GE-062-342-8608-01, TA-062-342-8608-01, 260260140137
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your beautifully renovated (2020) Aqua Palms Waikiki studio, offering stunning sea views, views over Fort De Russy Park, and vistas of Diamond Head. This stylish and comfortable space features a plush king-size bed, a 42-inch flatscreen LCD TV with cable channels, a dedicated desk and chair, and a cozy sofa for relaxing after a day exploring. Registration Number: 260120030245, TA-135-286-7840-01, GE-135-286-7840-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience the charm of this thoughtfully renovated 1960s Beach Bungalow, offering timeless appeal and peekaboo ocean views from the lanai and interior. Located just across the street from the stunning Kaanapali Beach, the property provides easy access to Maui's beautiful sands and waves.
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Lahaina
Experience the perfect Hawaiian escape at Kuilima Estates West, a beautifully remodeled 1-bedroom, 1-bathroom condo located at the entrance of Turtle Bay on Oahu's North Shore. This open-concept unit combines modern comfort with stunning views, offering an ideal retreat for couples or small groups seeking tranquility and adventure.Registration Number: 570010290104, GE-180-802-5088-01, TA-180-802-5088-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Welcome to Waikoloa Villas A107, your perfect Hawaiian escape nestled in the tranquil community of Waikoloa Village. Located just 15 minutes from Waikoloa Beach Resort, this beautifully decorated 2-bedroom, 2-bathroom ground-floor villa offers a serene retreat with stunning views of the 9th fairway on the scenic Robert Trent Jones II-designed golf course.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Kahana Reef 318 is a third-floor, two-bedroom, two-bathroom oceanfront condo that offers guests breathtaking unobstructed views of the Pacific Ocean. The unit features ceiling fans for comfort, a spacious master bedroom with a king-size bed, and a versatile living room with a queen-size sleeper sofa suitable for additional guests.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This RARE CORNER BEACHFRONT studio offers a unique Hawaiian escape with breathtaking ocean and sand beach views. Newly revitalized, it features handcrafted Balinese tamarind teak wood furnishings imported from Bali, creating a warm, traditional ambiance. Registration Number: TA-210-067-6096-01, GE-210-067-6096-01, 530080010063
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hauula
Welcome to your dream Maui getaway in this beautifully upgraded ocean view penthouse at Kihei Alii Kai. This top-floor corner unit comfortably sleeps six, featuring two bedrooms and one bathroom. The unit is professionally cleaned and offers a no-contact check-in and check-out process for your convenience.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Kihei
Discover this beautiful high-floor ocean view studio at Luana Waikiki, a charming boutique building nestled in the heart of Waikiki. The spacious room comfortably sleeps 4 with a queen bed and a full-size sleeper sofa, perfect for couples or small families. Registration Number: 1260060020217, TA-156-768-2560-01, GE-156-768-2560-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the beauty of Waikiki from this stunning oceanfront condo, just a short walk to the beach. The fully furnished and recently remodeled unit offers breathtaking ocean and sunset views from the lanai, perfect for memorable meals or relaxing evenings. Registration Number: GE-069-612-5440-01, TA-069-612-5440-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the comfort and charm of this spacious Maui vacation townhouse, offering 2 bedrooms and 3 bathrooms within a lush 34-acre resort. The open-concept design features a sunken living room with four sliding doors that invite in tropical breezes and showcase garden views, creating a seamless indoor-outdoor living experience.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Wailea
The Kaanapali Royal H303 offers a luxurious, spacious two-bedroom, two-bath condo perfect for families, couples, or friends seeking an unforgettable Maui escape. The open floor plan connects a fully equipped kitchen, a large living area, and a generous main lanai, creating an inviting space for relaxation and socializing.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
The Pineapple Princess is a beautifully appointed third-floor condo located within the exclusive Country Club Villas in Kona, offering breathtaking views of the ocean and the 18th fairway of the Kona Country Club golf course. Spanning 1,246 square feet, this spacious retreat features an open-concept kitchen, dining, and living area designed for comfort and convenience, making it an ideal home base for your Big Island adventure.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
