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,
(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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`address` like '%4471 Lower Honoapiilani Rd%' ) order by rating desc LIMIT 0, 15
The hidden gem of Maui: enjoy the best views, central location and a quiet, peaceful and relaxing experience at a price you can afford. This unit...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
It doesn't get much closer to the ocean than this! With new finishes, new bedroom furniture, "Westin Heavenly" mattress and box spring, 70" ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
**CONSTRUCTION DEALS FROM JUNE THROUGH OCTOBER 2019**
Seawall repair construction is estimated to begin June 2019 and end approximately ...
-
1
bedroom
-
1
bathroom
-
Sleeps 2
HI>
Lahaina
Kahana Reef 107 offers fantastic views of the neighboring islands of Lanai and Molokai, beautiful sunsets, and sea life such as Hawaiian Green ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
This private, top floor suite offers sweeping, direct oceanfront views towards the islands of Lanai and Molokai. Free long distance phone to US ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
***********************************************
NOTE: Sea wall repairs will begin in June, and continue through September. The work is on the ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
*******Note: Starting approximately June 1, 2019 thru September 30, 2019 there will be construction work done on our seawall, so there may be ...
-
1
bedroom
-
1
bathroom
-
Sleeps 3
HI>
Lahaina
This condo has been attractively remodeled with great finishes and appointed with Hawaiian furnishings. Maui Beachfront Rentals has taken great ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
Construction Special 25% off June 1 to September 30!
Exceptional Maui location right on the ocean and directly across from Molokai. Watch sea ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
Construction Special 25% off June 1 to September 30! Construction is not occurring in front of our condo building but is something that may ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
Located just 50 yards from the ocean this beautiful one bedroom/one bath condo has all the amenities of home. From the Kahana Reef, enjoy the ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
This private, top floor, corner suite offers sweeping, direct oceanfront views towards the islands of Lanai and Molokai. Tastefully renovated 1 ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
This cozy little Condo is perfectly situated just 30 feet from the edge of the ocean with panoramic views of both Lanai and Molokai! You can ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina Maui
Spectacular ocean views from your private balcony. View beautiful sunsets nightly and during the winter watch whales play. In this 1 bedroom, 1...
-
2
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Lahaina
