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,
(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 '%kbr%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Paradise awaits Kihei Beach Resort 408
Look no further, beautiful custom upgrades throughout, oceanfront condo directly on Sugar Beach, the longest stretch of sand Maui has to offer and just steps from your door. Start your morning off ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Hotel Room at the Kauai Beach Resort located in Full Service Oceanfront Resort just 5 minutes from the Airport. This is a third floor unit with a sliding door to a lanai overlooking the Luxury Pool Area and direct access to the ocean. The Pool ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
Enjoy a delightful beach vacation at Kauai Beach Resort and Spa, a full-service oceanfront resort situated on 25 lush acres along Kauai’s longest strolling beach. This is a privately owned, immaculate ground floor unit steps from the ocean and...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Entering this totally remodeled 1 bedroom/1 bath beachfront condo you are immediately confronted with stunning, unobstructed ocean views. The upgraded kitchen and bathroom are beautiful with marble tiling, granite counters, and lovely cabinets....
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Now with new central A/C system! Treat yourself and stay at this true Maui home away from home. Kamaole Beach Royale 611 is a special 2 Bed/2 Bath Ocean view vacation home with contemporary upgrades and a commanding western view across the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to your home away from home at Kamaole Beach Royale 506 on Maui. This one-bedroom, one-bathroom condo is the perfect retreat for anyone looking for a relaxing island getaway. Our condo has stunning ocean and beach views that can be taken...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Kihei Beach #204 is a one-bedroom/two-bath condo located on the 2nd floor, offering a beachfront experience on the stunning Sugar Beach in North Kihei, Maui. The condo features beautiful tile flooring throughout, an impressive granite & ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Beachfront condo #401 is a 2 bed/2 bath corner unit on the 4th floor of the 1st building, at the north edge of the property right in front of the pool. Extra windows provide even more expansive ocean views and also let in lots of light. Pick up ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Tiled throughout, the interior of this upgraded 2 bedroom/2 bath unit is clean and bright with a view that stuns you every time you walk through that front door! The beautiful furnishings and gorgeous granite & stainless-steel kitchen add to the...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This beachfront one-bedroom / one-bath condo in North Kihei, Maui offers a serene and beautiful retreat on Sugar Beach. The unit features quality finishes such as granite countertops, tile flooring, and lovely furniture. Central A/C, a spacious ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Aloha and welcome to our very own slice of paradise located at the incredible beachfront Outrigger Kauai Beach Resort & Spa (“OKBR”). A 4-Star luxury resort and 25-acre masterpiece nestled along a pristine, private 3 mile stretch of crystal ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Experience the ultimate beachfront getaway at Kihei Beach #202, a beautifully upgraded 2-bedroom unit located on the 2nd floor. With central A/C, stunning ocean views, and a newly renovated kitchen, this condo offers a serene and luxurious ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This spacious beachfront condo is a 2 bedroom/2 bath corner unit on the 2nd floor of the 10th (southside) building. The additional large window on the south side provides extra light and views of tropical greenery. Ocean views from the living ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Hotel Room at the Kauai Beach Resort located in Full Service Oceanfront Resort just 5 minutes from the Airport. This is a second floor unit with a sliding door to a lanai overlooking the Luxury Pool Area and direct access to the ocean. The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Kamaole Beach Royale #307 has one of the best locations on Maui, close to so many shops and eateries and across from popular beaches and parks. This Kihei vacation condo offers spectacular ocean and sunset views from the large West facing ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
This updated beachfront condo is a 1bed/2 bath unit perched upon the shores of white sanded Sugar Beach in North Kihei. Views from the condo & its lanai are unobstructed: wintering whales play in the bay, paddlers paddle, neighbor islands ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
