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 14350, 50
The Iconic Bamboo house is a local famous for its shape and design. Situated in the most beautiful "downtown" Haena, short 2 minute walk to the only restaurant, coffee, bar and spa in the end of the road, also short 2 minute walk to the a white sand beach.
2 bedroom
4 bathroom
Sleeps 6
Hawaii> Hanalei
Experience the ultimate Hawaiian getaway on this exclusive 5-acre estate in Kona, designed by renowned architect Skip Goodell. Sleeping up to 20 guests, this property offers a perfect blend of privacy and community, ideal for family reunions, creative retreats, or large groups seeking a unique escape.
8 bedroom
8 bathroom
Sleeps 20
Hawaii> Holualoa
Experience the comfort and luxury of a spacious 2-bedroom, 2-bath suite at The Bay Club within Waikoloa Beach Resort on the Big Island. This well-appointed suite offers a large primary bedroom with a king-size bed and en-suite bathroom, providing a restful retreat after a day of exploring Hawaii's stunning landscapes.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Beautiful 8 bedrooms for 16 in Poipu Kai is located only 250 yards to Brenneckes Beach and 350 yards to Poipu and Shipwreck Beach! Paved walkway from your door to the beaches! Only a 1-7 min walk to many excellent restaurants, and the famous Grand Hyatt!
8 bedroom
6 bathroom
Sleeps 16
Hawaii> Poipu
Discover your ideal Maui escape at Napili Point Resort C32, a spacious 2-bedroom, 2-bath oceanfront villa nestled above Honokeana Bay. With 955 square feet of open living space, vaulted ceilings, and tropical decor, this villa provides a perfect blend of comfort and natural beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Escape to comfort and style at The Fairways at Mauna Lani 902 by Gather, a meticulously curated vacation home designed for relaxation and enjoyment. Located within a desirable community, this residence offers sweeping views of the lush golf course and horizon, perfect for unwinding after a day of exploring.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Welcome to your perfect Kauai escape! Our spacious 1000 sq ft oceanfront one-bedroom suite offers breathtaking views of Kalapaki Beach from dual balconies. The suite features a comfortable king bed, a separate living area with a sofa bed, dining table, and modern amenities like a coffee maker and refrigerator.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lihue
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.Registration Number: 840291500023, STR # 2707, GE-074-086-1440-03, TA-074-086-1440-03
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waianae
5-bathroom private residence nestled in the prestigious Mauna Lani Resort. Accommodating up to 8 guests, this architectural masterpiece designed by Lucky Bennett offers a perfect blend of Polynesian elegance and modern luxury, creating an ideal Hawaiian escape.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kamuela
Discover this beautifully updated one-bedroom, one-bath condo located just steps from the pristine sands of Sugar Beach in North Kihei, Maui. Designed for comfort and convenience, this condo comfortably sleeps up to four guests with a plush king bed in the bedroom and a new queen sofa bed featuring an upgraded Tempurpedic mattress in the living room.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Legally Permitted RentalRegistration Number: 1310320290057, TA-103-977-1648-02, GE-103-977-1648-02
1 bedroom
1 bathroom
Sleeps 2
Hawaii> . Honolulu
Discover this charming Waikiki Shore accommodation featuring a cozy, island-style interior designed for comfort and relaxation. The unit offers convenient on-site parking, perfect for compact vehicles, with a maximum occupancy of 2 guests. Please note that the parking stall is small and limited in space, suitable for compact cars only. Registration Number: GE-092-755-3536-01, TA-092-755-3536-01, 260040120007
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience a memorable stay at this beautifully furnished condo on the 15th floor of Discovery Bay in Waikiki. Wake up to calming views of the Pacific Ocean and enjoy modern comforts like a washer/dryer in-unit, high-speed WiFi, cable TV, and phone service for the USA and Canada. Registration Number: TA-003-274-5472-01, GE-003-274-5472-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to Lahaina and unwind in these charming hotel suites that offer a blend of comfort and convenience. The cozy bedroom features a king-sized bed, complemented by two well-equipped bathrooms, ensuring privacy and comfort. The living area includes a Murphy bed, perfect for additional guests or flexible sleeping arrangements.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover Wailea Ekahi 49B, a beautifully appointed ocean view condo nestled in the heart of Maui's renowned Wailea resort area. Located along the pristine sands of Keawakapu Beach, this luxurious ground-floor residence offers 952 square feet of elegant living space designed for comfort and seamless indoor-outdoor enjoyment.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
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
Welcome to Nani Kai Hale 403, a beautifully updated top-floor beachfront condo located on the stunning Sugar Beach in North Kihei, Maui. This spacious one-bedroom unit comfortably accommodates up to four guests with a plush king-sized bed in the bedroom, a sofa sleeper in the living area, and two full bathrooms for your convenience.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a relaxing stay at this comfortable Lahaina hotel, featuring air-conditioned rooms with balconies overlooking the sea or garden. Each room includes private bathrooms, modern amenities like a coffee machine, refrigerator, and TV, ensuring a convenient and cozy environment for guests.
1 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaanapali
Discover this charming studio condo located on the 3rd and 5th floors, offering a cozy retreat for up to 2 guests. The unit features a spacious 600-650 sqft layout with a comfortable king bed and a private balcony to enjoy the ocean breeze. The fully equipped kitchen includes a stovetop, oven, refrigerator, microwave, dishwasher, coffeemaker, blender, cookware, and tableware, making meal preparation convenient.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the comfort and convenience of this spacious ground-floor condo, perfect for families or groups seeking a relaxing getaway. The property offers private patios and a 154 sqft enclosed yard, ideal for outdoor enjoyment. Inside, the gourmet kitchen is equipped with granite countertops, a travertine backsplash, a Wolf cooktop, convection oven, microwave, Sub-Zero refrigerator, and a 2-drawer Fisher-Paykel dishwasher, providing everything needed for preparing delicious meals.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
You are in paradise when you stay with us in the Top Floor Living space at Magic Skies! With 1,954 sq feet, the top floor is a complete living quarters! Enjoy the entire top floor all to yourselves, complete with 3 bedrooms (2 kings, 1 queen), 3 bathrooms (2 are en-suites), kitchen, dining table for 10, living room, laundry, game room and 1,402 sq of lanais with panoramic ocean views!
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Hilo
Nestled within the exclusive Mauna Kea Resort enclave, Amaui Villa 9 offers a luxurious Hawaiian retreat with stunning ocean vistas and thoughtfully designed living spaces. The open-concept kitchen, equipped with high-end appliances and natural stone countertops, flows seamlessly into the dining and living areas, creating an inviting environment perfect for entertaining or relaxing.
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Kamuela
Experience the perfect family escape at WorldMark Kihei on the beautiful island of Maui. This spacious 1-bedroom suite offers a fully equipped kitchen, separate living and dining areas, and a private lanai with stunning views. The bedroom features a plush king-size bed, while the living area includes a queen-size sleeper sofa, making it ideal for families.
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Welcome to your ideal Oahu studio condo rental in Waikiki, offering a perfect blend of comfort and convenience for your tropical vacation. Located at the start of Waikiki near the marina, this condo provides easy access to stunning beaches, world-class shopping, and fantastic restaurants. Registration Number: TA-232-888-7781-01, GE-232-888-7781-01, 55477899977
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover a breathtaking retreat perched atop the world with wide-open ocean views that showcase Magic Island and Ala Moana Beach Park. This Hawaii condo offers the perfect blend of indoor and outdoor living, featuring a spacious lanai to enjoy the stunning scenery every day.Registration Number: TA-033-709-5168-01, GE-033-709-5168-01, 1260110010184
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Welcome to Napili Point C35, a beautifully appointed 2-bedroom, 2-bathroom oceanfront condo situated in the heart of Napili on the sunny shores of West Maui. Covering 1,059 square feet, this spacious retreat comfortably accommodates up to 6 guests, making it ideal for families, couples, or groups seeking a relaxing beachside escape.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Napili Point B36, a beautifully renovated oceanfront retreat located along Maui's dramatic west coast. This spacious 2-bedroom, 2-bathroom condo offers 1,106 square feet of modern comfort and can host up to 6 guests. Recently refreshed, the unit features sleek tile flooring, coastal-inspired decor, designer lighting, and stylish furnishings that create a relaxing island atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the serenity and natural beauty of Kauai at Ka'iualani in Princeville. This updated condo features two spacious bedrooms, a fully equipped luxurious kitchen, and comfortable living and dining spaces. The property is beautifully furnished, offering indoor parking, air conditioning, and laundry facilities for your convenience.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Princeville
Discover the ultimate Hawaiian escape at Marriott’s Maui Ocean Club, located along the pristine Ka'anapali Beach. This luxurious oceanfront resort combines comfort, natural beauty, and exciting outdoor activities for an unforgettable vacation experience.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a comfortable stay in our Garden View 1-Bedroom Condos located on the 1st through 6th floors. Each unit features a private balcony with lush garden views, perfect for relaxing outdoors. Inside, you'll find a fully equipped kitchen with stovetop, oven, refrigerator, microwave, dishwasher, coffeemaker, blender, cookware, and tableware, making meal preparation effortless.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the tranquility of this private home nestled in the historic Manoa Valley, surrounded by lush greenery and offering breathtaking views of Diamond Head and the mountains. Located on a quiet, tucked-away street, this charming two-bedroom residence provides a perfect retreat just minutes from Honolulu's vibrant attractions. Registration Number: TA-067-237-0866-01, GE-067-237-0688-01, 290060130000
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Escape to this charming Waikiki studio in Honolulu, offering a peaceful retreat for up to 4 guests. The cozy living space features 2 beds — a queen and a full — perfect for relaxing after a day at the beach. Unwind in the relaxing bathtub or enjoy modern amenities like WiFi and air conditioning that make your stay comfortable. Registration Number: 260060020015, GE-208-244-0704-01, TA-208-244-0704-01
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Discover the charm of Castle Bamboo Waikiki Hotel #1102, a cozy retreat just a few blocks from Waikiki Beach. This recently remodeled unit offers a warm island aesthetic with beautiful hardwood flooring throughout. Wake up to stunning views of Waikiki and the Pacific Ocean from your private, furnished lanai, ideal for morning coffee or evening cocktails. Registration Number: GE-138-699-9808-01, TA-138-699-9808-01, 260230670085
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
This charming one-bedroom suite offers breathtaking views of the Pacific Ocean, perfect for a relaxing getaway. The spacious suite includes two bathrooms, a comfortable living area, and a fully equipped kitchen to meet all your needs. Step out onto your private balcony and enjoy the mesmerizing ocean vistas or unwind by our tranquil Koi Pond.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Immerse yourself in the stunning beauty of Kauai from this spacious 1,000 sq ft oceanfront hotel room, featuring a luxurious king bed, dual balconies with ocean views, and a separate living area with a sofa bed. The unit is fully equipped with all linens, towels, a coffee maker, refrigerator, and bathroom essentials, ensuring a comfortable stay without the need to bring anything.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lihue
Experience the perfect blend of comfort and elegance at The Point at Poipu, part of Diamond Resorts on Kauai. This oceanfront retreat offers spacious two-bedroom suites approximately 1,300 square feet, designed for relaxation and convenience. Each suite features a master bedroom with a king-sized bed, a guest bedroom with a queen or two twin beds, and a sleeper sofa in the living area, accommodating up to six guests comfortably.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kauai
Escape to this charming studio located in the heart of Honolulu. The space offers a comfortable and inviting atmosphere, perfect for unwinding after a day exploring the city. The bathroom features a bathtub and a hair dryer, providing all the essentials for a relaxing stay. Registration Number: TA-210-288-8448-01, 260210260118, GE-210-288-8448-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
This charming 3-bedroom house in Kailua offers a perfect retreat for your Hawaiian vacation. Located in a peaceful neighborhood, it features two queen beds and two twin beds, ensuring restful nights for all guests. Modern amenities include air conditioning, high-speed WiFi, a refreshing pool, and a washing machine for your convenience. Registration Number: 44013049, TA-173-947-0848-01, GE-173-947-0848-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kailua
Welcome to your own Deluxe 2-Bedroom Beachfront Maui condo, ideally situated just steps from the stunning Keawakapu Beach on the sunny South Shore in Kihei. This beautiful property offers breathtaking ocean views from the private lanai/balcony, perfect for relaxing with your favorite tropical drink while enjoying gorgeous Maui sunsets.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> kihei
Welcome to Maui Kamaole #A-106, your ideal tropical retreat in South Kihei. This beautifully renovated one-bedroom, two-bathroom condo offers a perfect balance of comfort and elegance, set within the lush, landscaped grounds of the sought-after Maui Kamaole resort.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Pualani Island Getaway - Ohana - 30-night minimum
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Welcome to your dream vacation in Wailea, Maui! This stunning two-bedroom residence blends comfort, style, and convenience, creating an unforgettable island escape. The spacious open-plan living area is tastefully furnished, featuring plush seating and a flat-screen TV, perfect for relaxing after a day exploring the island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Experience the ultimate Hawaiian escape at Marriott's Kauai Lagoons - Kalanipu'u, where our spacious two- and three-bedroom villas offer comfort, style, and exclusive access to a private beach. Each villa spans approximately 1,044 square feet, providing ample space for families or groups of up to six guests.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kauai
Welcome to Hale Kaimana, a luxurious oceanfront retreat located in the prestigious Colony Surf building on Honolulu's Gold Coast. This beautifully appointed 2-bedroom, 2-bathroom condo offers spectacular panoramic views of Diamond Head and the Pacific Ocean, making it an ideal escape for couples, families, or anyone seeking a peaceful beachfront getaway.Registration Number: TA-057-714-2784-01, GE-057-714-2784-01, 310320100112
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Experience a serene retreat in our luxurious 1-bedroom Golf Villas with breathtaking fairway views in Kapalua. This 1000 sq ft villa offers a spacious living area with a queen sofabed, a fully equipped kitchen, and a private balcony to enjoy stunning sunsets.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Come and unwind in this beautifully renovated one-bedroom condo at the iconic Ilikai Hotel. This spacious unit has been thoughtfully updated to ensure a comfortable and relaxing stay for travelers. Sleeping arrangements include a luxurious California King bed and a sofa sleeper in the living area, comfortably accommodating up to four guests. Registration Number: GE-035-279-4624-01, TA-035-279-4624-01, 1260100070434
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Make yourself at home in this welcoming Waimea retreat. This spacious suite offers a comfortable bedroom, a cozy living room, and a modern bathroom with a walk-in shower and complimentary toiletries. The well-equipped kitchenette includes a refrigerator, microwave, and kitchenware, ideal for preparing meals during your stay.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimea
This stunning 2-bedroom, 2-bath Maui vacation condo on the 7th floor offers a perfect blend of luxury and comfort. Newly remodeled, the home features tasteful furnishings, elegant stone flooring in the living area, and plush carpeting in the bedrooms.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this stylish studio at Ilikai Hotel Lite #742, offering stunning ocean views and modern comforts. Located adjacent to Waikiki Beach and Yacht Harbor, this condo provides easy access to Honolulu's top attractions including Ala Moana Beach Park, Hawaii Convention Center, and Ala Moana Center.Registration Number: TA-170-021-2736-01, GE-170-021-2736-01, 260100070105
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
