select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `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, rewritten_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 and round(price)>0 and bedrooms>=0 AND (`headline` like '%2 bedroom%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Travelers looking for the FINEST and most LUXURIOUS accommodations, look no further... This top floor, 2 bedroom and 2 bath condo is nestled on the Ocean Bluff with both Tropical Garden and Ocean Views. This Condo is a SECRET GEM offering High
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Halii kai is an Ocean front Resort in the beautiful Waikoloa Beach Resort. What sets 16B apart from the rest? Location, amenities and service. I live on Island and can assist with any issues or questions. Halii Kai Unit 16B is right
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa
vrKCV - Country Club Villas 315, Professionally Managed by Kona Coast Vacations
*CONSTRUCTION NOTICE - Complex began stair railing steel painting project for the month of August, 2024. This consists of sanding away old paint, which can be
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Our luxury 1400+ sq ft condo (F102 at the Mauna Lani Terrace) is walking distance from the world class (Auberge) Mauna Lani Resort, with nearby restaurants, Stores tennis, and golf facilities. It’s a short walk either to Mauna Lani Hotel or
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela,
Stylish island décor creates a casual vibe in this 1,082sf suite with lanai flowing onto the lawn. Homey ease plays out with a modern kitchen opening to the dining/living area, washer/dryer, flat screens, WiFi, double-vanity master bath,
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> WAIKOLOA
Book with confidence, this nicely updated 2 bedroom, 2 bathroom (1086 sq. ft) single level condo. This condo is managed by PMI Maui, a local Real Estate Brokerage that specializes in vacation rentals. We are located within a mile of the resort
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Surround yourself in luxury while enjoying privacy and seclusion all while being close to world class dining and shopping. Enjoy unparalleled views and seamless indoor/outdoor living with a top notch beach steps away and some of the best
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to Paradise Found, a luxurious oceanfront escape at Hale Awapuhi along Kauai's breathtaking Coconut Coast. Located in an exclusive, gated community with only nine private units, this stunning two-bedroom, two-bath condo offers sweeping
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
TMK: 4-2-8-018-005-0001-000
This custom designed Hawaiian plantation style cottage is located just off the Brennecke's beach side of Poipu Beach Park, 100 steps to the beach!
This spacious house was designed and built in 2013 to
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Penthouse Luxury Oceanfront, Third Floor, 2 Bedroom | 2 Bathroom with Central Air
Fall asleep to the sounds of enchanting surf and awake to tropical sunrises in this spectacular and fully remodeled 2 bedroom condo overlooking Wailua Bay
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Welcome to our 2 bedroom Waikiki Shore Condo. This exclusive Waikiki Shore Condominium is located directly on Waikiki Beach, and is the only privately owned condominium building on Waikiki Beach, and sharing this world famous beach frontage
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Hana's Heaven - a charming 2 bedroom cottage complete with full kitchen, living room and 3 lanais. Wake every day to the sunrise and ocean expanse. Off the beaten track, this home away from home is quiet and serene, the perfect place to relax
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Hana
Aloha! We would like to welcome you to our little piece of paradise on the Big Island of Hawaii on the South Kohala/Kona Coast. A 20 minute drive north from the Kona airport, the South Kohala area is upscale and close to some of the islands
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa
Westin Ka'anapali Ocean Resort Villas. 2 bedroom 2 bath lock-off villa. I bedroom I bath with full kitchen, full bedroom, living room and dinning room on one side of lock-off and a studio with galley kitchen kitchen on the other side. Both have
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Newly renovated Fully equipped Kitchen – new refrigerator, range, dishwasher and microwave. Many amenities.
Hawaiian style outdoor living and dining room in 700 sq. ft. Lanai with 4 ceiling fans to assist the ocean breezes.
• 2
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
We are literally oceanfront!
E Komo Mai (Welcome) to Paradise!
30 Day minimum stay required by Oahu Ordinance
Our homes are fully furnished with everything you need for an ideal Hawaiian vacation!
Makapu'u Hale is 2
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waimanalo
Sun, fun and the Hawaiian Island lifestyle is waiting for you! Our townhouse is located in the beautiful beach front property of Ekahi Village. Explore the winding pathways through Ekahi's lush gardens spanning over 34 acres. Take a stroll
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kihei
Kings’ Land, the newest Hilton Grand Vacations Club time share resort on the famed Kohala Coast, offers expansive fairway views and stunning Waikoloa panoramas. Carved out of an ancient lava field, this 112-acre oasis occupies a superb
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Beautiful two bedroom beachfront home across from famous Brenneckes Beach! Known for excellent boogie boarding, body surfing, surfing, and swimming! Just a short walk from your door and be on the sand! Breathtaking views from the hot tub on the
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Evan Price Binnacle Realty, Inc.
We will be happy to respond to your needs as concerns dates and other resort options via return e-mail. Please use the "Send email" for any questions that you may have. This is a
- 3 bedroom
- 2 bathroom
- Sleeps 9
Hawaii> Lahaina
FABULOUS LOCATION - Just 50 steps to the pool, the hot tub and the BBQ’s and a few more steps to the ocean-front lounge chairs. Grab a cool drink and head out to watch the sun set between Molokai and Lanai. Bring your binoculars for excellent
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
ALOHA and welcome to our home at Luana Kai. Our unit is set apart from any other by the love we've put into it and the care we've taken to maintain it.
Our deluxe condo has tile floors throughout and stainless steel appliances for
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Reservations are available in weekly (7 night) increments with arrivals and departures on Fridays, Saturdays, and Sundays only. The Westin Ka'anapali Ocean Resort Villas is a gorgeous beachfront resort with luxury villas. Enjoy this villa and
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Mahana 919 is a 2 bedroom, 2 bath spacious corner unit has one of the most outstanding views at the Mahana and on the island - It is also one of the nicest places to stay!! This condo is on the south west corner of the building which means you
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
May we welcome you to Alihi Lani at Poipu. Our beautiful air conditioned condo located in the heart of Kauai's sunny south shore! Alihi Lani. or 'Beautiful Horizon Views', aptly describes the magnificent ocean panorama of Hawaiian sunsets,
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Poipu
Aloha, we would like to welcome you to a beautiful ocean front condo designed by Peter Rusnak. Hawaiian decor with island style furnishings throughout the unit. You will experience Hawaii while relaxing in a luxury 2 bedroom condo.
H209
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
*note: if you are booking a short stay for 7 or less days, please inquire first before doing a reservation request, unless your arrival date is less than 30 days away, in which case you're welcome to proceed with the reservation
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Open the door to our beautiful second home in Kauai and know you are in paradise. There is nothing between you and the vast blue ocean, just 35 feet away, which is framed by a wall to wall expanse of floor to ceiling windows. The soothing sounds
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
***WOW - DIRECT OCEANFRONT - AC***
SANDS OF KAHANA
The Aloha Spirit invites you to come and experience firsthand the many activities and beautiful sunsets that await you at the Sands of Kahana Resort. Whether you're looking for a relaxing
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
A touch of Old Hawaii...this charming, comfortable, up-to-date, 2 bedroom corner condo is located on the ground floor of the two story 12 unit Pikake Condominiums in Honokowai. No long treks from the parking lot... our parking is close to our
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Coming to Ko Olina for a full month? Come stay in a great condo with a prime location at Ko Olina Kai gated community.
This is a wonderful 1,144 sq. ft. two bedroom ground floor end-unit condo, within a 8 to 10 minute walk to the beach
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Ko Olina
Aloha and welcome to our home at The Palms at Wailea. The ocean view will take your breath away the minute you step inside. But it's the condo renovation and our commitment to our guests that will keep you coming back.
Our remodeled
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Book with confidence, this beautiful over sized, 2 bedroom, 2 bathroom condo that has stunning ocean views. This condo is managed by PMI Maui, a local Real Estate Brokerage that specializes in vacation rentals. We are located within a mile of
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Enjoy one week of elegant luxury in my two bedroom unit right across from Kahana Beach. Check-in days are Wednesdays, Fridays and Saturdays. Amenities include pool, tennis courts, and air conditioning. Test the waters of nearby beaches famed for
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Whether you are coming to Waikiki on business or just to escape the stress of business with a vacation, you will find this 2 bedroom condo in a very desirable building your perfect tropical oasis. Designer -inspired décor echoes the peaceful
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Honolulu
Aloha and welcome to my Hawai’i Island cottage!
I am delighted to share my 1 acre of rainforest and hope you will choose to stay at Hawaiian Paradise Cottage.
Enter the property through a private electric gate and down a tree-lined
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Keaau
Situated on 18 acres of perfectly manicured tropical foliage, bordering the award winning Wailea Blue Golf Course, Ekolu Village is an exclusive and desirable complex that is exceptionally quiet and free of any traffic noise. This property is
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailea
This year make it a very MAUI CHRISTMAS!!
For years MAUI HAS BEEN VOTED #1 ISLAND IN THE WORLD. Come see for yourself...
All of the amenities of a resort, with the comforts of home! The Westin Ka'anapali Ocean Resort Villas is all
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
The best views on the Island! Your own private lanai that looks onto the beautiful Bali Hai peninsula and Hanalei bay. Fully refurbished and equipped 2 bedroom condo, each room has it's own bathroom, plus large living, dining, kitchen area.
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Princeville
Welcome to the beautiful, family friendly, Westin Ka’anapali Ocean Resort Villas, North. We have an OCEAN FRONT 2 bedroom lock off available in 2025. Best availability occurs when inquiry is at least 8 months in advance as the resort and type
- 1 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
2 bedroom 2 bath ocean front condo in Kihei.
Aloha!
Our condo is in Kauhale Makai - or Village by the Sea which is a great resort complex on a large grassy ocean front property. There is a saltwater pool and a hot tub. It is a 2
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Located on Maui's famed West side you will be 70 yards away from a Beach Park with playground equipment, swings, benches & tables, and a protected beach - great for young children and for watching the whales and sunsets, plus the Honokowai
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
The Kauai Beach Club resort features an array of amazing on-site amenities such as a 26,000 square-foot pool rimmed with colonnades and waterfalls, a children's pool, spa, fitness center, and two 18-hole championship golf courses designed by
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lihue
Magnificent large 7th floor end unit condo, considered the corner suite, is fully equipped with new furnishings, including barn doors, new ceilings, new bathrooms, new kitchen, new flooring and ultra modern features throughout. This condo has
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
The rental is the entire downstairs with it's own private entrance and large patio with BBQ. The downstairs has two separate bedrooms with a large family room in the middle. The kitchen has a refrigerator, sink, microwave, two burner hotplate,
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Holualoa
Welcome to Papaya 2 at Tiki Moon Villas, offering an idyllic escape just across from the Polynesian Cultural Center. Ideal for families or intimate gatherings of friends, this two-bedroom vacation rental on the North Shore invites you to
- 3 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Laie
RELAXING GARDEN RESORT:
Kihei Resort offers a wonderful zen like experience. As you walk through the grounds of Kihei Resort, it is difficult not to enjoy the serenity of the beautifully landscaped grounds, smell the plumerias and hibiscus,
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Please choose a Friday through Sunday check-in and enjoy a seven-night stay.
Thank you for choosing Dash Realty Group of Windermere, Florida. We are a registered Fla. Seller of Travel Ref. No. ST42435.
Our rates reflect
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Koloa
The much coveted ground floor, one level, 2 bedroom - 2 bathroom floor plan at Papakea. No steps! Also, brand new ductless air conditioning system in both bedrooms and living room / kitchen area. An amenity not all of the condos have! Enjoy
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
The beautiful home is located on the east side of Oahu in Hawaii Kai. Located near beaches such as the famous Hanauma Bay, restaurants, shopping centers,hiking trails, scenic lookouts and water sports. The home offers 2 bedrooms (2 queen beds),
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Honolulu
