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 6200, 50
Experience the perfect blend of indoor and outdoor living in this spacious ground-floor condo located in Wailea Ekolu Village. Boasting stunning ocean views from the open lanai, this 2-bedroom, 2-bathroom home has been completely renovated with modern touches, including new windows, double-pane sliding doors, bamboo cabinets, and granite countertops.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Kihei
Experience the beauty of Maui with a stay at Kihei Kai Vista, Unit 2406, located in Maui Vista. This well-appointed 4th-floor condo offers breathtaking ocean views, where you can enjoy rainbows, whales during season, tropical bird songs, and spectacular sunsets from your lanai.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Escape to sunny South Maui in our beautifully renovated top-floor studio at Grand Champions Villas. This tropical-inspired space offers a comfortable king bed, modern bathroom, private lanai, and upgraded kitchenette with a larger fridge and dedicated freezer.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Wailea
Discover this beautiful private home in the prestigious Mauna Lani resort, offering 2,400 sq ft of elegant living space. The property features a heated pool and spa, set amidst lush tropical landscaping, perfect for relaxing and entertaining. This two-story dream residence overlooks the renowned Mauna Lani golf fairways, providing stunning vistas from almost every room.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimea
Discover the ultimate island getaway at vrKCV - Holua Kai 21, a beautifully appointed oceanfront residence in Kona on the Big Island. Set in the exclusive gated enclave of Holua Kai within the Keauhou resort community, this home offers breathtaking 180-degree views of the Pacific Ocean, coastline, and nearby golf course fairways.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Discover this absolutely stunning tropical plantation home in Princeville, Kauai, offering a blend of elegance and comfort. Inside, you'll find richly appointed spaces with rare solid Hawaiian Koa wood flooring and a spacious, open floor plan. The living room features 19-foot vaulted ceilings and sliding glass doors that open to a large wraparound lanai, perfect for outdoor living amidst lush tropical plants and mountain-waterfall views.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Princeville
Experience the perfect island escape at Kauai Kailani #315, a beautifully renovated boutique condo situated in the heart of Kapaa on Kauai's renowned Royal Coconut Coast. This charming third-floor unit offers a comfortable 600 sq ft of living space, ideal for couples or small families.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapaa
Stay longer and enjoy exclusive offers! Book 10+ nights for a free mid-stay clean, or 14+ nights to receive one night free. This hotel-zoned property remains unaffected by recent legislation, ensuring a worry-free stay. Ho'olei 24-6 features a gorgeous 3-bedroom garden view villa designed for seamless indoor-outdoor living.
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kihei
Welcome to Wailea Ekahi 48A, a stunning condo offering the largest floor plan with breathtaking ocean vistas. 5-bath retreat is ideal for couples or families seeking comfort and luxury. The spacious lower level features a sunken living room with a convertible queen sofa sleeper, Smart TV, surround sound, and an expansive lanai perfect for relaxing outdoors.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Wailea
Discover the perfect Maui getaway at Honua Kai Konea 143, a spacious ground-floor condo nestled in the highly sought-after interior courtyard of the Konea Tower. Spanning 1,315 square feet, this two-bedroom suite offers a luxurious and comfortable retreat with easy access to the pools, beach, and BBQ areas, making it ideal for families or groups seeking convenience and privacy.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to Pu'u Poa 305, a beautifully appointed two-bedroom condo offering breathtaking, unobstructed ocean vistas. Wake up each morning sipping coffee on the deck while watching surfers ride the waves at the point near the St. Regis Hotel. During winter and spring, spot whales splashing and dolphins leaping right from your balcony, and enjoy spectacular sunsets with Bali Hai in the background.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Princeville
Located on Maui's renowned Kaanapali North Beach, this luxury two-bedroom, one-bath villa at Honua Kai Resort & Spa offers a perfect blend of comfort and stunning views. Situated on the 6th floor of the Konea Tower, the 950 sq ft interior opens onto a spacious 200 sq ft lanai, providing breathtaking vistas of the West Maui mountains and the Pacific Ocean.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the beauty of Maui from this beautifully renovated oceanfront two-bedroom, two-bath condo at Royal Mauian Resort in Kihei. Situated between Kamaole I and II beaches, the condo offers spectacular whitewater ocean views right from your private lanai, perfect for soaking in Hawaii's natural splendor.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Located in the desirable Ka Milo neighborhood at Mauna Lani Resort, this luxurious two-story home offers four bedrooms and three and a half bathrooms, comfortably accommodating up to ten adults for an unforgettable Hawaiian vacation.
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Waimea
Welcome to Hale Maluhia La�a', a serene and spacious Hawaiian retreat nestled in lush tropical surroundings just a short two-minute walk from the famous Kealakekua Bay. This beautifully maintained 4-bedroom, 4-bathroom home offers a perfect blend of comfort and natural beauty, making it ideal for families, friends, or groups seeking a peaceful escape on the Big Island.
4 bedroom
2 bathroom
Sleeps 10
Hawaii> Capt Cook
Welcome to our Maui Dreamin’ — Mana Kai #304! This beautifully updated 2-bedroom, 2-bathroom oceanfront condo offers breathtaking views of the blue-green ocean and pristine Keawakapu Beach. Situated at the tip of Keawakapu, between South Kihei and Wailea, the location is ideal for exploring Maui’s best beaches, hiking trails, surfing spots, snorkeling, scuba diving, dining, and shopping.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Kihei
Experience the ultimate Maui vacation in Papakea G204, a charming 1-bedroom, 1-bath condo on the second floor, accommodating up to 4 guests. The resort features elevators for easy access and lush tropical landscaping, including koi lagoon ponds, oceanfront saltwater pools, hot tubs, shuffleboards, golf greens, pickleball, and tennis courts, all just north of the famous Kaanapali Beach.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover Aina Lani, a cozy and beautifully furnished one-bedroom retreat nestled in the sunny and dry side of Waimea Town on the Big Island. This peaceful home is perfect for exploring the island's diverse attractions and relaxing in a tranquil setting.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Waimea
Welcome to Hale Kahakai Punahele, a delightful Kauai cottage situated right across from the renowned Brennecke Beach and just steps from the sunny sands of Poipu Beach. This charming island-style home offers unparalleled oceanfront views over a quiet lane, providing a serene and picturesque setting for your Kauai getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Koloa
" Nestled just 10-15 minutes north of Lahaina near Kanapali and Kapalua, this location offers easy access to all West Maui attractions including golf, tennis, snorkeling, beach walks, and shopping. Remodeled in 2020, this condo features modern touches and a private lanai perfect for enjoying spectacular sunsets and whale watching from December 15 to April 15.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Village Manor Apartment #E21 in Waipouli, Kapaa offers a cozy, bright, and airy second-floor retreat perfect for your Kauai getaway. 5-bath end unit comfortably accommodates up to four guests, including children and infants. Nestled in a central location on Kauai's eastern shore, it provides easy walking access to shops, grocery stores, beaches, and restaurants, making it an ideal base for exploring the island.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Anchorage Point provides a unique blend of contemporary luxury and natural beauty in a private beachfront setting on Kauai's North Shore. This stunning 3-bedroom, 3-bath home offers unobstructed panoramic views of the Pacific Ocean from both the living spaces and the master suites, creating an ideal retreat for families and nature lovers alike.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Hanalei, Haena
This beautifully remodeled 2-bedroom, 2-bathroom condo offers a perfect blend of comfort and tranquility on the stunning Kona coast. The master bedroom features a king-sized bed and can be separated from the living area with elegant Shoji screens, providing privacy and flexibility.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Escape to this beautiful condo at Hanalei Bay Resort, perched at the highest point with panoramic views of Mt. Namolokama, waterfalls, Hanalei Bay, lava rock pools, and hot tubs. The spacious oversized lanai features a lounge chair, table, and two chairs, perfect for relaxing while soaking in the scenery.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
Experience the perfect getaway in this beautifully appointed Royal Garden Studio managed by Ali'i Beach Rentals. Located in a peaceful area yet within walking distance to Waikiki’s top attractions, this end-unit condo offers spectacular ocean views and a prime location. Registration Number: 260160390159, TA-005-508-9152-01, GE-005-508-9152-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Welcome to our beautifully remodeled ground level luxury condominium, offering 1090 square feet of comfort and style. This 1-bedroom, 2-bathroom unit is situated directly on Ekahi's lush green belt in the lower village, just steps from the stunning beach and four resort pools.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Experience the perfect Maui getaway in Konea 407, a stunning oversized 2-bedroom ocean view villa spanning 1315 sq ft. Located on the 4th floor, this premium unit offers breathtaking views of the Pacific Ocean and resort pools. The villa features three king beds, ideal for groups of up to eight guests, making it perfect for families or friends gathering for a celebration or relaxing retreat.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Discover this beautifully remodeled condo #108 at Kihei Resort, nestled in lush Hawaiian gardens with a tranquil koi pond view from your private lanai. The unit offers a spacious one-bedroom layout with thoughtful updates including a brand-new kitchen, new laminate floors, fresh paint, and stylish furniture.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Experience the ultimate Maui getaway in Hokulani 736, a luxurious 7th-floor corner suite offering breathtaking views of both the Pacific Ocean and West Maui Mountains. Spanning 1240 sq. , this spacious two-bedroom residence comfortably accommodates up to six guests with two king-sized beds and a queen-sized convertible sofa.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Discover this beautifully remodeled tropical golf villa situated on the sunny Kohala Coast, nestled between two renowned golf courses. This spacious 2000 sq ft villa offers two large master suites, each with soaking tubs and separate showers, a fully equipped kitchen, a comfortable living and dining area, and a private wrap-around lanai with a wet bar.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa
This luxurious 3-bedroom oceanfront residence offers breathtaking views of the Pacific Ocean and the West Maui Mountains from its rare corner location. The expansive wrap-around lanai is perfect for entertaining, featuring an outdoor kitchen and Viking gas grill.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the ultimate Hawaiian retreat in this spacious 1,600 sq ft two-bedroom, two-and-a-half-bath townhome located on the western edge of Mauna Lani Fairways within the prestigious Mauna Lani Resort. This upscale property offers a peaceful setting with breathtaking sunset views and exceptional privacy, making it ideal for romantic getaways, family vacations, or relaxing escapes.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
In the heart of paradise, Unit D5 at Ma'alaea Surf Resort offers a beautifully refurbished retreat just five steps from Maui’s soft white sands. This ground-floor unit combines comfort with Hawaiian country charm, creating a relaxing haven for couples seeking a memorable getaway.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
KBM Resorts: Kapalua Golf Villa KGV-24P7, Beautiful Remodeled Spacious 2 Bedroom with Ocean + Golf Views, L'Occitane & Beach Amenities
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
5-bath home is situated on Kauai's North Shore, just a short walk from Kephui Beach. The property offers a perfect blend of comfort, natural beauty, and convenient access to outdoor adventures.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Hanalei
This beautifully remodeled oceanfront condo in the heart of Kailua Kona offers an unforgettable Hawaiian getaway. The fully furnished one-bedroom features a king-size bed and a fold-out Tempurpedic queen in the living room, perfect for relaxing after a day of adventure.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience the perfect blend of beauty, comfort, and value in this spacious, renovated one-bedroom condo in sunny Poipu. The unit offers stunning ocean and mountain vistas from the lanai, which overlooks Prince Kuhio Park—a peaceful, lush open space meeting the sea.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Poipu
Experience the ultimate Maui escape in this luxurious one-bedroom, one-bath condo located at Sugar Beach Resort. Enjoy breathtaking ocean vistas and stunning sunsets right from your private lanai, where you can watch the Kihei Canoe Club glide by and spot playful humpback whales during the season.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this beautifully remodeled oceanfront one-bedroom unit, featuring a new kitchen and bathroom with a tiled stand-up shower.Registration Number: TA-121-644-2368-02, 1260100020059, GE-121-644-2368-02
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Nestled between lush mountains and the sparkling sea on Kauai's spectacular north shore, this family-friendly, air-conditioned condo offers a perfect blend of comfort and Polynesian charm. The spacious, tastefully decorated townhouse features bright, tropical decor and is an end unit with a garden view, providing a sense of privacy and tranquility.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Experience the perfect Maui getaway with this tandem package of two neighboring two-bedroom suites, offering a combined 2495 sq ft of luxurious living space and breathtaking ocean views. These spacious suites are ideal for families or groups up to 12 guests, featuring four king-sized beds and two queen-sized convertible sofa sleepers.
5 bedroom
4 bathroom
Sleeps 12
Hawaii> Lahaina
Discover this stunning, fully remodeled 1-bedroom, 2-bath condo at Kamaole Sands, offering 818 sq ft of comfort and style. Located in Building #2, Unit 105, the condo boasts beautiful ocean and garden views from the large shaded lanai, perfect for relaxing or dining outdoors.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the perfect Hawaiian getaway in this spacious 3-bedroom apartment located in peaceful Waikiki. Over 1000 sq ft of comfortable living space with stunning oceanfront verandahs offers breathtaking views of surfers, sunsets, and Friday night fireworks.Registration Number: TA-198-110-8224-01, GE-198-110-8224-01, 260240240081
4 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Aloha from Waikiki! Discover the vibrant heart of Honolulu at the Waikiki Royal Garden Studio, a cozy 352 sqft retreat perfect for exploring the island. , this rental offers unparalleled access to Waikiki's top attractions, shopping, and beaches.Registration Number: GE-044-312-3200-01, TA-044-312-3200-01, 260160390123
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Discover this breathtaking oceanfront 1-bedroom condo in Honokowai, offering spectacular views of the Pacific Ocean. Perfectly situated near iconic Kaanapali Beach and the tranquil Napili Bay, this retreat provides a serene escape amidst natural beauty.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Our Kailana home offers a recently renovated, stylish tropical retreat in the charming beachside neighborhood of Kailua, Oahu. It’s just a short walk to the pristine beach, making it perfect for relaxing days by the water. The property is spacious, private, and peaceful, with lush tropical plants surrounding the yard, creating a serene oasis.Registration Number: 430260120000, GE-012-164-3008-01, TA-012-164-3008-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua
This beautifully managed vacation condo at Mauna Lani offers a perfect Hawaiian retreat. Featuring a spacious layout with a king bed in the main bedroom, a queen in the guest bedroom, and two twin beds in the third bedroom, it comfortably accommodates families or groups.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Experience resort-style living at Palm Villas I-23, a beautifully furnished golf villa nestled within the exclusive Mauna Lani Resort. This elegant two-level home offers 1,460 sq ft of luxurious space, perfect for your Big Island getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
Discover this beautiful, spacious studio nestled in the lush Hana rainforest. It features a comfortable queen bed, a fully equipped kitchen with fridge, stove, oven, microwave, and Keurig coffee maker, along with a private full bathroom. Relax on the wrap-around covered deck, enjoying the sights of towering trees and the soothing sounds of the ocean and native birds.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
