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 '%new listing%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Cheerful and bright, this studio is located In a quiet neighborhood overlooking Hilo. The studio occupies the first floor of 2 story home. It has a private entrance and parking on site. A full private bathroom is adjacent to the studio. Guests ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hilo
Our condo is zoned "Hotel/Resort" so no need to worry about our zoning.
Hear the Waves....Lay in bed and listen to the waves! Spacious condo with direct beach & ocean view. A Sandy Swimmable beach is right outside your door.
*** ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Now with Air Conditioning! Yes we now have Daikin AC systems in each Bedroom, and Living room.
New Listing, other listing 5 Stars all the time. Spectacular Ocean Beach Front Maui Penthouses 6 Bedrooms 6 Baths.
Our MauiPenthouses are the ...
- 6 bedroom
- 6 bathroom
- Sleeps 18
Hawaii> Lahaina
Peaceful and centrally-located beach house along the Seven Mile Miracle, the surfing mecca on Oahu which includes Waimea Bay, Banzai Pipeline, and Sunset Beach. Large 2 bedroom/1 bathroom with high ceilings, off-street parking with spectacular ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Haleiwa
The condo is a 1Bedroom 1bath newly renovated; made up of all stainless appliances, tile floors, California king bed, pull out sofa sleeper, 60" HDTV/DVD player, and air conditioning.
There is a full kitchen equipped with a microwave, coffee...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
*** Low season fall 2024 discount. 15% off nightly rate on all travel dates from September 10 - December 15, 2024.
Escape to paradise at Wailea Ekahi Village 4A, Maui's dream vacation rental. This spacious 2-bedroom, 2.5-bathroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei
vrKCV - Mauna Lani Fairways 1601, Professionally Managed by Kona Coast Vacations
Feel the Aloha Spirit and relax in tropical paradise at Sandy Feet Retreat! Newly renovated in the summer of 2022, enjoy the modern Hawaiian vibe in this ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Boasting stunning panoramic views of the Pacific Ocean that extend deep into the home's living, kitchen, and dining areas, Wailea Elua 2204 is a luxuriously renovated second-floor villa featuring some of the most spectacular vistas South Maui ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Beautiful remodel and ideal Kihei location create beachy perfection for your island getaway.
Relax on your own huge lanai. Feel the wonderful trade winds and take in the island scents from the beautiful Plumeria trees and Hawaiian ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
vrKVR - Halii Kai 10B, Professionally Managed by Kona Coast Vacations
Hali'i Kai 10B - Your Piece of Paradise
Haili'i Kai 10B is a premier ocean view property inviting you to start your vacation mornings with a cup of Kona coffee ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa
Be the first to enjoy this STUNNING VIEW! First time being offered as a rental for your vacation, this condo has been meticulously remodeled and fully refurnished. Everything is new! From the flooring and appliances to the push-back glass ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Welcome to this breathtaking top-floor, Ocean View condo, the epitome of island life and comfort. This 2-bedroom haven offers the perfect retreat for your dream vacation. With stunning ocean views and an array of amenities, your stay here will ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihie
Escape to paradise and experience the wonders of Ma'alaea Harbor in this exceptional 2-bedroom condo. With its array of features and stunning ocean views, your vacation here promises to be truly unforgettable.
Step onto the grand ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
This property is hotel zoned, and not being affected by the recent proposed legislation.
Be one of the first guests to rent this very special ocean-front residence! Welcome to Wailea Beach Villa B202, an exquisite oceanfront retreat of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei
NEW LISTING JULY 2023. See my other Properties # 1503692 and # 3197017 and #3156864 for more 5-STAR reviews and to book multiple units in the same building.
Located on the 10th floor of the Waikiki Sunset with beautiful mountain and city...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Escape to Paradise at our Luxurious Kapalua Golf Villa!
Welcome to your dream Hawaiian vacation at this exquisite 2-bedroom, 2-bathroom retreat, nestled in the highly sought-after Kapalua Golf Villas. From the moment you step foot into ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Wailea Elua Village #1603 boasts some of the best Ocean Views and is located on one of the most prestigious properties on Maui. This condo has been lovingly maintained and updated to ensure it remains a luxury-level choice for those traveling to...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Experience the beauty of Waikoloa Colony Villas, Unit 1206! This exceptional and newly renovated, 2-bedroom and 2-bathroom condo is a lavish and cozy home base for your Hawaiian getaway. Property amenities include a BBQ, fitness center, pool, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Are you ready to indulge in an unforgettable escape on the stunning west coast of Hawaii Island? The Shores at Waikoloa Beach Resort beckons you to experience the tranquility and beauty of the Big Island. Nestled between two pristine golf ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Waikoloa Village
Experience breathtaking ocean views from our bright and spacious condo. This ground-floor unit features 2 bedrooms and 2 baths, just steps from the ocean—perfect for unwinding after a day of island adventures. Located conveniently in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Two bedroom, two bath private residence at the top of Lanikai with spectacular, panoramic views of the Kailua coastline. Feel on top of the world in this truly unique hilltop bungalow. Nestled just beneath the Lanikai Pillboxes, a popular hiking...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua
Modern and sophisticated with a splash of Aloha, #29 is an ideal spot for honeymooning couples and those returning for their tropical anniversary celebration. Tucked in the lush foliage at the back of Grand Champions, this condo has the unique ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Immerse yourself in the enchanting allure of Kailua-Kona at our 3-bedroom, 2-bath single-family haven, offering you panoramic views of the endless ocean horizon. Every detail has been meticulously attended to, including newly installed split AC ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Welcome to your dream retreat at Napili Surf Resort in beautiful West Maui. This beautifully remodeled beach haven is perfectly situated to offer you an unforgettable vacation experience. As you step into this cozy and thoughtfully decorated ...
- 1 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Lahaina
*Professional photos coming mid-December*
Beautifully remodeled home at one of Maui’s favorite properties! G302 is one of a handful of condos on property with a fantastic ocean and sunset view as well as views of Haleakala all from your own...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Escape to paradise in this newly updated, air conditioned, 2-bedroom vacation haven nestled within the prestigious Regency at Poipu Kai. This tropical retreat promises a perfect blend of modern luxury and Hawaiian charm, providing an ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Koloa
Centrally located in the heart of Ka'anapali, Maui Eldorado Kaanapali is the perfect place to enjoy your Maui Vacation.
This ground-floor end unit with an elevated garden view is very close to the front desk and outdoor parking - handy ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to your dream getaway at Maui Kaanapali Villas! Nestled along stunning North Kaanapali Beach, this charming 1-bedroom, 1-bathroom condo promises a rejuvenating retreat amidst ocean views and spectacular West Maui Sunsets. Maui Kaanapali ...
- 1 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Lahaina
New Listing! Welcome to Waikomo Stream Villas unit 100, your tropical gem in the heart of Poipu! Nestled in lush greenery and surrounded by vibrant flora, our one-bedroom condo is the perfect escape for those seeking tranquility and natural ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Welcome to your tropical getaway at the Kaanapali Royal Resort in the vibrant Kaanapali Beach Resort area. Our remodeled condo offers two bedrooms and two full en-suite bathrooms, ensuring comfort and convenience during your stay. H101 features ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
New Listing! The Location:
Perfectly positioned to accommodate everyone's interests, the Coconut Coast is flanked by epic coastline threaded together by the Ke Ala Hele Makale coastal bike and walking path. Just a 7-minute walk along the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lihue
Summary:
Discover Maui at Maui Banyan H109 apartment, a charming 1-bedroom, 2-bath condo steps from Kamaole Beach Park II. Conveniently on the first floor, it hosts up to 4 guests. Relax on the lanai amidst lush greenery, stroll to the nearby...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Come enjoy paradise at Keauhou Akahi unit #A408, at this two-bedroom, two-bathroom property. Enjoy your private lanai, with ocean views, and BBQ!
Keauhou Akahi is conveniently located in the Keauhou area of Kona. You are a close drive or ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to your dream Maui getaway! This highly updated 2-bedroom, 2-bath condo in Maalaea offers the perfect blend of modern comfort and breathtaking oceanfront views making it an ideal haven for families or friends seeking an unforgettable ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Our Kamaole Sands condo 8-106 has everything you need for the perfect Maui vacation.
-King bed and lovely en suite bathroom in the master suite.
-King sized sleeper sofa in the living room (yes, KING!) can sleep two more.
-Full second ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Escape to paradise at the prestigious Alii Oceanfront Resort on stunning Kaanapali Beach, Maui. This cozy and convenient 2bedroom, 2bathroom condo boasts a ground floor garden view from the spacious furnished Lanai, and steps to both the lush ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Hale Cassia by Gather, a serene retreat nestled in the heart of Princeville, HI. This three-bedroom, two-bathroom home is an ideal sanctuary for six guests seeking a quintessential Kauai experience. From the moment you arrive, the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Forget your worries and experience the best of the Big Island in this elegant, light-filled, quiet unit at the Mauna Lani Golf Villas. Wake up to the spectacular views of the sunrise above the sacred mountains of Mauna Kea and extraordinary ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Puako
Welcome to Mahana 405! This beautiful oceanfront condo on North Kaanapali beach sleeps 2-4 and features a convenient second bathroom. Enjoy stunning views of the ocean, beach and outer islands from the living room, dining area, bedroom, and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Aloha and welcome to International Colony Club 22. Cottage 22 is a special place where travelers keep coming back and keep it a secret! With Hawaiian Style on the exterior and modern charm on the inside. Our focus is comfort and the quality of ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Embrace the pinnacle of coastal living at our exclusive Kailua Village Penthouse, nestled atop the 7th floor, where modern elegance meets breathtaking ocean vistas in Kona, HI. This newly remodeled sanctuary features two lavishly appointed ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua - Kona
Your Ideal Vacation Stay Here! The view is awesome from this newly remodeled, spacious studio condo on the 27th Floor of the Hawaiian Monarch Hotel, just a few blocks from Waikiki Beach! This clean, modern, luxurious space is in a great location...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Oceanview! Waipouli Beach Resort F301 New Listing!
Waipouli Beach Resort F301 is Newly Decorated with a Beautiful Oceanview from the Living Room, Master Bedroom and Balcony, a 1 Bedroom, 2 Bathroom Beautiful Condo at the Top Rated, 1st ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Come and enjoy the spirit of Aloha at Kaleialoha 208 in beautiful West Maui. This one-bedroom, one-bathroom oceanfront condo is the perfect tropical retreat for couples, comfortably accommodating up to four guests. The unit boasts panoramic ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Hale PIKO is a beautiful 4 bedroom, 3 bathrooms, air-conditioned private home located just one block away from world-famous Poipu and Brennecke's beaches. Situated at the end of a cul-de-sac on a quiet street in sunny Poipu, you can walk to the ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
CHECK OUT OUR WEBSITE (paradisesandshawaiirentals.com)
E’komo mai “welcome” to Turtle Bay Ocean Villa unit 210! Newly updated, painted and re-decorated in July 2024, this charming 1700 sq ft villa will help make all of your ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Book 5 nights and enjoy the 6th night free! Valid for new reservations from 10/10/24 for stays between 10/10/2024 - 12/8/2024!
Welcome to your dream vacation home at Pacific Palm Villas, Hali'i Kai, located in the heart of the exquisite ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
BRAND NEW LISTING - Quite Top Floor End 2 BEDROOM 2 BATH Villa WITH A LOFT located in Fairway Villas Waikoloa in the Waikoloa Beach Resort on the Kohala coast of Hawaii island. This top floor villa has enough room for a family of 6. Take ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
A beautiful, unobstructed view of the deep blue Pacific Ocean and coral reef from every room! Our condo is located on an ocean bluff having the luxury of a 180 degree view of blue water from every room. Along with magical waves, you will see ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Discover Wailea Ekahi 39E, our exquisite 1-bedroom, 2-bath condo with stunning ocean views. Whether you're seeking a romantic getaway, a solo escape, or a tranquil spot to work remotely, this condo provides the ultimate coastal ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
