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 '%Kahana Sunset%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Our condo, now with Air conditioning! Our condo is in north Lahaina, far away from the fire affected areas. We are considered to be located Napili Honokowai. Our unit E-8, is one of 72 units in an oceanfront resort. This unit is a townhouse ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Kahana Sunset is a beachfront resort on the world famous west coast of Maui, 10 minutes south of Kapalua and north of Kaanapali. You will be swimming and snorkeling with turtles and tropical fish off of the white sand beach of protected Keoni ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Kahana Sunset OCEAN VIEW, newly remodeled 2 bedroom, 2 bath split level condo on secluded Keonenui Bay Beach.
Heated Swimming Pool
Kiddie Pool
BBQ Area
Air conditioning
Secluded white sand beach
Turtle Sanctuary
Free parking...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Summary:
Kahana Sunsets is your place for an ocean front resort. This is a ground level condo with ocean views from the patio, bedroom and living room. This condo is fully renovated and all new items with many upgrades. Enjoy the secluded ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Kahana Sunset B4B by Gather, offered by the Stice Team. This beautifully remodeled one-bedroom, one-bathroom condo offers 700 sq. ft. of living space with warm and tasteful Hawaiian decor throughout. Enjoy stunning ocean views, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kahana Sunset is quite special since it has its own spacious white sandy beach in Keone Nui Bay. Sitting on our west facing beach you have picturesque views of Molokai and Lanai islands.
Since this is a small complex, it is ideal for ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Lahaina
Kahana Sunset Resort B4 offers guests the ideal location to enjoy Maui - this air conditioned ocean view 2- bedroom and 2.5 bath town house style (2 levels) 2 - level condominium has wonderful ocean and resort views. With over 950 square feet of...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Lahaina
Treat yourself to this direct oceanfront tropical getaway at Kahana Sunset Resort. When you enter this beautifully remodeled 2 bedroom/2.5 bath condominium, you will be immediately captivated by the breathtaking views of the Pacific ocean. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kahana
Kahana Sunset B9 is an upgraded oceanview air-conditioned two bedroom / two bath condominium with spectacular views of the Pacific from both the living room and master bedroom lanai. Air Conditioning is available and is optional - $25.00 per ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kahana
Kahana Sunset D1D
Great views from this ground level, air conditioned one bedroom / one bath condominium located in the D building of Kahana Sunset's oceanfront resort. Beach, pool and BBQ's are easily accessible out your front door - ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kahana Sunset E1 is an air conditioned two story condominium with 2 bedrooms and 2 baths - direct ocean views. You can't help but enjoy spending time on one of the two lanais enjoying your morning cup of coffee or an evening glass of wine. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahana
Kahana Sunset Resort E5E - Spacious one bedroom corner unit with air conditioning. Located less than 100 yards from the ocean, this is true tropical living where you will enjoy swimming, snorkeling, paddle board and relaxing in the sun on the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Tropical landscaping, sparkling heated swimming pool and delightful surprises, like the sandy beach of Keoni Nu'i Bay, surround you while staying at Kahana Sunset. Unit C8 has been fully renovated and features granite countertops, tile ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahana
Kahana Sunset Resort B10
Enjoy the beautiful expansive views of the Pacific when staying in this spacious two level 2 bedroom Kahana Sunset condominium. Relax on either of your lanais and watch whales play in the warm waters of the Pacific ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kahana
Kahana Sunset Resort is perfectly nestled on the pristine portion of West Maui’s Kahana Coastline known as Keoni Nui Bay. This spacious air conditioned two level 2 bedroom condominium located in the E building provides beautiful expansive ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Kahana Sunset Resort A10 offers guests the ideal location to enjoy Maui - the fully renovated corner unit offers plenty of privacy and extra windows for viewing the beautiful Pacific oean. This beachfront resort is the only resort situated on ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Kahana Sunset Resort is the only beachfront resort situated on the secluded, sandy beach of Keoni Nu'i Bay with magnificent views of Lanai and Molokai. This direct oceanfront 2 bedroom 2.5 bath, is spacious two story condominium.
Spend...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Kahana Sunset Resort offers guests the ideal location to enjoy Maui. This beachfront resort is the only complex situated on the secluded, sandy beach of Keoni Nu'i Bay with magnificent views of the islands of Lanai and Molokai. This two ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Kahana Sunset Resort E10 is a two bedroom / two and 1/2 bath upgraded two story condominium with breathtaking Kahana coastline and ocean views. Located on the white sandy beach of Keoni Nui Bay, the Resort is the perfect place to spend the day...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Located on the Keoni Nu’I Bay, Kahana Sunset Resort offers guests the ideal location to enjoy Maui. This beachfront resort is situated on the sandy beach of Keoni Nu'i Bay. Unit E2E is a spacious condominium with easy access to the beach ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
