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 16150, 50
Experience the perfect blend of comfort and style at Castle Bamboo Waikiki Hotel #1006. This thoughtfully designed studio offers a minimalistic yet vibrant space, beautifully accented with artwork showcasing local tropical scenes. The functional layout maximizes space, furnished with premium furniture, including a plush king bed with high-quality bedding, two small dressers, and a dedicated workstation for business or leisure. Registration Number: 260230670083, TA-116-104-4992-01, GE-116-104-4992-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This beautifully remodeled one-bedroom, one-bath corner unit at Maui Vista is your ideal island escape! This spacious condo offers extra windows for abundant natural light, lush garden views, and cooling Maui breezes—plus the added privacy of a quiet end-unit.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the perfect Maui getaway in this luxurious 5-bedroom residence, combining a premier 3-bedroom ocean view unit with a nearby 2-bedroom suite, ideal for large families or groups of up to 16 guests.
7 bedroom
5 bathroom
Sleeps 16
Hawaii> Lahaina
Discover the vibrant charm of Waikiki from our beachfront hotel rooms, just steps from Kuhio Beach and Diamond Head. Each 338 sq ft room offers stunning ocean views, a private lanai, and thoughtful amenities like a mini-fridge, Keurig coffee maker, and free bottled water, ensuring a comfortable and convenient stay.Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Mokulua Seaside by Gather, a luxurious retreat nestled just one house away from Kailua's pristine beaches on the tranquil east end of Mokulua Drive. This stunning four-bedroom, four-bathroom home comfortably accommodates up to ten guests, making it perfect for families or groups seeking a peaceful escape surrounded by natural beauty.Registration Number: TA-013-318-7072-01, GE-013-318-7072-01, 430040990000
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
Enjoy a luxurious stay at this stunning villa located closest to Oneloa Beach. The split-level loft style design features no stairs between parking and the front door, offering convenience and ease of access. Inside, tile floors throughout create a cool, inviting atmosphere, complemented by an updated kitchen equipped with granite counters, a farmhouse sink, fridge with ice maker, stove/oven, microwave, dishwasher, cookware, tableware, and a unique chef’s table counter for culinary adventures.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the serene beauty of Kauai Beach Villas, nestled along the pristine shores of Kauai in Lihue. This oceanfront resort provides a peaceful retreat surrounded by lush tropical gardens and freshwater lagoons, ideal for travelers seeking both relaxation and adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lihue
This elegant 1-bedroom, 1-bath villa offers a luxurious retreat in the heart of Waikiki at the Hilton Hawaiian Village.Registration Number: TA-151-395-1558-04, GE-205-637-5678-02, 497388072676
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the perfect beachfront escape in Waikiki with these convenient hotel rooms just minutes from top attractions. Each unit offers a comfortable 360sq ft space with a king bed, daily housekeeping, coffee maker, mini fridge, and all linens and essentials provided, so you can relax without a worry. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Set within the Hilton Waikoloa Village Resort, the Ocean Tower hotel offers a prime location at Waiulua Bay, providing easy access to resort amenities like three pools, a four-acre ocean-fed lagoon, on-site shopping, a full-service spa, and Dolphin Quest.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Welcome to Finding Meili! Escape to Tropical Opulence at the Kaanapali Residence Club on Kaanapali Beach! Reservations at this property require owners to rent our units out in 7-Night increments from SATURDAY-SATURDAY only. In case you're needing a stay shorter than 7-Nights, Finding Meili may be able to provide this special request, however, stays shorter than 7-Nights *may incur a higher nightly rate.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience a comfortable and convenient stay at the Westin Kaanapali Ocean Resort in this well-appointed studio unit. Located on the 5th floor, unit 7569 offers breathtaking ocean views and is the closest among properties in this category to the sea, providing an ideal setting for your Hawaiian getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Maui getaway at Marriott's Maui Ocean Club - Napili Villas, a popular timeshare resort perfect for families and couples alike. 5-acre super pool complete with thrilling water slides, waterfalls, and a dedicated children's area, making it an ideal spot for kids and those young at heart.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to the stunning shores of Kaanapali Beach with these two spacious 3-bedroom, 3-bath villas at Marriott Ocean Club. Each villa offers a luxurious master with a king bed, a second bedroom with a king and sofa bed, and a third with two full beds, providing ample space for family or friends.
8 bedroom
6 bathroom
Sleeps 24
Hawaii> Lahaina
Experience the beauty of Kauai at Hanalei Bay Resort, a sprawling 22-acre tropical paradise overlooking the picturesque Hanalei Bay on Kauai's North Shore. This listing offers a comfortable hotel room within the resort, perfect for travelers seeking convenience and stunning scenery.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
1 Bedroom Ocean View
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautifully appointed 1st-floor villa located in Kapalua, offering a blend of comfort and elegance. The property features a spacious layout with 1200-1500 sqft, perfect for up to four guests including children and infants. The villa is accessible via three stairs from the parking area, with plush carpeting in the living room, dining area, and bedroom, complemented by tile floors in the kitchen and bathrooms.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a luxurious stay at Honua Kai with this beautiful 1-bedroom ground-floor suite, Hokulani 138, offering expansive living space and stunning views of the West Maui Mountains or lush resort grounds. Perfectly positioned for easy access to the pools, beach, and outdoor amenities, this suite provides a serene retreat on Maui's North Beach.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Kaanapali Alii 122, a beautifully appointed one-bedroom residence offering partial views of the sparkling Pacific Ocean in the distance. This inviting condo features a spacious open living area with an oversized sectional sofa perfect for relaxing or socializing, complemented by tropical decor and large windows that frame the scenic surroundings.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate Hawaiian retreat at Honu Hideaway, a luxurious and spacious 2-bedroom, 2-bath penthouse offering stunning panoramic views of Waikiki, Diamond Head, and the Pacific Ocean. The condo's expansive floor-to-ceiling windows and two lanais provide breathtaking vistas of the vibrant cityscape and shimmering ocean, perfect for relaxing or entertaining. Registration Number: TA-055-572-7360-01, GE-055-572-7360-01, 260160390200
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Experience the ultimate beachfront stay in Waikiki with three distinct hotel rooms, each offering 360sf of space, two double beds, and partial ocean views. These rooms are perfect for families or couples seeking comfort and convenience. Daily housekeeping ensures a hassle-free stay, while amenities like a coffee maker, mini-fridge, linens, towels, and bathroom essentials are provided, so you can settle in and relax from the moment you arrive.Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Discover the vibrant charm of Waikiki from our beachfront hotel rooms, just steps from Kuhio Beach and Diamond Head. Wake up to stunning ocean vistas and immerse yourself in Hawaiian culture and adventure. Each room offers thoughtful amenities like a mini-fridge and Keurig coffee maker, ensuring a comfortable stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience a relaxing stay in this spacious ground floor condo located in a beautiful Hawaii resort. The unit offers a large private 473 square feet deck, perfect for outdoor dining and lounging. Inside, the fully equipped kitchen includes a stovetop, oven, microwave, refrigerator, dishwasher, coffeemaker, toaster, cookware, and tableware, making meal preparation effortless.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Welcome to Foster Tower, a stunning 2-bedroom, 2-bath condo perched on the 14th floor at 2500 Kalakaua Ave, managed by Ali'i Beach Rentals. This property requires a minimum stay of 30 days and offers a perfect blend of comfort, style, and prime location in Waikiki. Registration Number: 260260140071, TA-160-169-3184-01, GE-160-169-3184-01
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Discover this spacious garden view 1-bedroom, 2-bath condo perfect for up to 4 guests. The condo offers 898 sqft of comfort with a full kitchen featuring granite countertops, a Wolf cooktop, convection oven, microwave, and a two-drawer Fisher-Paykel dishwasher.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Escape to the lush Hawaiian rainforest with this exclusive villa offering a truly authentic experience. Nestled in a private setting, the property provides a peaceful retreat just 15 minutes from the renowned Kona white sand beaches and airport.
5 bedroom
3 bathroom
Sleeps 17
Hawaii> Kailua-Kona
Escape to this stunning two-bedroom, two-bathroom condo in the gated Kaanapali Royal complex, ideally situated in the heart of Kaanapali Resort on Maui. Spanning 1,534 square feet, this tropical haven offers breathtaking ocean views from a spacious 500-square-foot lanai, perfect for enjoying year-round sunsets over the Pacific and the outer islands of Lanai and Molokai.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> LAHAINA
Welcome to your ideal Maui retreat in South Kihei! This beautifully renovated condo combines contemporary comfort with a warm, inviting atmosphere, perfect for couples, solo travelers, or business visitors. Located in the vibrant heart of Kihei, it’s just steps from some of Maui’s most stunning beaches, local cafes, shops, and cultural attractions, making it an excellent base for exploring the island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
FREE daily activities included with your stay value up to $570! Complimentary admission, every day of your stay*: Whale Watch (seasonal), Dinner Sunset Sail, Snorkel Sail, Alpaca Farm Tour, Pineapple Farm Tour, and All Day Electric Bike Rental.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Discover your perfect Maui getaway at Hyatt Ka'anapali Beach, a highly acclaimed resort just steps from the stunning sands of Ka'anapali Beach. This spacious 1200 sq ft two-bedroom top-floor suite offers breathtaking unobstructed ocean views, guaranteed on floors 9-12.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Lahaina, HI
Beautiful 5 bedroom plus 2 bonus sleeping areas for 23 pool home! Amazing location, across the street from Magic Sands Beach, and 1 block to White Sands Beach! This spacious and welcoming home with 5 Bedrooms and 3 bathrooms plus an office bedroom.
5 bedroom
4 bathroom
Sleeps 23
Hawaii> Kahaluu-Keauhou
Escape to this spacious ocean view 2-bedroom, 2-bath condo located on the 5th to 7th floors, offering stunning vistas and modern amenities. The private balcony provides the perfect spot to unwind while enjoying the scenic surroundings. Inside, the kitchen is fully equipped with a stovetop, oven, refrigerator, microwave, dishwasher, coffeemaker, blender, cookware, and tableware, making meal preparation effortless.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to this charming beachfront condo in Kihei, perfectly situated across from the pristine beaches of Kamaole Beach 1, 2, and 3. The property offers a relaxing atmosphere with a stunning sunset view from the sitting area built into the wall, ideal for unwinding after a day of adventure.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Kihei
Experience the comfort of Wyndham Waikiki Beach Walk, just a short stroll from the famous Waikiki Beach. This one-bedroom suite offers a fully equipped kitchen, spacious living and dining areas, and in-unit washer/dryer for convenience. Guests can enjoy the outdoor pool and hot tub, perfect for relaxing after a day of exploring. Registration Number: 26002015, TA-127-799-4496-01, GE-127-799-4496-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Aloha and welcome to our Big Island getaway. Our Villa is a part of the Waikoloa Colony Villas located within the exclusive Waikoloa Beach Resort. Located just eighteen miles north of the Kona Airport, this premier Resort is known for its central location to beautiful beaches, shopping and upscale dining.
2 bedroom
3 bathroom
Sleeps 5
Hawaii> Waikoloa
Experience the perfect Hawaiian getaway in this charming Waikiki condo located just a block from the famous Waikiki Beach. The unit offers comfortable sleeping arrangements with two queen-sized beds in the bedroom and a sofa bed in the living room, accommodating families or groups comfortably. Registration Number: 260250050519, GE-121-578-0864-01, TA-121-578-0864-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to Wave Rider Studio Apartment, your perfect tropical retreat nestled in a friendly neighborhood. This bright, airy space combines island charm with modern comforts, making it ideal for a memorable vacation. The studio boasts a king-size bed, tropical decor, and large windows that invite natural light and ocean breezes.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Welcome to this charming 3-bedroom retreat designed for relaxation and comfort. The house features a spacious layout with two twin beds and a king bed, making it ideal for families or groups seeking a peaceful escape. Modern conveniences include WiFi, air conditioning, and a washing machine, ensuring a comfortable stay regardless of the season. Registration Number: TA-090-473-8432-25, 248675489123, GE-090-563-2677-09
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
Discover the perfect island retreat in this charming Honolulu vacation rental, offering 1,400 sq ft of comfortable living space with stunning views of the Honolulu skyline and nearby beaches. This 3-bedroom, 2-bath home combines mid-century modern style with island charm, making it ideal for families or groups seeking a relaxing getaway.Registration Number: 340310250000, GE-187-170-8672-01, TA-187-170-8672-01
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
This inviting 1-bedroom condo at Waikiki Banyan is perfect for up to 4 guests seeking a comfortable Hawaiian retreat. The open layout features a cozy queen-sized bed and a living area equipped with a 65" HD Smart TV with Netflix and other channels, ensuring entertainment after a day on the beach. Registration Number: GE-101-642-2400-02, 260250050219, TA-101-642-2400-02
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your dream retreat in the heart of Ko Olina! This beautifully remodeled 3-bedroom, 3-bathroom townhome is nestled within the exclusive, gated community of Ko Olina Kai, offering a perfect blend of luxury, comfort, and island charm.Registration Number: TA-118-229-1456-01, 1910560050082, GE-118-229-1456-01
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Kapolei
Discover this stylish 8th-floor condo offering a relaxing retreat with a private patio. Inside, you'll find a cozy king bed and a queen sofa bed, perfect for restful nights. The vinyl plank flooring in the living and dining areas adds a modern touch, complemented by tile in the bathroom and kitchen.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Enjoy a relaxing stay at Tree Fern Escape, a charming and comfortable home just minutes from Volcano National Park. This cozy 3-bedroom, 2-bath property offers a perfect blend of comfort and convenience, making it an ideal choice for families or couples seeking a memorable Hawaiian getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Volcano
Experience the ultimate Hawaiian getaway at Ilikai Hotel 805, a luxurious private one-bedroom suite situated in the heart of Waikiki, Honolulu. This condo offers breathtaking ocean views and convenient access to Waikiki's famous beaches, shopping, and nightlife.Registration Number: GE-059-977-1136-01, TA-059-977-1136-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Perched where the waves meet the shore, this oceanfront retreat in Wailuku offers breathtaking Maui views from floor-to-ceiling windows. Watch whales breach during winter months and enjoy vibrant sunsets from your plush living room sectional. Step outside onto a private lanai, where gentle breezes and panoramic ocean sights create a perfect backdrop for morning coffee or evening relaxation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailuku
Experience a tropical retreat in this charming second-floor condo located right on Poipu Beach. The bright and airy space is decorated with tropical beach decor, creating a relaxing atmosphere. Step onto your private lanai, accessible via a dedicated stairway, and enjoy stunning views of the beach and ocean.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience a comfortable stay in this spacious 1-bedroom condo with a beautiful garden view. The unit includes a full kitchen equipped with a fridge, stove/oven, microwave, dishwasher, cookware, and tableware, making meal preparation easy and convenient.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This exclusive tandem offering features two beautifully appointed suites on the 7th floor, both with spectacular ocean views from a prime corner location. The 2BR Deluxe Oceanview suite boasts a spacious 300+ sq ft wrap-around balcony, perfect for enjoying panoramic vistas of the tranquil Pacific.
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Lahaina
Designed for relaxation and luxury, our condo features:
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Lahaina
Escape to a beautifully maintained 3rd-floor beachfront studio located above the golden sands of Kaimana Beach on Oahu's renowned Gold Coast. This highly desirable condo is perfect for those seeking a relaxing and convenient Hawaiian retreat.Registration Number: 310320070017, GE-139-896-5760-01, TA-139-896-5760-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
