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 '%Oceanfront%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Dream your most desired vacation. Then come to magical Maui, one of the world’s most elite vacation destinations. How better to experience our island than by renting from an owner who has combined her own personal getaway with the conveniences...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
Gorgeous, completely upgraded oceanfront/oceanview 2 bedroom, 2 bath. This condo is exquisite and has been meticulously renovated for the discriminating traveler. Huge, unobstructed ocean and island views from the living room, lanai and master ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
SPECTACULAR VIEWS await you at Kahoa House for your Hawaiian holiday. Early-birds and mainlanders adjusting to the time difference can enjoy a cup of Ka’u coffee in the comfortable living room, and watch the sun rise out of the Pacific ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hilo
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Enjoy breathtaking sunset views every night from this beautifully updated condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom.
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Our comfortable, fourth-floor, south shore condo is right ON the ocean so you will SEE --and HEAR-- the sound of the surf as soon as you open your front door. The condo, with stylish, contemporary furniture, boasts a comfortable king-size bed. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> poipu
In Hawaii's lush tropics, discover your dream home at this stunning 2-bedroom retreat. Nestled just an 11-minute walk from the picturesque Maalaea Harbor, you'll be perfectly positioned to explore the stunning West Maui Mountains and the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Simply put, this property sits on one of the most gorgeous ocean-front locations in East Hawaii. Unlike other ocean-front homes, you will not need to walk to the end of a yard to appreciate the Pacific waves. Here, you will feel as if you are on...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Hilo
Maui is outdoor living at its finest! Enjoy having breakfast lunch and dinner on your lanai staring at the ocean! Wake each morning to birds singing, swaying palm trees and the teal blue waters of the ocean.. there is no escaping the absolute ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Hale Aloha… On The Sand TVNC-4185
Anaik Martin, is the property manager, lives nearby and is available 24 hours a day, 7 days a week, (808) 652-3410. Professionally cleaned and disinfected by a licensed ...
- 6 bedroom
- 7 bathroom
- Sleeps 12
Hawaii> Anahola
Was recently completely remodeled.
Our oceanfront condo is 23ft. from the ocean and is located on the 2nd floor and offers spectacular sunsets.
Watch the surfers and the dolphins from your lanai.
A Short walk to town and over 20 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Aloha! E Komo Mai (Welcome) to our beautiful luxury Beach Villa in Ko Olina on the island of Oahu and conveniently located just 25 minutes from Honolulu! We would love to have you come stay with us!!
Our fully equipped luxury 15th-floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
DISCOUNT: June 23 for 5 nights at $545 per night due to less than week availability.
Please be sure to read the reviews. Prior guests truly tell the story.
This beautiful multi-million dollar property is directly oceanfront with ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
#240 is one of the top 1 bedroom, end unit, rental condominiums at Sugar Beach Resort, which is one of the top resort designations on Maui. Consider staying with us for the following reasons:
1) Oceanfront property - Spectacular views ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Discover the stunning shoreline of Princeville, home to your two-bedroom condo. Situated just a 14-minute drive from the stunning Hanalei Bay and a 28-minute drive from the majestic Na Pali Coast State Park, this property offers a prime location...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
****Please note this property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the rental is actually used or occupied. Please inquire regarding availability for stays...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haleiwa
Enjoy stunning ocean views from all the bedrooms and living area of the condo. Greet every morning on your bed enjoying the views and sounds from the ocean!
*** OCEANFRONT overlooking greenbelt, quiet and private, no noise from pool or ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
This nicely remodeled 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
*Available for booking! Thankfully, our condo is not located in the wildfire affected area*
Enjoy panoramic views in this renovated oceanfront corner unit condo located on Kahana Beach at Valley Isle Resort complex! This unique corner ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Enjoy Kauai's simple pleasures and grand landscapes right from home! From the moment you arrive, you immediately sense the Aloha spirit and the other-worldliness of Kauai's North Shore. Hanalei Bay Villas' surroundings are stunning and beyond ...
- 3 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Princeville
See the whales from our top-floor unit! They have arrived and will be there through early Spring!
Experience the ultimate beachfront getaway in Maui, perfect for small families and couples. Whether celebrating a romantic honeymoon or an ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> kaanapali
Kona Bay Estates is a gated and patrolled oceanfront community. The Sunset Beach House offers 2700 square feet of luxurious space. It is just 500 meters from the sea. Opening walls of glass offer indoor-outdoor living with spectacular views of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Located on a picturesque lava bluff, our top floor condo offers privacy, cooling trade winds and panoramic ocean vistas which a camera can't begin to capture.
Crashing waves, migrating whales and sunrise/sunset views are what you'll see as...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
This unit is remodeled and ready for you to call home for your stay! Walk down the stairs and onto the longest walking beach in Maui! Heading South on the beach, you are a 5 minute walk to delicious Sugar Beach Bakery and Ululani Shave Ice. Head...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Stylish Oceanfront 2nd Floor 2 Bedroom | 2 Bathroom
Lanikai is centrally located on the Island yet 'tucked away'. This elegant second floor unit has sweeping Ocean views. This luxurious condo comes with a fully-equipped kitchen area ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Words cannot describe the incredible view from this penthouse condo. It's mesmerizing!! You'll hear the waves crash below you along the coastline and marvel at the Pacific Ocean stretching out as far as you can see. Turtles, spinner dolphins, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> koloa
Enjoy the perpetually calm span of crystalline ocean and the gentle trade winds as you relax on the oceanfront lanai or sun deck of this beautiful home tucked onto Oahu's south shore. Launch a kayak or stand up paddle board from the private ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Honolulu
Welcome to Pacific Sunset Villa, home of expansive ocean views and ultimate luxury. This award winning home with 4 bedroom with ensuite bathrooms and 3 half bathrooms sits on an oceanfront setting and offers easy access to all the amenities at ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Waikoloa
This luxury condo is as close to the beautiful blue waters of the Pacific Ocean as you can get at Kanaloa at Kona. Unit 1804 is a top-floor (second floor) oceanfront condo with over 1700 square feet of living space with all the amenities of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Experience a relaxing Hawaiian vacation in one of Maui's most serene oceanfront settings. Despite the devastating fire in Lahaina, Puamana remains a haven of tranquility and beauty, where the spirit of "Old Hawaii" lives on. Unit 9-1 is located ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
This oceanfront, single-level townhome is located just 25 feet from the water, offering a front-row seat to stunning sunsets and surfers from the comfort of your lanai. While the Lahaina fire has impacted some parts of the community, this ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
3 Bedroom Oceanfront Luxury at The Hyatt Residence Club – Ka'anapali's Newest, Most Exclusive Resort.
Perched upon Ka'anapali, one of the world's most beautiful beaches is the Hyatt Residence Club Ka'anapali. Within the lush borders of ...
- 4 bedroom
- 3 bathroom
- Sleeps 11
Hawaii> Lahaina
Kealia Landing sits on a bluff overlooking wide, sandy Kealia Beach with panoramic, unforgettable views of Kauai's east coast extending ten miles to the Hoary Head Mountain range. From this wonderful cul-de-sac in an historic mill community, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kealia
Our last winter opening until May! Just 409/nt for this gorgeous OCEANFRONT condo!
Mar 18-24
Are you craving PANORAMIC views from your condo & private balcony? Condo B9 boasts unobstructed 180° up-close-&-personal views of white sand...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Luxury Oceanfront Studio Villa at The Westin Ka'anapali Ocean Resort Villas North.
This oceanfront luxury studio villa features outstanding oceanfront views of the Pacific Ocean and sleeps up to 4 with one king sized bed and one queen ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Freshly Renovated 1 Bedroom Oceanfront Suite at Marriott's Maui Ocean Club.
When you can’t afford a mediocre Maui vacation!
Family time is limited, make the most of your vacation time with an out of this world Ocean front ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Hawaii STVR Registration Number: STVR-19-355146 ************* Aloha and welcome to our luxury, oceanfront, island home!! This is not your “ordinary vacation rental”, but rather a completely renovated, highly upgraded home, in an ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
*Inquiry Only*
Indulge yourself in the ultimate island style luxury at the Big Island of Hawaii’s most exclusive retreat, Kauhale Pilialoha. Experience breathtaking sunsets, enjoy the natural beauty of Hawaii from the ultimate poolside ...
- 10 bedroom
- 10 bathroom
- Sleeps 30
Hawaii> Kamuela
2 Bedroom Oceanfront Luxury Villa at The Westin Ka'anapali Ocean Resort North!
This two bedroom, two bathroom luxury villa features over 1200 square foot of interior space and incredible ocean front views of the tranquil blue Pacific ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
H203 is a newly renovated, colorfully decorated 2-bedroom, 2.5-bathroom oceanfront condo with spectacular views. Located in the highly desired H-building, guests experience beautiful unobstructed ocean views. Enjoy peaceful afternoons on the ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
Welcome to Kihei Beach, a charming six-story beachfront property nestled in North Kihei equipped with an elevator. Enjoy breathtaking views of Kaho'olawe, Molokini, and the sparkling Pacific Ocean in a relaxed, friendly setting. Right outside ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
J202 is an amazingly well-located 2-bedroom, 2.5 bathroom condo only steps away from the ocean. With a great location on the property, families will enjoy being close to the Mauna Lani's sandy beaches, as well as a private pool/BBQ area. The ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Marriott's Maui Ocean Club Two Bedroom Oceanfront Corner "Ohana" Villa - New Napili or Lahaina Towers!
With its premier location, luxurious accommodations, and sprawling tropical grounds, Marriott Maui Ocean Club is a perennial Maui ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
C302 is a top floor oceanfront condo that is currently available at a reduced rate! The 2 bedroom, 2-bath top-floor villa features vaulted ceilings, gorgeous ocean views and a large covered lanai for sunset dinners. The living room features new ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Beautifully upgraded oceanfront condo at the Mauna Lani Resort, only steps away from the beach! B301 is a newly updated 2 bedroom / 2.5 bath condo located on the top floor of the B building - offering gorgeous ocean and sunset views. This ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Beautiful oceanfront condo only steps away from the beach! Superbly located at the exclusive Mauna Lani Terrace, this newly decorated 2 Bedroom / 2.5 Bath upgraded condo has a large outdoor lanai that is perfect for sunset dinners, or relaxing ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program!
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Come to the charming town of Hanalei and experience the Jones House, a spacious oceanfront home on spectacular Hanalei Bay. Steps away from the beach with breathtaking views of Hanalei Bay, the Jones House is sure to delight. Swim, surf, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hanalei
Oceanfront! Live the Dream! Ultra Luxury "Beachfront" Ground Floor Unit A104 at Waipouli Beach Resort & Spa offering stunning full oceanfront views from every room, with watercolor sunrises, whale watching and romantic dinners on the lanai with...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
