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, rewritten_description,
(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
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 ocean views and sunsets.
Watch the surfers and the dolphins from your lanai.
A Short Walk to town
- 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
DISCOUNTS:
May 8-15 7 nights at $545 per night due to cancellation.
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
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to your tropical paradise vacation! Immerse yourself in the breathtaking beauty of Kauai’s North Shore with spectacular oceanfront views and stunning sunsets from our elegantly designed, ultra-luxurious
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
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
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
. . . . . . . . . . . . . . . . . . WELCOME TO SUNSET CENTRAL! . . . . . . . . . . . . . . . . . .
At Sunset Central YOU ARE SO CLOSE YOU CAN FEEL THE OCEAN! As soon as you walk into SUNSET CENTRAL you take a deep breath and
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
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
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
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
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
Gorgeous Oceanfront Waipouli Beach Resort G302, one of Waipouli Beach Resort's most desirable Ocean Front suites, offers spectacular 180 degree unobstructed ocean and sandy beach views. You can see the ocean from every room! Fall asleep to the
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Oceanfront! Exquisite and Tranquil #1 Rated Oceanfront 2 bedroom, 3 bath condo at the beautiful #1 Ranked Waipouli Beach Resort & Spa on the island of Kauai.
There is Central AC in this condo as well as a full size washer and dryer for
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapaa
Royal Oceanfront G101, an Exquisite Ground Floor Oceanfront Villa! Privacy, "Expansive Lawn".
Lanai Furniture by Kingsley Bate!
Paradise Found! Royal Waipouli Beach Resort Oceanfront Ground Floor Villa
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Experience resort. style oceanfront living in this stunning 2. bedroom, 3. bath condo at Waipouli Beach Resort & Spa, a premier destination on Kauai. With breathtaking direct ocean views from every room, this luxurious retreat is just steps from
- 2 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kapaa
$325 per night up to 4 guests. $35 per night for each additional guest. Due to oceanfront location, this unit is located within the Tsunami Evacuation Zone.
Honu Hale Kai is a warm and casual home base for your family vacation
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Spectacular top-floor 2-bedroom penthouse with panoramic views of Molokai and lanai, expansive lanai, and luxury upgrades-perfect for whale watching and indulging in an unforgettable Maui escape.
*Special Offer: Book any KBM rental home
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Freshly Renovated 2 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
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
Aloha! Welcome to Unit #764 at the beautiful and world-famous Ka'anapali Beach and the Whaler! Ka'anapali Beach is a destination featured on the Travel Channel and rated as one of the best beaches in the USA. The Whaler is a popular luxurious
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Aloha!
Welcome to Hale Mahina Beach Resort , your Maui home away from home.
Located between Lahaina and Kapalua, it is a short drive to many wonderful Hawaiian experiences. Beachside dining, sunset cruises, snorkeling excursions,
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Location, location & location! Or, you could say: Ocean, Ocean & more Ocean. Breathtaking ocean views are captured on the lanai's WEB CAM: https://hollyskauaicondo.com/#HKCWebcam
HollysKauaiCondo offers substantially advantages over the
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
2025 UPDATE: Pool and hot tub area will be closed for remodeling for approximately 3 months during Sep, Oct, and Nov 2025. Standard nightly rate is $280 plus tax & fees. With the 25% discount the nightly rate is $210.00 plus tax &
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kapaa
