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 (city = 'lahaina' OR postalCode = 'lahaina' OR region like '%|lahaina|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Kahana Sunset Resort offers guests the ideal location to enjoy Maui. This beachfront resort is the only resort situated on the secluded, sandy beach of Keoni Nu'i Bay with pristine views of the beautiful blue Pacific. This direct oceanfront ...
2 bedroom
3 bathroom
Sleeps 6
HI> Lahaina
Kahana Sunset Resort offers guests the ideal location to enjoy Maui. This beachfront resort is the only resort situated on the secluded, sandy beach of Keoni Nu'i Bay with magnificent views of Lanai and Molokai. This direct oceanfront two ...
2 bedroom
2 bathroom
Sleeps 6
HI> Lahaina
The LoveRentals portfolio is comprised of curated, professionally managed vacation rental homes around the world. We only select professional property managers that meet our high standards. Book with peace of mind knowing that you will have the ...
2 bedroom
2 bathroom
Sleeps 6
HI> Lahaina
Okika (Orchid) Hale - Luxurious Villa - just steps to beach! | Aloha and welcome to the Okika (Orchid) Hale an elegant tri-level three bedroom vacation home located just steps away from two of Maui's most beautiful beaches and the world famous ...
3 bedroom
3 bathroom
Sleeps 7
HI> Lahaina
An island paradise will be yours when you stay at this exquisite 1-bedroom, 2-bathroom vacation rental villa in Kapalua, Maui. Suitable for 2 adults, this villa offers access to the community pool and boasts both ocean and golf course views from...
1 bedroom
2 bathroom
Sleeps 2
HI> Lahaina
Beautiful views of the world famous Kaanapali Beach and Black Rock from this 1 bedroom, 2 bath condominium. Sleeps 4. Seasonal watching of humpback whales, spectacular sunsets or enjoy your morning coffee or breakfast on your large lanai while ...
1 bedroom
2 bathroom
Sleeps 2
HI> Lahaina
Enjoy snorkeling with the sea turtles, humpback whale watching in the winter or gazing at rainbows, this property is perfect for it all! The Kahana Sunset sits on Keonenui Bay and feels as though it has its own private beach. Conveniently ...
2 bedroom
3 bathroom
Sleeps 6
HI> Lahaina
Ka'anapali Beach Club by Diamond Resorts 1 Bedroom Unit Sleeps 4
**PLEASE READ ENTIRE DESCRIPTION BEFORE REQUESTING RESERVATION**
Book our Amazing Timeshare & let The Timeshare Brokers, LLC make the booking process easy!
Key Points:
...
1 bedroom
1 bathroom
Sleeps 4
HI> Lahaina
Kahana Manor 509 - New Remodel! All the comforts of home await you at Kahana Manor 509 - this spacious one bedroom /one bath is conveniently located across the street from the ocean, walking distance to beach, dining and shopping. Ideal ...
1 bedroom
1 bathroom
Sleeps 4
HI> Lahaina
Westin Ka'anapali Ocean Resort Villas - Oceanview 2 Bedroom Unit Sleeps 8
**PLEASE READ ENTIRE DESCRIPTION BEFORE REQUESTING RESERVATION**
Book our Amazing Condo & let The Timeshare Brokers, LLC make the booking process easy!
Key Point
s:
...
2 bedroom
2 bathroom
Sleeps 8
HI> Lahaina
The Ultimate Maui Vacation Rental. Spectacular Ocean / Beach Front Maui Penthouse the Hawaiian is an Ocean / Beach Front Maui Penthouse with 2 Bedrooms & 2 Bathrooms. Look no further The Hawaiian Penthouse has views you will never want to leave....
2 bedroom
2 bathroom
Sleeps 6
HI> Lahaina
Once you have arrived to your retreat for the next several days you will walk through the front door and immediately hear the calming and gentle sound of the Pacific Ocean. Your view is of the many sailboats, and the silhouette of the island of ...
6 bedroom
6 bathroom
Sleeps 15
HI> Lahaina
Steps from beach, on site pools,restaurant and bars. Beach boardwalk access to all of strip including 5 star shopping and eating at Whaler's Village. World Class snorkelling 5 minutes away on foot at Black Rock.
Unit is a 2 bedroom Lock off ...
2 bedroom
2 bathroom
Sleeps 8
HI> Lahaina
Marriott Maui Ocean Club OCEANVIEW 1 Bedroom 2 Bathroom Unit Sleeps 4
**PLEASE READ ENTIRE DESCRIPTION BEFORE REQUESTING RESERVATION**
Book our Amazing Condo & let The Timeshare Brokers, LLC make the booking process easy!
Key Points:
...
1 bedroom
2 bathroom
Sleeps 4
HI> Lahaina
Welcome to the Napili Point! This oceanfront 1 bedroom directly on the stunning Honokeana bay in Napili is a relaxed, contemporary space designed to create special memories in a stylish, comfortable environment. Freshly remodeled, this air ...
1 bedroom
1 bathroom
Sleeps 4
HI> Lahaina