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
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 5
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 3
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
Welcome to our little slice of paradise on Keawakapu Beach, where the spirit of old Hawaii comes to life! Please contact us for seasonal discounts and special rates.
Nestled amidst the swaying palms and turquoise waters, Hale Hui Kai ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
. . . . . . . . . . . . . . . . . . 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 start to ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Royal Oceanfront G101, an Exquisite Ground Floor Oceanfront Villa! Privacy, "Expansive Lawn".
OPEN DATES FALL 2024 SPECIAL $395 per night plus taxes, outclean, resort fee, admin, damage protection
...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Great condo, awesome view and it includes the best selection of free beach gear in Waikiki (If you use this, it will add up to significant savings. See below for details.)
One of the best ocean view apartments in Waikiki, ideally located ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
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 ...
- 2 bedroom
- 2 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, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
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
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 ...
- 3 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
***The pool has just been renovated and is gorgeous beginning November 1st 2024***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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
ATTENTION: OUR POIPU SANDS COMPLEX IS BEING REROOFED FROM SEPTEMBER 2024 THROUGH DECEMBER 10, 2024 FROM 7:30 AM TO 6 PM (MONDAY THROUGH SATURDAY). THE CURRENT NIGHTLY RATE DURING THIS PERIOD REFLECTS A CONSTRUCTION DISCOUNT TO COMPENSATE FOR...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
$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,...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapalua, Maui
Pacific Paradise Maui at KAANAPALI VISTA
Last minute specials!!
Discounts on each night now through Dec 17, 2024!
Location! Location! Location! In heart of Kaanapali and all it's activities, dining, shopping and mesmerizing ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
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
OUR PICTURES REALISTICALLY REPRESENT; (taken in 2018)
Hawaii State Tax ID # TA‐183-831-8080-01
Aloha! Welcome to another Exclusive member of the Platinum Maui Vacation Ohana! All villas in this program are given Hawaiian names. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
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 F Building oceanfront...
- 2 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
Kaimalino Kai is a two story, 3 bedroom, 4 full bathroom oceanfront gem perched elegantly against the backdrop of Kailua Bay. Hear the sound of the waves crashing against the rocky shoreline while magical ocean views complement the elegant ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua
You are sure to enjoy a memorable vacation in this fully furnished, spacious retreat in an ocean view studio premium villa at The Westin Ka'anapali Resort Villas. Maui's boundless beauty and activity begin and end here!
The Studio Premium...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
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
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
CHECKOUT LAST MINUTE SPECIAL RATES
Escape to Hawaii for a great vacation !!
Great condo , Great Location, Great Rates
Book your stay today for your next getaway!!
(SLEEPS 2 MAXIMUM)
IF YOU ARE LOOKING FOR A WONDERFUL ...
- 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...
- 3 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 6
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...
- 3 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
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
Discover the delightful one-bedroom getaway nestled right within Haleiwa. Nestled just a 22-minute drive from the renowned Banzai Pipeline and 24 minutes from the stunning Turtle Bay Beach, this cozy retreat offers the perfect base for your ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> haleiwa
RAINBOWS & TROPICAL BREEZES
Aloha! Welcome to our condo at the Plantation in Princeville, on the beautiful Garden Island of Kauai. Come and enjoy Rainbows, Sunsets and Tropical Breezes. Our Hale faces East which allows for the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Yes you can afford this great vacation in our wonderful condo, located near Wailea . We are in the Hale Kamaole resort and are on the ground floor.
Features include:
1. Walking distance to Kamaole Beach 3 (it's right across the street :) ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to the newly renovated, top floor ''Turquoise Cove'' Villa - Makena Surf F301. This amazing property has unsurpassed ocean views, with a unique, double wrap-around lanai, perfect for enjoying tropical sunsets or catching the occasional ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Wailea
LIVE INSPIRED. LIVE KAPALUA! Pride of ownership is evident as you walk through the door of this wonderful corner location Golf Villa. As you enter, it's easy to just walk past the beautiful ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
