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 15950, 50
Discover this beautifully remodeled oceanfront condo in Kailua-Kona, Hawaii, featuring modern interiors and breathtaking views of the Pacific Ocean. Completed in 2024, this 1-bedroom unit combines luxury and comfort, perfect for a memorable Big Island getaway.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Experience true relaxation at Westin Nanea Ocean Villas Maui, a luxurious resort set on 26 acres of lush tropical landscape. The property seamlessly blends Hawaiian cultural elements with modern comfort, offering a perfect retreat for families, couples, and solo travelers alike.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Discover Maui's unique adventures, from volcanic heights to lush waterfall trails. At the Westin Ka'anapali Ocean Resort Villas, leverage our concierge's connections with top tour companies; your pre-arranged activity tickets await at the desk.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Discover the beauty of Waikiki from the Waikiki Marina Resort at Ilikai, a stylish condo resort ideally located near Ala Moana Center, Ala Wai Yacht Harbor, and Fort DeRussy. This charming studio apartment comfortably sleeps up to 4 guests, featuring furnished lanais, air conditioning, and free WiFi, ensuring a relaxing stay. Registration Number: 260100070381, GE-075-433-7792-01, TA-173-554-2784-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover this inviting 2nd-floor condo in Poipu Beach, offering a spacious 880 sqft layout with a cozy lanai. The unit features a comfortable bedroom with a king bed, a queen sofa bed in the living area, and modern amenities throughout. The fully equipped kitchen boasts granite countertops, a full stove and oven, microwave, refrigerator, dishwasher, coffee maker, blender, toaster, and ample cookware and tableware, making meal prep a breeze.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience a beautifully renovated condo at Regency on Beachwalk by Outrigger, offering modern comforts and stylish furnishings. The space features new wood laminate flooring, fresh lighting fixtures in the kitchen and entryway, and a contemporary design throughout.Registration Number: 260030140035, TA-005-754-0608-01, GE-005-754-0608-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This exceptional tandem listing features two luxurious top-floor penthouse suites at Honua Kai, offering breathtaking ocean views from nearly every room and an expansive 8th-floor vantage point. The 2-bedroom suite stands out with its deluxe corner floorplan, providing stunning vistas from the interior spaces and a massive 300+ sq ft wrap-around balcony, perfect for soaking in Maui’s natural beauty.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Lahaina
Our exquisite villa is situated within the prestigious Ritz Carlton, Oahu Turtle Bay Resort, offering direct access to a beautiful sandy beach just 25 yards away. Guests can enjoy exclusive use of the Ocean Villas pool and Jacuzzi, free parking, and zero resort fees. Registration Number: GE-171-186-7392-01, TA-171-186-7392-01, 570010130014
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kahuku
OM Villas proudly offers luxury villas at Marriott's Maui Ocean Club in Lahaina, available for weekly stays with Saturday arrivals and departures. Nestled along the stunning shores of Maui, this tropical retreat immerses guests in the welcoming aloha spirit amid pristine beaches, turquoise waters, and lush rainforests, creating an unforgettable Hawaiian vacation experience.
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Lahaina
Welcome to your ultimate beachfront escape on Oahu’s renowned North Shore! This expansive 7-bedroom, 6-bath multi-unit home offers a perfect mix of space, comfort, and island luxury, making it an ideal retreat for large families, multiple families traveling together, or groups of friends seeking a tropical getaway.Registration Number: GE-010-357-4626-01, TA-062-218-3318-01, 330006640000
7 bedroom
7 bathroom
Sleeps 22
Hawaii> Kahuku
Welcome to our charming 1-bedroom, 1-bath condo nestled in the heart of Kapaa, Kauai. This cozy retreat is perfectly situated for exploring the island, offering a fully equipped kitchen, a comfortable living space, and a private lanai where you can enjoy the tropical breeze.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Experience the epitome of luxury at this spacious two-bedroom oceanview villa within Disney Aulani Resort and Spa, situated in the upscale Koolina area of Oahu. Spanning over 1100 sq ft, this elegantly designed villa offers a perfect blend of comfort, style, and scenic beauty. Registration Number: 910570350000, TA-175-718-1952-01, GE-175-718-1952-01
2 bedroom
3 bathroom
Sleeps 9
Hawaii> Kapolei
Welcome to Kona Islander Inn, your tranquil retreat in the heart of Kailua-Kona. This inviting studio offers a private lanai with peaceful views of the pool, perfect for relaxing after a day of exploring. Ideally situated within walking distance of downtown Kona, you'll have easy access to oceanfront strolls, vibrant local restaurants, boutique shops, the farmers market, and nearby beaches—all connected by paved resort walkways.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Step into this beautifully updated 11th-floor condo in Hawaii, offering a spacious balcony with views and modern amenities. The condo features a stylish kitchen with granite countertops and stainless steel appliances, including a stove, oven, refrigerator, microwave, coffeemaker, toaster, blender, and tea kettle, plus a complimentary tea starter kit.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Enjoy a relaxing stay in this comfortable 2-bedroom condo located on the second floor with a walk-up access. The unit boasts a fully equipped kitchen including stovetop, oven, microwave, refrigerator, dishwasher, coffeemaker, toaster, cookware, and tableware, ideal for preparing home-cooked meals.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Experience the perfect Hawaiian retreat in this beautifully appointed 2-bedroom, 2-bath ocean view villa nestled within the exclusive Hualalai community. Spanning 1,650 sq ft, this luxurious villa offers breathtaking views of the ocean, lush golf course, and vibrant sunsets.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Aloha and Welcome to Our Villa!
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa
Welcome to Castle Bamboo Waikiki Hotel #1206, a cozy studio featuring rustic island decor with warm earth tones and beautiful hardwood floors. Large sliding glass doors fill the space with natural sunlight and open to a private lanai offering partial ocean views, perfect for relaxing after a day exploring the island. Registration Number: GE-040-188-9792-01, TA-040-188-9792-01, 260230670095
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to your own Deluxe Oceanview Maui Studio condo in West Maui, offering stunning views and a tropical Hawaiian decor. Located just minutes from famous Kaanapali and steps to beautiful beaches, this condo provides the perfect setting for a relaxing getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the perfect Hawaiian escape in this stunning 2-bedroom, 2-bathroom condo at Pacific Shores. Ideally located across from Charley Young Beach, this recently renovated unit offers comfort and convenience for up to 6 guests. Nestled in the heart of South Maui, it provides the ideal blend of relaxation and adventure, with easy access to some of Maui’s best attractions.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Kihei
Welcome to Makena Surf E203, a stunningly remodeled luxury condo nestled within the prestigious Makena Surf Resort on Maui. This beautifully updated unit offers breathtaking ocean vistas, modern interiors, and access to exclusive resort amenities, making it an ideal retreat for your Maui vacation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Makena, Maui
Experience a comfortable and versatile stay in this beautifully appointed 2-bedroom lockout unit at The Westin, offering stunning island views and a prime beachfront location. Spanning approximately 1400 sq ft, the unit combines a large one-bedroom suite with a separate living area, a full-size kitchen, a Jacuzzi bathtub, and a cozy studio with its own amenities.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
REGISTRATION/RESORT/PARKING FEES DUE TO THE RESORT AT THE ARRIVAL.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to Kaanapali Alii 222, a beautifully appointed one-bedroom, two-bath garden view condo offering a perfect Hawaiian getaway. This spacious residence features an enclosed sleeping den, comfortably accommodating up to four guests, making it ideal for families or couples seeking both comfort and convenience.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the beauty of Hawaii from this stunning oceanfront Kona Reef Penthouse. Perched on the top floor, this 1BR/1BA condo offers spectacular views of Kailua Bay and the Big Island coastline. Just steps from Honl�s Beach and a short walk to lively Kona downtown, it provides the perfect blend of relaxation and adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
5-bathroom luxury retreat nestled on the prestigious Kahala Avenue in Honolulu, Hawaii. This expansive estate comfortably hosts up to fourteen guests, blending elegance, comfort, and privacy amidst lush tropical landscaping. The property features a tropical pool and spa paradise, with a cascading waterfall pool and a large heated Jacuzzi spa perfect for relaxation and family gatherings. Registration Number: 1350070050000, TA-153-997-3120-01, GE-153-997-3120-01
6 bedroom
7 bathroom
Sleeps 12
Hawaii> Honolulu
Discover the perfect tropical retreat at Wailea Ekahi Village, situated above the stunning Keawakapu Beach. This spacious 1,505 sq ft second-floor condo features two bedrooms and two bathrooms, offering breathtaking ocean views from your private lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Welcome to Finding Meili! Secure your luxury beachfront escape at KoOlina Beach Nai'a Lagoon, a premier Hawaiian destination. Guests can enjoy exclusive savings of $500 to $1,000 on select 7-night stays when booking through Finding Meili. Inquire about eligibility for shorter or custom stays, as reservations are typically rented in weekly increments (Friday to Friday, Saturday to Saturday, or Sunday to Sunday).Registration Number: TA-199-820-7488-01, 910570130000, GE-199-820-7488-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
Discover the vibrant charm of Waikiki from our beachfront hotel room, just steps from Kuhio Beach and Diamond Head. Wake up to breathtaking ocean views and immerse yourself in Hawaiian adventures. The room offers a cozy 338 sq ft space with a king bed, private lanai, mini-fridge, Keurig coffee maker, and all essentials for a comfortable stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Escape to this charming 2-bedroom, 1-bath condo nestled in Makaha Valley on Oahu's west side. The unit offers a spacious, well-lit living area with an open plan that combines the living, dining, and kitchen spaces, creating a welcoming atmosphere for relaxation and gatherings.Registration Number: 123456789012, TA-060-326-8608-01, GE-060-326-8608-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waianae
Step into the luxurious world of Westin Nanea Ocean Villas, set on 26 acres of lush tropical landscapes infused with Hawaiian design elements. Guests enjoy significant savings of 30-70% compared to direct bookings, with the convenience of resort-managed units and check-in at the front desk using your reservation confirmation.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the comfort of this spacious 1250 sqft 2-bedroom condo with a loft, situated on the third floor with stunning golf course views. The condo features a fully equipped modern kitchen with granite countertops, ceramic cooktop and oven, refrigerator, microwave, dishwasher, coffeemaker, toaster, rice cooker, blender, cookware, and tableware, making it ideal for preparing meals during your stay.
2 bedroom
2 bathroom
Sleeps 7
Hawaii> Waikoloa
Wailea Beach Villas Penthouse 404 is a stunning two-bedroom plus den, two-bathroom ocean view residence offering over 1,978 sq. ft. of elegant indoor-outdoor living. Situated on the fourth floor, this penthouse enjoys sweeping views of the Pacific Ocean, Kaho'olawe and Molokini, creating the perfect backdrop for your Maui vacation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Poliahu is a newly constructed, modern-style bungalow designed to offer a luxurious and refined experience, comparable to a high-end hotel. The interior features a plush king-sized bed, radiant heated floors for comfort, and contemporary lighting that enhances the elegant ambiance.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
5-bath home offers 2,165 sqft of comfort and style. Showcasing local South African art and woodwork, the house provides a unique cultural ambiance. Outside, a spacious thatched hut with a large barbecue area invites gatherings with family and friends, perfect for enjoying the island’s fresh produce.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Discover this beautifully renovated studio on the 13th floor of Marine Surf, offering a sleek modern design and stunning city views with a glimpse of the ocean. Perfectly situated in the heart of Waikiki, this unit provides easy access to the finest attractions, including the world-famous Waikiki Beach, shopping at Kalakaua Avenue, and local dining options. Registration Number: 1260190010113, TA-080-271-1040-01, GE-080-271-1040-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to the Big Island in style at this expansive 5-bedroom luxury estate located in the prestigious Champion Ridge at Mauna Lani community. Newly completed in 2024, this modern home offers unparalleled privacy, a spacious layout, and a host of amenities designed to enhance your vacation experience.
5 bedroom
6 bathroom
Sleeps 12
Hawaii> Kamuela
Welcome to this beautifully remodeled oceanfront condo at Sugar Beach Resort in North Kihei, Maui. This spacious unit offers spectacular views of the sparkling Pacific Ocean, Kahoolawe, Molokini, and Makena from your private lanai. Perfectly situated in a low-traffic area, the property provides direct access to Sugar Beach, renowned for its soft sands, ideal for long walks, swimming, and beach activities like body surfing and snorkeling.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to Paniolo #202 A & B, a picturesque duplex nestled in the heart of Princeville on the stunning island of Kauai. This tropical retreat offers breathtaking views of lush foliage and the majestic Hanalei Mountains from two private patio balconies, perfect for watching spectacular sunsets.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Escape to this enchanting 4-bedroom house in Wailuku, offering a perfect retreat for families or groups. Accommodating up to 12 guests, the home features three luxurious queen beds and four cozy bunk beds, ensuring a comfortable stay. Modern bathrooms equipped with bathtubs and hair dryers—you’ll have all the necessary conveniences.
4 bedroom
6 bathroom
Sleeps 12
Hawaii> wailuku
5-bathroom vacation retreat nestled in the prestigious Kukui'ula community on Kauai's southern coast. This brand-new, beautifully designed home in the Kaulu neighborhood offers a perfect blend of contemporary elegance and classic plantation charm, providing guests with a luxurious and comfortable escape surrounded by lush gardens, tranquil ponds, and distant ocean views.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Koloa
The Aqua Aloha Surf Waikiki offers a charming and affordable base just two blocks from the iconic Waikiki Beach, perfect for couples or solo travelers seeking a vibrant island experience.Registration Number: GE-207-595-5200-01, TA-207-595-5200-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to Manukai Hale by Gather, a stunning 3-bedroom, 3-bathroom home situated on Manukai Street. This luxurious residence offers a spacious, open floorplan ideal for relaxing and entertaining, with contemporary updates and charming Boho decor throughout.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
This exceptional 3-bedroom, 3-bathroom penthouse offers breathtaking 8th-floor ocean views and vistas of the West Maui Mountains. Combining a deluxe two-bedroom oceanview corner suite with a neighboring one-bedroom suite, it provides over 2,000 sq ft of elegant living space.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Lahaina
Embark on an unforgettable Hawaii vacation at Ko Olina Kai 1027A by Gather, a stylish and comfortable two-bedroom, two-bathroom ground-floor condo nestled in the peaceful Ko Olina Kai community within the renowned Ko Olina Resort. This single-level home is perfect for up to four guests, offering a harmonious blend of modern comfort and island charm, ideal for extended stays and memorable vacations.Registration Number: 910560050115, GE-026-366-1056-01, TA-026-366-1056-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Discover the vibrant heart of Waikiki from our welcoming hotel room, situated just steps from the soft sands and azure waters of Waikiki Beach. This 220 sqft unit offers a cozy retreat with a luxurious king bed, city views, and modern comforts such as free Wi-Fi, a mini refrigerator, microwave, and in-room safe, making it ideal for travelers seeking convenience and relaxation. Registration Number: TA-005-625-7024-01, 260160460000, GE-005-625-7024-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This stunning top-floor penthouse in Waikoloa Beach Resort offers breathtaking resort views and peek-a-boo ocean vistas overlooking the Hilton Waikoloa hotel. Nestled amidst lush greenery, koi ponds, and beautifully maintained grounds, the property provides a luxurious and relaxing retreat for families and couples alike.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Beach Resort
Discover this spacious oceanfront 2-bedroom, 2-bath condo offering 1141 sq ft of comfort and style. The open kitchen features modern appliances including a dishwasher, stove, oven, refrigerator, microwave, coffeemaker, toaster, blender, and tea kettle, along with a complimentary tea starter kit.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover the perfect family getaway at Wyndham Waikiki Beach Walk in Honolulu, Hawaii. Located just steps from the iconic Waikiki Beach, this resort offers a harmonious blend of tropical relaxation and vibrant city life. The 2 Bedroom Deluxe Lower Level Suite provides ample space with two bedrooms, two full bathrooms, a fully equipped kitchen, and a spacious living and dining area, making it an ideal home-away-from-home for families. Registration Number: TA-005-837-4656-01, GE-005-837-4656-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
