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 15650, 50
Stay comfortably in Kihei at this 1-bedroom condo, directly across from the beach. Enjoy a delightful queen bed in the bedroom and a cozy sofa bed in the living room. This amazing property offers Wi-Fi, an indoor washer/ dryer, and AC for your convenience.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Celebrate the 4th of July at the luxurious Westin Nanea Ocean Villas in Maui from June 29 to July 6. This stunning 2-bedroom villa offers a perfect blend of comfort and elegance, ideal for a memorable holiday.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This spacious 5-bedroom, 3-bath duplex in Ewa Beach offers a perfect escape for large families or groups. With two separate units, each featuring its own entrance, kitchen, and living space, guests enjoy privacy and comfort. The property is well-equipped with high-speed WiFi, a smart TV, and air conditioning to keep everyone comfortable. Registration Number: 910390100000, GE-140-425-7792-01, TA-140-425-7792-01
5 bedroom
3 bathroom
Sleeps 15
Hawaii> Ewa Beach
Experience the perfect Maui getaway at Wailea Elua Village 707, a beautifully upgraded second-floor condo offering spectacular ocean views and a tranquil tropical setting. Located on the northern edge of the resort, this spacious 1,528 sq ft residence combines comfort and convenience, making it an ideal retreat for families, couples, or friends seeking a relaxing island escape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Discover the comfort and convenience of this charming 1-bedroom condo in the heart of Waikiki, Honolulu. The space offers a cozy queen bed, modern amenities like air conditioning and WiFi, and access to fantastic on-site facilities including a pool, BBQ area, and laundry rooms. Registration Number: 2760070260227, GE-002-410-0864-01, TA-002-410-0864-02
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience the relaxed island vibe at Waikomo Stream Villas, a lush garden resort nestled on the sunny south side of Kauai. This ground-level, end-unit condo offers a breezy, airy atmosphere, perfect for a tranquil getaway. Located centrally near shops, restaurants, and just a short walk to Poipu Beach and Baby Beach, it provides convenient access to the island’s best spots.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Koloa
Discover this beautifully upgraded 1-bedroom condo nestled in the heart of Waikiki, just a short walk from the famous Waikiki Beach. The unit offers sweeping city views and all the comforts needed for a relaxing stay. Prepare meals in the fully equipped kitchen or unwind in the living area with a flat-screen TV, WiFi, and air conditioning. Registration Number: GE-020-791-5008-01, 260120030267, TA-020-791-5008-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to our inviting Kahana Beach Condo, located just 20 feet from the ocean in a prime Maui setting. This cozy, newly renovated studio offers 750 square feet of comfortable space designed to provide a peaceful and relaxing escape. Ideal for 4-5 guests, the air-conditioned unit ensures you stay cool while enjoying the tropical warmth outside.
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
The Pali Kai Suite is a luxurious tropical retreat nestled within the exclusive Pali Kai Palace estate in Kauai. This charming 2-bedroom suite provides a perfect escape with stunning panoramic views of lush landscapes and the ocean beyond. Meticulously designed interiors feature tasteful furnishings and spa-level appointments, ensuring a comfortable and refined stay for up to four guests.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lihue
Discover the stunning charms of Waikiki from our beachfront hotel rooms, just steps away from Kuhio Beach and Diamond Head. Each modern room offers thoughtful amenities such as a mini-fridge, Keurig coffee maker, and private lanai, providing comfort and convenience for your stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience the perfect Hawaiian escape at Marriott's Waiohai Beach Club in Poipu, Kauai. This beachfront resort offers guests comfortable guest rooms and spacious two-bedroom villas that blend island charm with modern amenities. The guest rooms feature a plush king bed and a sofa bed, providing ample space for relaxation after exploring the island.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Koloa
Experience the ultimate Hawaiian retreat at Hillside Villas 1496-3, a beautifully designed two-story condo nestled in the serene Ko Olina Resort community. This spacious retreat offers a perfect blend of comfort, elegance, and breathtaking ocean views, making it ideal for both short-term escapes and extended stays. Registration Number: GE-117-452-1344-01, TA-117-452-1344-01, 910560130011
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kapolei
Bellani Vacations presents spacious 2-bedroom villas at the luxurious Westin Ka'anapali Ocean Resort Villas. Choose a Friday, Saturday, or Sunday check-in for a 7-night stay. Please contact us to confirm availability, as the Vrbo calendar may not reflect real-time updates.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to Hanalei Bay Resort unit 7305, a charming top-floor studio offering breathtaking views of the ocean, mountains, and Kauai's famous North Shore sunsets. This Hawaiian-style condo features a comfortable King bed and a cozy lanai with seating for two, perfect for relaxing while taking in the spectacular scenery.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Welcome to this beautifully situated 1st floor villa offering panoramic ocean views, including stunning sights of Molokai Island and Makaluapuna Point. The interior features a tile entry, updated open kitchen with granite countertops and stainless steel appliances such as a fridge with ice maker, stove/oven, microwave, and dishwasher.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This exceptional seventh-floor property combines four neighboring residences, offering nearly 3600 sq ft of luxurious living space perfect for groups or large families. The setup includes six spacious bedrooms and bathrooms, four state-of-the-art kitchens, and four expansive lanais that provide breathtaking views of either the Pacific Ocean or the lush West Maui Mountains.
10 bedroom
5 bathroom
Sleeps 20
Hawaii> Lahaina
Welcome to this serene Maui beachfront estate, offering a luxurious retreat with five private full suites and an exotic fruit grove. Nestled behind a private, double-gated entrance and lined with 700 feet of illuminated coconut palms, this property creates a secluded, tropical oasis just minutes from Kihei's amenities.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Make this comfortable and convenient property your next Waikiki adventure! Relax by the pool, enjoy dinner with family at one of three on-site restaurants, or grab a pastry and morning coffee at the coffeehouse. The sun, sand, and surf of Waikiki Beach are just a short walk away! Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 260220400000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Oceanfront Bliss at Valley Isle 1107
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Pohinahina Hale offers a spacious, private retreat just 6 minutes from the heart of Hualalai Resort, accommodating up to 10 guests in style and comfort. This 5,740 sq ft luxury estate features two primary suites, two additional bedrooms in the main home, and a separate ohana (guest house), providing ample space for family and friends.
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Kailua Kona
Welcome to Maui Banyan P409A, a beautifully furnished studio located in the heart of Kihei, Maui. This inviting condo is perfect for up to 4 guests seeking a comfortable, tropical retreat. The unit features two full-size beds with plush linens and a ceiling fan to ensure a restful stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover this beautifully updated loft suite at Luana Kai, offering a modern ocean-inspired vibe just 300 feet from the beach. The two-level unit features approximately 1000 sqft of comfortable living space, perfect for families or groups of up to 6 adults and 2 children.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
A cozy first-floor bedroom and bathroom offer guests privacy and easy access in this well-located Princeville condo. Situated in the heart of Princeville, it’s within walking distance to Anini Beach, known for its calm waters and beautiful scenery.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Welcome to our charming country cottage nestled on the breathtaking Big Island of Hawaii in Kea'au. This idyllic retreat offers a tranquil escape from the hustle and bustle of everyday life, surrounded by lush greenery and stunning garden views, where guests can relax in comfort.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Keaau
Experience the perfect island getaway in this charming Waikiki studio, just a short walk from the beach. The space is thoughtfully furnished with island-inspired decor, making it ideal for couples or small families. The studio features a comfortable queen-sized bed and a folding twin bed, along with modern amenities like a window unit AC system, WiFi, and a 60-inch smart flat-screen television for your entertainment. Registration Number: 1260270200048, GE-183-267-9936-01, TA-183-267-9936-01, STR 144
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to a peaceful, off-grid getaway at the end of a scenic two-mile dirt road. This cozy one-bedroom rental is designed for comfort and simplicity, featuring a screened-in living room and fully equipped screened-in kitchen — perfect for enjoying fresh air and beautiful views without any bugs.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kurtistown
Summary:
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lihue
Experience the perfect blend of convenience and comfort in this modern studio located on the 13th floor of a centrally situated hotel in Honolulu. The unit offers a stunning city view from a private lanai, making it an ideal base for your Hawaiian adventure.Registration Number: GE-036-530-7392-09, 230380020477, TA-036-530-7392-09
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the ultimate Hawaiian escape at Mauna Lani Point E205, a beautifully appointed second-floor condo offering breathtaking ocean vistas. Located within the prestigious Mauna Lani community, this two-bedroom, two-and-a-half-bath residence combines elegance, comfort, and convenience for an unforgettable stay on the Big Island.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
Welcome to Kealoha Tropical Beach Villa by Gather/Elite, your perfect retreat along Oahu's North Shore's 7-mile miracle. This stunning custom teak home is situated on Kamehameha Highway, just across the street from renowned surf spots like Alligator Rock and Leftovers Beach. Registration Number: 610110230002, GE-129-442-8160-01, TA-129-442-8160-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Haleiwa
Discover the perfect beachfront escape in Waikiki with this convenient hotel accommodation. Located just minutes from popular attractions, the excitement of Waikiki is right outside your door, offering an authentic Hawaiian experience. The property features two separate hotel rooms, each approximately 360 square feet, with comfortable double beds, all linens, towels, and bathroom essentials provided. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Kahaki Hale by Gather, a charming hidden gem nestled in the vibrant heart of Kapaa, Kauai. This inviting one-bedroom, one-bathroom condo comfortably accommodates up to four guests, offering a perfect blend of island charm and modern convenience.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Luxury Oceanfront Studio with 1 King, 1 Sofa Bed. Stunning sunset views from your oceanfront Lanai. All amenities. Multiple heated pools with rock waterfalls, soothing whirlpool spas, lighted tennis courts and workout facilities on property. Community Bar-B-Q area.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautiful oceanfront condo at Menehune Shores in Kihei, offering spectacular views of the ocean from most rooms. Wake up to breathtaking ocean vistas in the master bedroom with a comfortable king bed that sleeps up to four guests.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover the vibrant charms of Waikiki from our beachfront hotel room, just steps from Kuhio Beach and Diamond Head. Wake up to stunning ocean vistas and enjoy a blend of relaxation and adventure. The 338 sq ft unit features a comfortable king bed, private lanai, mini-fridge, Keurig coffee maker, and all essentials to make your stay enjoyable. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Welcome to Valley Isle 402, a beautifully appointed 1-bedroom, 1-bathroom condo offering breathtaking oceanfront views from your private lanai. This charming unit comfortably accommodates up to 4 guests, making it perfect for couples or small families seeking a relaxing Maui getaway.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the beauty of Kauai at Hanalei Bay Resort 9204, a spacious three-bedroom condo ideally located on the North Shore. This well-appointed unit offers privacy and comfort with each bedroom featuring a king-sized bed, private entrance, and lanai.
3 bedroom
6 bathroom
Sleeps 6
Hawaii> Princeville
Legally permitted rental Registration Number: 260270310052, TA-051-692-7488-01, GE-051-692-7488-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience a relaxing stay in this beautifully renovated 1-bedroom condo nestled in Honolulu, offering stunning mountain and downtown city views. This spacious 650 sq ft suite comfortably sleeps up to 3 guests with a queen bed and a full-sized sofa bed. Registration Number: TA-119-862-2720-01, GE-119-862-2720-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience the perfect Hawaiian retreat in our beautifully renovated Nihilani condo, offering 1400 sq ft of comfort and style. The ground-floor end unit features an open floor plan with a spacious living room, a large flat-screen TV, and a fully equipped gourmet kitchen with granite countertops, making it ideal for families or couples seeking a luxurious escape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Nestled high in the cliffs of Princeville, this Kauai resort offers a perfect blend of natural beauty and luxurious amenities. Guests can indulge in relaxing treatments at the on-site spa or use the resort as a base for exploring the island's breathtaking landscapes and adventures.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
This beautifully renovated Maui studio offers a perfect retreat for your island vacation. Step onto the lanai for views of the tennis court while enjoying your morning coffee or breakfast. The unit is equipped with air conditioning to keep you comfortable after a day at the beach, ensuring a restful stay.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Discover Coastal Bliss at Pismo BeachNestled on a cliff overlooking the Pacific Ocean, our hotel offers a serene escape with inviting accommodations and delectable coastal cuisine. Guests can enjoy breathtaking views of the ocean, explore world-class hiking trails, play championship golf, and visit nearby wineries for a complete coastal experience.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Immerse yourself in the luxury of Ko Olina Beach Villas B1101 by Gather, an exquisite 11th-floor retreat offering unparalleled oceanfront views. Located within the exclusive Ko Olina community on Oahu's Leeward Coast, this spacious three-bedroom, three-bath villa embodies Hawaiian elegance and tranquility.Registration Number: GE-091-567-8720-01, 910570090249, TA-038-981-7856-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
You’re so close to making the Hawaiian vacation of your dreams a reality, and at Kolu Honu, all the enchantment of the islands comes to life, beckoning you to a truly magical experience! Located in the heart of downtown Kailua-Kona along the renowned Ali’i Drive, this home features a prime location just steps away from the azure waters of the ocean stretching out as far as the eye can see.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Consider this condo your home away from home during your visit to Kailua-Kona. This inviting space offers breathtaking ocean views and a peaceful atmosphere, ideal for unwinding after a day of adventure. The living area features a comfortable queen bed, a cozy love seat, and a large flat-screen Smart TV, providing a perfect spot to relax in cool comfort.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Escape to this luxurious Westin Ka'anapali Oceanview studio and immerse yourself in the beauty of Maui. The spacious unit offers breathtaking ocean views and access to top resort amenities such as a relaxing spa, vibrant snorkeling and surfing spots, and expansive beaches perfect for lounging.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your perfect Kona getaway! Nestled on the vibrant Alii Drive in the heart of Big Island, this inviting 2-bedroom, 1-bathroom condo offers a peaceful retreat just steps from stunning beaches and exciting local attractions.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience a tranquil escape in this charming beachfront condo, ideally located just steps from the stunning turquoise beach. Designed for couples, it offers a comfortable bedroom with a plush king bed and a modern bathroom featuring a refreshing shower and hair dryer.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Welcome to Royal Kahana 210, a beautiful 1400 sqft oceanfront condo offering breathtaking views of Molokai and Lanai from its expansive wraparound lanai. 5-bath unit comfortably hosts up to 6 guests in a quiet, second-floor location, ensuring a peaceful stay.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Napili-Honokowai
