select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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,
a.`title_rewritten`, a.`custom_url`, a.`ai_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 = CASE
WHEN postalCode IN (96701,96706,96707,96712,6717,96730,96731,96734,96744,96782,96786,96789,96792,96826,96795,96797,96813,96814,96815,96816,96817,96818,96819,96821,96822,96825)
THEN
CASE
WHEN (registration_number like '%TA-%' or registration_number like '%TA %') THEN 1
ELSE 0
END
ELSE 1
END
and postalCode!=''and round(price)>0 and bedrooms>=0 AND LENGTH(href) > 0 AND sleeps>=1 order by rating desc LIMIT 16300, 50
BRAND NEW FAMILY RETREAT
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Spacious 3BR Townhome – Steps to Napili Bay, Pool, Hot Tub & More!
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
In south Kihei, this elegant top-floor condo is accross the street from Kam III beach, one of the best beaches on Maui. Great amenities & conveniences: WiFi, electric standing desk, washer/dryer, new and quiet AC, within walking distance to shops, restaurants, grocery store and activities.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Your group will be close to everything when you stay at this centrally-located home near Pahoa Town where you'll find local shops, a variety of restaurants, and the gateway to exploring the Puna District. The 3 bedroom 2 bath home is located in a quiet Hawaiian neighborhood with mature landscape to provide additional privacy while you relax on the lanai.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Pahoa
Summary:
6 bedroom
6 bathroom
Sleeps 18
Hawaii> Princeville
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Whether you’re seeking adventure or looking for relaxation, Marriott’s Waikoloa Ocean Club is a truly unique vacation destination. This welcoming oasis celebrates the breathtaking landscapes of the Big Island — its white-sand beaches, lush green flora and sparkling waterfalls.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Kailua-Kona is waiting for you at this spacious 2-bedroom condo. Inside, guests will find a king bed and a queen bed, perfect for a good night's sleep. With amenities like WiFi, AC, pools and a fitness room, you'll have everything you need for a comfortable stay just across the street from the ocean and minutes from Kona Village.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Spacious, comfortable villas encourage restoration and rejuvenation during your stay on the island paradise of Hawaii. The resort's location on the island lends itself to golfing, hiking, horseback riding, biking, snorkeling, whale-watching, surfing, and more.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
The community of Black Sand at Makena consists of a 7 lot oceanfront compound with gated entry. This home affords one of the most outstanding view locations in all of Makena. The home, originally purchased in 2008, has had extensive upgrades and has been beautifully maintained in a pristine condition.
6 bedroom
6 bathroom
Sleeps 14
Hawaii> Kihei
Find your bliss in this luxurious space, where unequaled comfort provides an intimate refuge. Nicknamed "The Garden Isle" for its expanses of lush rainforest, Kauai has bedazzled generations with its miles of solitary beaches and profusion of aquatic wonders.
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Lihue
Relax at the pool or on the beach, hit the trails, or enjoy tons of daily activities and entertainment right on site–the choice is yours at this oceanfront property in Lahaina! Spend the day lounging on beach chairs or enjoying a private beach cabana.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Polynesian Shores 211 – your perfect island getaway! This spacious second-floor condo has 2 bedrooms and 2 bathrooms, comfortably accommodating up to 6 guests. The primary bedroom boasts a cozy king bed and a private ensuite. The second bedroom features two twin beds, and the living room offers a queen sleeper sofa for additional sleeping space.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
He mau makana nâu kêia na kô mâkou kûpuna — These are gifts for you from our elders. Hawaii Volcanoes National Park is a refuge for native flora and fauna, many of which are endemic to the Hawaiian Islands. Enjoy walking or biking around the park, spend a day hiking at Akaka Falls Stake Park or visit Punaluu Black Sand Beach - you will love your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Pahoa
Wailea Elua 2102 is a delightful one-bedroom with a bonus room, two bathroom second-floor condo that comfortably accommodates up to five guests. Designed with a relaxed, island-inspired style, this residence blends casual elegance with modern comfort, creating an inviting retreat for families.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Wailea
Summary:Registration Number: GE-654-456-7811-01, TA-321-546-8190-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Summary:Registration Number: GE-654-456-7811-01, TA-321-546-8190-01
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Summary:
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Village Manor Beach Side Apartment #D21. Waipouli, Kapa'a, Kaua`i Vacation Rental with distant ocean views and coconut grove views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Poolside Poi'pu Condo - 2BR Kahala at Poipu Kai
3 bedroom
2 bathroom
Sleeps 4
Hawaii> KOLOA
More than just a city on the beach, Waikiki has plenty of exciting options to keep visitors entertained. Kalakaua Avenue and Kuhio Avenue are home to high-end shopping, fine dining, and major entertainment venues such as the Waikiki Beach Walk, and the International Market Place. Registration Number: TA-175-366-1440-01, 260210780000, GE-175-366-1440-01
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
More than just a city on the beach, Waikiki has plenty of exciting options to keep visitors entertained. Kalakaua Avenue and Kuhio Avenue are home to high-end shopping, fine dining, and major entertainment venues such as the Waikiki Beach Walk, and the International Market Place. Registration Number: TA-175-366-1440-01, 260210780000, GE-175-366-1440-01
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
More than just a city on the beach, Waikiki has plenty of exciting options to keep visitors entertained. Kalakaua Avenue and Kuhio Avenue are home to high-end shopping, fine dining, and major entertainment venues such as the Waikiki Beach Walk, and the International Market Place. Registration Number: TA-175-366-1440-01, 260210780000, GE-175-366-1440-01
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Step into a world of relaxation, surrounded by swaying palm trees and vibrant hibiscus blooms. Your spacious suite features a full kitchen, separate living room, and private lanai. Refresh yourself in our sparkling outdoor pools, perfect for relaxation or family fun.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Step into a world of relaxation, surrounded by swaying palm trees and vibrant hibiscus blooms. Your spacious suite features a full kitchen, separate living room, and private lanai. Refresh yourself in our sparkling outdoor pools, perfect for relaxation or family fun.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Step into a world of relaxation, surrounded by swaying palm trees and vibrant hibiscus blooms. Your spacious suite features a full kitchen, separate living room, and private lanai. Refresh yourself in our sparkling outdoor pools, perfect for relaxation or family fun.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Welcome to Kona! We're thrilled to have you stay in our spacious, townhouse-style condo.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Plantation Hale #C5
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Welcome to our dreamy Kona getaway! This newly refurbished, luxury beachy retreat is designed for relaxation and convenience in the heart of Kailua Kona. Great for the Sunsets! Walking distance from Kona's best restaurants and bars, it provides easy access to Ali'i Drive and all it offers, including beaches, snorkeling, sightseeing, shopping and eateries.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua-Kona
Escape to a peaceful, off-grid getaway at the end of a scenic two-mile dirt road. This cozy one-bedroom rental is designed for comfort and simplicity, featuring a screened-in living room and fully equipped screened-in kitchen — perfect for enjoying fresh air and beautiful views without any bugs.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kurtistown
Summary:
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lihue
This beautiful brand new home is overlooking Hanalei Bay & the beautiful tree canopy of Princeville. With a 10 min walk to the 1 Hotel beach, a short walk to Princeville Center and a quick drive to Hanalei Town, it is the ideal central north shore location.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Princeville
Aston Maui Kaanapali Villas #267 - Premium one bedroom loft, with two bathrooms, and sleeps four guests.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
This 2-bedroom condo in Kihei is perfect for your escape to relaxation. With a king bed and a queen bed, guests can unwind in comfort. The property also features 2 bathrooms with shower amenities. The living room includes a cozy sofa bed for extra sleeping space.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience Lahaina like a local in this welcoming 1-bedroom condo. The cozy bedroom features a comfortable queen bed, perfect for a good night's sleep. Enjoy the fantastic amenities like WiFi, fitness room, and a convenient washing machine and dyer in unit.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
This listing is a combination of 2 separate units close to one another.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Lahaina
Kick back and relax in this calm, stylish space. This condo features an upgraded interior that provides a comfortable and enjoyable stay. It is ideally located for easy access to the airport and nearby attractions, perfect for exploring the area.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Enjoy stunning sunsets and whale watching at this centrally-located condo. Centrally located in West Maui, this beautiful condo is ocean front! The living room includes a queen sleeper sofa, with a king bed in the cozy bedroom. Located close to world-class beaches, dining and attractions in Kaanapali, Napili and Kapalua.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
5-bath townhome offers a rare opportunity to stay in one of Maui’s most peaceful and private communities. This is a comfortable, casual home—perfect for families or small groups seeking direct ocean access, a relaxed setting, and plenty of space to unwind.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Nestled high in the cliffs of Princeville, this Kauai resort blends natural and man-made pleasures into magnificent harmony. Take the opportunity to enjoy a meditative retreat at the on-site spa, or use the resort as a basecamp for your many Hawaiian adventures.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
You are guaranteed Ocean FRONT, the best view class in the entire resort.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to this charming condo in Lahaina with private lanai's at the luxurious, five star Marriott's Maui Ocean Club Resort. This amazing property features 2 bedrooms with a king bed and a full bed, along with 2 bathrooms equipped with a jetted bathtub in the master bath.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Hawaiian Botanical Themed waterfall room is a top floor queen bedroom with bench, has ensuite bathroom featuring tiled walk-in shower with regular head, rain shower head and handheld, double sinks & granite counter tops. The room also has a mini frig, coffee, microwave.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hilo
This room is large and is located on the main floor with two beds, a queen size and a full size bed, sitting area, frig, microwave, coffee, double doors lead out onto front lanai, with ocean view. Bathroom is dedicated but is also the bathroom for the living area on the main floor, not an ensuite.
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Hilo
With double doors leading to a koi pond on the main level this koi pond themed room is quaint and comforting. The room is fully handicap accessible with 36" doors, has a king size bed, a sitting chair, bench, no steps, private bathroom with tiled walk-in shower with grab bars, bench, shower chair, regular, rain shower and hand-held heads.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hilo
Enjoy the panoramic views from your large top floor ocean facing room with private bathroom, walk-in shower and walk-in closet and kitchenette. The room has sea life themed decor and custom art and woodwork featuring a wall of ocean facing windows and double doors leading onto the ocean facing lanai and a whale wall carving.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hilo
Turtle lovers, you will feel right at home with queen size bed with panoramic ocean views. It features an ensuite w granite counter tops, double sinks & marbled tile walk-in shower w rain shower head, regular head & hand-held. The room also has a mini frig, coffee & micro.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hilo
Feel emersed in the Hawaiian culture in the Hawaiian Kingdom Hula Room. It is located on the top floor with a King bed, it's a large room with a sliding door which provides access to back lanai with waterfall views, Bathroom is dedicated but is also the bathroom for the living area on the top floor, not an en-suite.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hilo
Ocean-View Lanai (800 Sq Ft) | 4 Mi to Ho'okipa Beach Park | Newly Renovated Kitchen
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haiku
