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 '%special%') AND sleeps>=1 order by rating desc LIMIT 0, 50
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
Kauai Haven is nestled within a classic Hawaiian style backdrop, resting on the edge of a bluff overlooking the spectacular Pacific Ocean. An award-winning designer, who is part of the Coastal Living Network of designers, has designed this ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Ask about 10 day specials/ 2 person stays and 8th night free if you book a 7th night stay!!!!
https://www.youtube.com/watch?v=VigSB3M4z3w
The above is the link to a video of my house.
Makai Hideaway is quiet and peaceful. Unwind and ...
- 3 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Captain Cook
Aloha! Welcome to the luxurious Maui Vista 1315!
This beautiful property can be your own personal paradise and has everything you need to create the memories of a lifetime.
Inside this unit, you’ll find classy décor with a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Located in central Kihei, nestled on the northern end of beautiful Halama Street. Family-owned and operated legal short-term rental. This ocean front, 2 BR 1 BA unit, has a full kitchen, bathroom and a sofa sleeper pullout in the living room. ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kihei
Located in central Kihei, nestled in on the northern end of beautiful Halama Street. Family-owned and operated legal short-term rental. This ocean front, 1 bedroom 1 bath unit, has a full kitchen and bathroom. Cozy, quiet, great for couples and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
OCEAN VIEW
BOOK WITH US TODAY!
BEST REMODEL in MAUI VISTA
Completely gutted with NEW EVERYTHING!!!
Tigerwood Hardwood Floors, Italian Tile, Travertine, Fresh Paint, Ceiling Fans, All New Furniture, New Mattress...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Come enjoy the beauty of Hawaii in our perfectly located condo at Waikoloa Beach Villas. Just 20 minutes north of the Kona airport you will find yourself in paradise!
Our property is perfect for 2 couples or a family of 3. Decorated to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Village
Royal Oceanfront G101, an Exquisite Ground Floor Oceanfront Villa! Privacy, "Expansive Lawn".
OPEN DATES FALL 2024 SPECIAL
Lanai ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Unbelievable views from almost every room in the house!
Located just minutes above Kailua-Kona town, this spacious two-story home is a beautiful island getaway for your whole family. Two master bedrooms with king size beds and private ...
- 4 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
Summer Booking Special - Up to 50% off our normal rates
Right on the ocean! Super Clean! Great Location! High-speed WiFi, Free Parking, NO RESORT FEES!
Fortunately, our condo was not affected by the fires in Lahaina. There are ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Located in the Award Winning Resort Community of Hualalai Four Seasons.
3 Bedrooms / 3 Baths
Living Area: 2,657 sqft and 940 sqft of Lanai
Ocean,Golf Course and Mountain Views
Spilt Level Townhouse
2 ~ 4 person Golf ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Relax and enjoy panoramic views and beachfront perfection. The 2 bedroom, 2 bath home is located in the B building and sits only steps from an extraordinary sandy beach offering the famous 'five island view' along with the accompanying ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Come enjoy our spacious 1 bedroom, 2 bathroom condo in one of the most beautiful spots in all of Maui. Located directly on the 4th fairway of the Kapalua Bay Course can be both entertaining and comical as you watch the golfers pass by. A ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
HONUA KAI RESORT AND SPA SUITE 215 welcomes you with so much ALOHA....
This spacious studio unit with full bath can accommodate up to 4 guests.
Included in our fabulous price are many owner-supplied extras such as:
• Beach...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Your dream vacation awaits you on the Island of Maui in this beautifully upgraded condominium located across the street from Kamaole II Beach Park. Surrounded by lush tropical landscape, Maui Banyan is just steps away from restaurants, shops, ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Aloha and Welcome to Maui Ka’anapali Villas NEWEST STUDIO RENOVATION!
Our beautiful NEW Studio, 1 Bathroom Corner Unit is extremely RARE! PROFESSIONAL PHOTOS COMING SOON! We have just completed an entire renovation on this special ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Top to bottom fully renovated 1244 sq ft condominium in Maui Kamaole.
Enjoy ocean views and the quiet location of our unit located in the D Cluster of Maui Kamaole.
Tastefully decorated with all the amenities including brand new ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Hale Halia is just steps from Poipu Beach park with unparalleled oceanfront views and is a great family vacation destination. This lovely two-story floor plan is a design that takes full advantage of the magnificent Poipu coastline and sunset ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Koloa
We are running a special price so you can leave that nippy weather and extend your summertime! Check it out.
In the evening and weekend you can have a front row seat right from your lanai or window of the whales and turtles! Just in ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Village of Ma'alaea
Welcome to the Premier destination on Kauai, The Waipouli Beach Resort and Spa! .
Waipouli Beach Resort and Spa is located beach front on the Coconut Coast between the historic towns of Kapaa and Wailua. Located convenient to both ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Welcome to Olu’olu (meaning a “happy" home, in Hawaiian), which it will always be! Enjoy beautiful Wailea, in South Maui at our home!
Olu’olu is remodeled, beautiful and relaxing. Your first view from the living room lanai, across ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailea, Maui
JUST RENOVATED DESIGNED BY A NYC ARTIST. THIS BEAUTIFUL CONDOMINIUM IS BORDERING KIHEI AND WAILEA IN SOUTH MAUI, HAWAII. IT IS LOCATED AT THE GORGEOUS MAUI HILL RESORT BY ASTON. THE PROPERTY HAS BEAUTIFUL GARDENS, SWEEPING VIEWS OF THE PACIFIC ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This is Wailea Living!
Nestled in a private and secluded enclave between the Wailea Blue Course and the Wailea Tennis Club, the Wailea Grand Champions Villas are where travelers can golf, play tennis, be pampered at the Wailea Grand Spa, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
$350/NT+TAX SPECIAL PLUS $100 LAVA LAVA BEACH CLUB CARDS FOR A LIMITED TIME!
UNBELIEVABLE SPACIOUS TOWNHOME ILLUMINATED WITH A WALL OF NATURAL LIGHT!
Kulalani 904 is one of the newest homes developed within the resort. At a ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Mana Kauai Island Vacations Presents Holo Makani!
The gated estate of Holo Makani is located in the sand on a dramatically beautiful and pristine beach on Kauai's North Shore coast line. Enjoy the lull of the ocean and the gentle trade winds...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Hanalei
Our luxurious fully remodeled, non-smoking, front-row (G building) condominium is furnished and decorated to meet your highest expectations. It offers spectacular panoramic views of popular Kamaole III Beach and the ocean and neighboring islands...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Mana Kauai Island Vacations presents the charming Palaka Cottage!
Recapturing bygone days, the completely renovated 2 bedroom 1 bath "Palaka" Vacation Cottage draws one back to the days of the sugar industry's plantation cottages. With ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Hanalei
Summer Special! Perfect Couple Retreat! Enjoy Sweeping Ocean Views High End Top Floor Remodel
TOP FLR OCEAN VIEW BEAUTIFUL REMODEL, ENHANCED CLEANING
https://www.youtube.com/watch?v=Or2Innjn2LE
Enjoy Sweeping Ocean ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Wailea
SEE OUR 10% SPECIAL TILL DEC
Absolutely beautiful location. This property is gracefully set beside the 50 foot long pool shaped as a fish. This is quiet and peaceful, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Thank you for looking at our home for your vacation. Though we are truly saddened by the devastating fire in Lahaina, we are continuing our support for many as they recover from this deadly incident. As part of our help in this recovery process,...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapalua, Maui
KOHALA LUXURY RENTALS
SUMMER 2024 SPECIAL RATE $350/NT+TAX AND $100 LAVA LAVA BEACH CLUB DINING CARD!
KULALANI 501 - Two Master Suites, Two Levels, New Furnishings, Steps from the Pool!
Welcome to Kulalani 501 ~ A beautiful ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Waimea
CHECK OUR 10% SPECIAL TILL DEC
These Suites are in a beautiful setting. You will hear the waves crashing. See the turtles in front of your vacation Suite. Enjoy the wonderful warm Pacific Ocean, the waves crash ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Mana Kauai Island Vacations Presents the Kalihiwai Beach House.
Kayaks, boogie boards, surfboards, and stand-up paddleboards are yours to be enjoyed every day of your vacation. No need to pay the high rental fees.
Kalihiwai Beach is one of...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kilauea
Come to West Maui, and together, let's rebuild dreams, one vacation at a time.
BEST LOCATION AT PAPAKEA! Gorgeous oceanfront condo owner managed. Non-smoking. Our F302 unit (F ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Mana Kauai Island Vacations present the charming Umetsu Cottage!
Welcome to our idyllic one bedroom, one and a half bath cottage at the Hanalei Bay. This remodeled beach cottage reflects the era of Old Hawaii. Located steps from the iconic ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hanalei
Welcome to our beautiful luxury Beach Villa in Ko Olina on the island of Oahu! The complex of the Beach Villas is an Oceanfront property. Our villa is spacious with 3 Bedrooms and 3 Baths in over 1500 square feet of living space. Our villa ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapolei
*Newly Remodeled*
Kahikole 4 bedroom Luxury Villa on the 8th fairway of the Nicklaus designed Hualalai Golf Course. Incredible views over the 8th and 4th greens, ocean views and sunsets.
Kahikole Villa offers 3 bedrooms in the main ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua Kona
Thank you for checking us out! We are located North Kaanapali beach!
At last, you’ve found an extraordinary combination of secluded privacy, breath taking ocean views and magical sunsets. Meditate and relax, stroll by the Koi ponds and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Hale O Ho'okipa which translates in Hawaiian to 'House of Hospitality'.
Truly one of the nicest ocean front villas in the Kapalua Bay Villa Resort! Some make this claim without conviction, but we have provided quality photos ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kapalua
You will love this luxurious, air conditioned, two bedroom, two bath, 1300 square foot Villa! Fully furnished, with its own spacious, private dining lanai, this unit was completely remodeled in 2020. It is immaculate, impeccably maintained and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Unit B1 is Ocean Front at Sunset Hale. Newly updated 2-bedroom 2.5-bath ground floor oceanfront unit.
The Kitchen, living room and dining area are all facing out to beautiful ocean and sunset views! Enjoy these views from inside or ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Haleiwa
LAST MINUTE $100+ PER NIGHT SPECIAL SUMMER RATES FOR THESE STILL AVAILABLE DAYS LEFT ON OUR 2024 SUMMER SEASON CALENDAR !!🌅
Escape to Hawaii for your vacation !!🏝️
Great condo , Great Location, Great Rates
Book ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
FOR A WOW EXPERIENCE! Less than an hours drive from Honolulu (yet only 10 minutes from local shops and restaurants) but a world away from big city life. If you want to relax and enjoy the ocean, beach, mountains and get a chance to see the real...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Waianae
Our beautifully appointed condo is perfect for a family (6 maximum), a couple or two couples (as there are 2 primary bedroom suites, each with a king size bed, private bath and flatscreen ). The condo features a spacious living and dining area ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
A relaxing Hanalei Vacation can be yours at Hale Ylang Ylang. Steps to the beach and near the heart of Hanalei, this luxury two bedroom townhome has views of the ocean and our north shore mountains.
Relax in this comfortable home in...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hanalei
Mana Kauai Island Vacations Presents Ohana Anini
****Certain Restrictions Apply, New Reservations Only****
Ready to experience all of the beauty and adventure that the north shore of Kaua’i has to offer? Stay at our spectacular Ohana ...
- 4 bedroom
- 5 bathroom
- Sleeps 11
Hawaii> Kilauea
Welcome to Hale Koi! From the moment you step into this tropical retreat, you'll be captivated by all its allure and uniqueness. A world of luxury unfolds, seamlessly merging with the breathtaking natural beauty that surrounds.
The ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapolei
SPECIAL RATES FOR NOV/DEC 2024 and 1Q 2025!
Relax in what feels like your open-air home in paradise!
Stunning views. Savor the outside air and breezes on your lanai. Superbly maintained. Consistent rave reviews (for ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Ko Olina
Prices start at $150,000/month and will not be pro-rated based on length of stay. Please inquire to get an accurate quote.
Banyan House is Oahu’s most luxurious vacation rental near the foot of Diamond Head. The exquisite home features...
- 8 bedroom
- 10 bathroom
- Sleeps 16
Hawaii> Honolulu
