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 15500, 50
This premium one-bedroom deluxe ocean view villa offers 750 square feet of luxurious space with a private lanai overlooking the stunning ocean. Designed to comfortably sleep four, it features a king-sized bed in the master bedroom, a queen sofa bed in the living area, and a lock-off studio with a queen bed, sofa bed, whirlpool tub, and mini-kitchen, making it ideal for families or extended stays.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Kanai A Nalu 209 is a newly renovated 2-bedroom, 2-bath ocean view condo located in the Kanai A Nalu resort in Maalaea, Maui. This second-floor unit offers stunning south-facing views overlooking the tropical courtyard, pool, and the Pacific Ocean .
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailuku
The Maui Sunset Resort, with its immaculate grounds and tropical landscaping, is a family-friendly, oceanfront property offering breathtaking ocean and garden views. It has easy access to the sand beach where you can watch the amazing sunsets.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Discover the ultimate luxury experience at Hale 'Oli 'Oli, a stunning oceanfront villa situated just steps from the pristine sands of Kailua Beach. This exquisite property offers a seamless open-concept layout, a chef�s dream gourmet kitchen, and six spacious bedrooms designed for comfort and style. Registration Number: TA-048-077-3632-01, GE-048-077-3632-01, 430190500000
6 bedroom
6 bathroom
Sleeps 12
Hawaii> Kailua
Discover your ideal Hawaiian escape at Kaanapali Alii 351, a stunning one-bedroom plus den condo offering breathtaking ocean vistas. This spacious residence is perfect for couples or small families seeking both comfort and convenience. The open floor plan seamlessly connects the living and dining areas, both beautifully decorated and featuring picturesque views of Kaanapali Beach and the Pacific Ocean.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the ultimate beachfront getaway at Westin Kaanapali Ocean Resort Villas, offering spacious suites with full kitchens, cozy living areas, and luxurious amenities. Each villa features a king-sized Westin Heavenly® Bed and a Westin Heavenly® Bath with a separate whirlpool tub, ensuring restful nights after days spent lounging by the pool or exploring Maui's stunning beaches.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover this beautifully upgraded ground floor condo blending modern designer touches with an island vibe. The spacious living area with high ceilings flows seamlessly into a fully stocked chef's kitchen and opens to a lanai (balcony) with expansive seating, offering breathtaking views of the golf course, lake, and gorgeous sunsets each night.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience ultimate relaxation in this luxurious 214 sq ft ocean view King room, featuring a private balcony, plush king bed, coffee maker, mini fridge, and microwave. The hotel offers stunning sunrise and sunset vistas, with the soothing sound of waves providing a serene backdrop. Registration Number: TA-043-621-7856-01, GE-043-621-7856-01, 440080030000
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
This beautiful gated beachfront property in Kona offers the perfect blend of comfort and convenience. Located right in downtown Kona, it’s within walking distance to shops, restaurants, the farmers market, the pier, and Alii Drive. The house features three bedrooms: a master with a king bed and private full bathroom, a second bedroom with a queen bed and private full bathroom, and a third bedroom with a queen bed.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua-Kona
Experience the comfort and convenience of this charming boutique hotel in Waikiki. Each 300 sqft unit features two double beds, a mini-fridge, microwave, wet bar, and daily housekeeping, making it ideal for families and travelers seeking a cozy stay. Registration Number: 260170280065, TA-030-278-8608-01, GE-030-278-8608-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience a comfortable stay in this 9th-floor Hawaii condo offering a spacious balcony with scenic views. The condo features vinyl plank flooring in the living area and tile in the kitchen and bathroom for easy maintenance. The updated kitchen is equipped with granite countertops, a refrigerator, stove/oven, microwave, cookware, and tableware, making meal prep a breeze.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Experience a modern, spacious 1-bedroom condo in the heart of Waikiki, perfectly suited for couples or small groups. Recently renovated, the unit boasts a full kitchen, a relaxing lanai with stunning views of the Ala Wai Canal and the mountains, and a private entrance ensuring privacy and ease of access.Registration Number: 260240730095, GE-083-404-5440-01, TA-083-404-5440-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This tandem 3BR/3BA pairing features two stunning 6th-floor oceanview suites at Honua Kai, situated right next to each other for convenience and privacy. Each suite offers breathtaking views of the lush interior courtyard, pools, and the expansive Pacific Ocean, making it ideal for families or groups seeking a luxurious Maui getaway.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Lahaina
Experience the beauty of Maui in this beautifully remodeled second-floor condo at Wailea Ekahi Village. With stunning ocean views and breathtaking Haleakala sunrises visible from the kitchen window, this property offers a perfect blend of comfort and luxury.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Wailea
5-bathroom ocean view residence situated along the iconic 15th hole of the Mauna Lani South Course. This residence offers panoramic vistas of the Pacific Ocean, the lush fairways, and the black sands of Honokaope Bay, all visible from the expansive, comfortably furnished lanai.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Kamuela
Welcome to Kaanapali Alii 233, a charming two-bedroom, two-bath condo that invites you to experience the best of Hawaiian living. Situated on the lower floor, this residence offers close-up views of the sparkling pool and lush tropical gardens, extending out to the ocean beyond, creating a serene backdrop for your getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Consider this charming condo your home away from home while you visit Kihei. This superb 1-bedroom unit features a cozy living room with a sofa bed, ideal for accommodating extra guests. Enjoy modern amenities including air conditioning, WiFi, a washing machine, and a dryer, ensuring your stay is both convenient and comfortable.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Escape to this quiet, beautifully remodeled 1-bedroom, 2-bath Maui condo covering 900 sq ft at Maui Kamaole in South Kihei. The spacious unit features stunning ocean and mountain vistas from the large front lanai, living room, and kitchen, providing a perfect tropical retreat.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience the beauty of Maui from this top-floor, two-story condo at Kahana Villas, offering panoramic ocean views from the private lanai where you can relax with your morning coffee or unwind with stunning sunsets. The open-concept living area is bright and welcoming, featuring a fully equipped kitchen with stainless steel appliances, a wine fridge, and modern amenities that make cooking and entertaining effortless.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the perfect Maui getaway at the Westin Kaanapali Ocean Resort Villas. This spacious 2-bedroom lockoff unit combines a studio and one-bedroom suite, accommodating up to 8 guests comfortably. Located just steps from the pristine Kaanapali Beach, the property offers luxurious accommodations with all the amenities needed for a relaxing vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Reconnect with nature at this unforgettable Maui escape. Kipahulu Campground within Haleakala National Park offers stunning views and outdoor adventures. Tony’s Place provides a private spot in Hana town, just a short walk to Hamoa Beach, perfect for relaxing and exploring.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Paia
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
Beautifully Renovated Beach Resort Condo on Kaanapali Beach - Managed by Hawaii Life Vacation Rentals
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Lahaina
Experience a comfortable stay in this 7th-floor condo in Hawaii offering a private balcony and stunning views. The updated kitchen features granite countertops and stainless steel appliances, including a stove, oven, refrigerator, microwave, coffee maker, toaster, blender, and tea kettle, with a complimentary tea starter kit for your convenience.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Aloha and welcome to our stunning vacation home nestled in the heart of Princeville, Kauai! This luxurious two-story condo features two bedrooms and an optional third bedroom with two twin beds, offering a perfect blend of comfort, style, and island charm.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Princeville
Experience the vibrant heart of Waikiki in this beautifully decorated 1-bedroom condo located on the 29th floor of Waikiki Banyan Tower 2. The unit comfortably sleeps four with two full-size beds and offers a spacious living area separated by sliding doors for privacy. Registration Number: TA-050-011-4432-01, GE-050-011-4432-01, 260250050747
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
The Oceanfront 1 King Bed suite offers 425 square feet of comfort with stunning ocean views from your private lanai. Inside, you'll find a cozy sitting area, a dining space, and modern amenities including a flat-screen TV, high-speed internet, a coffee maker, refrigerator, and electric kettle for convenience.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Lahaina
Looking for a relaxing getaway in Waikiki? This cozy studio on the 12th floor offers a king bed, small couch, and a fully equipped kitchen, all just a block from Waikiki Beach. The prime location puts you within walking distance of top attractions like Ala Wai Golf Course, vibrant eateries such as Musubi Cafe Iyasume and Maguro Bros, and shopping at the International Market Place. Registration Number: 260230560055, GE-156-169-1648-01, TA-156-169-1648-01
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Discover the perfect beachfront retreat in Waikiki with this spacious hotel room package. Each 360sf unit features a comfortable king bed, club lounge access, and daily housekeeping, ensuring a relaxing stay. Located just minutes from popular attractions, the property offers stunning ocean views and a variety of on-site amenities including a swimming pool, hot tubs, spa services, and multiple restaurants. Registration Number: TA-023-408-4352-01, GE-023-408-4352-01, 23408435
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Honolulu
Welcome to Hale Alapi'i Lanikai Getaway by Gather, a beautifully renovated four-bedroom, three-bathroom home nestled in the heart of Kailua's iconic Lanikai neighborhood. This serene property offers a perfect blend of island charm and modern comfort, making it an ideal retreat for families or groups seeking to immerse themselves in Hawaii's natural beauty.Registration Number: 430030350000, GE-150-218-4960-01, TA-150-218-4960-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua
Escape to the stunning Big Island in this beautifully remodeled 1-bedroom condo at Keauhou Punahele. Perfectly situated next to the scenic Kona Country Club, this charming retreat offers a seamless blend of modern comforts and island charm.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Welcome to your dream Maui escape at Kaanapali Alii, where luxury meets oceanfront living. This beautifully appointed two-bedroom condo offers sweeping views of the Pacific Ocean and direct access to the pristine sands of Kaanapali Beach. Step inside to discover a spacious, thoughtfully decorated living area designed for comfort and relaxation.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Valley Isle 301, a beautiful 1-bedroom, 1-bath condo with stunning ocean views, located just steps from the beach between Kaanapali and Kapalua. This spacious third-floor unit comfortably sleeps up to four guests and offers the perfect blend of island charm and modern comfort.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience a comfortable stay in this beautifully renovated third-floor condo located on the south side of the building, offering a serene garden setting from your private lanai. The condo comfortably accommodates up to six guests, including children and infants, making it an ideal choice for families or small groups.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua Kona
Experience a relaxing getaway in this beautifully decorated second-floor oceanfront condo at Poipu Beach. The tropical beach decor and original artwork by Coco create a welcoming atmosphere. The condo features custom tiled floors throughout and a fully equipped kitchen with upgraded stainless steel appliances, granite countertops, and all necessary cookware and tableware.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience the best of Kauai from this beautifully upgraded private retreat in Princeville. Located just minutes from Hanalei Bay and some of the island's most iconic surf spots, this spacious 2-bedroom home offers a perfect blend of comfort and convenience.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Welcome to the charming Aloha Hale studio condo, a bright and spacious corner unit located in the desirable Kihei Bay Surf complex on Maui. This well-appointed condo offers more space than typical studios, featuring additional windows that flood the interior with natural light and provide lovely views of the surrounding area.
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Experience the perfect Hawaiian escape in this beautifully designed 600sf suite nestled in Maui. The space combines modern comfort with tropical charm, offering stunning partial ocean views from a private lanai. Inside, you'll find a luxurious bedroom with a king bed and a versatile sofa bed, providing ample space for relaxation and family fun.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your own Waikiki Beach 1-bedroom oceanview vacation condo rental, just steps from the sandy shores of Waikiki. This beachfront property offers a spacious lanai framed by lush coconut palms, providing a tropical oasis to relax and unwind. Registration Number: 0488766688, TA-467-482-8878-01, GE-467-482-8878-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to our luxurious Kapalua villas nestled in lush gardens beside pristine beaches. These fully-equipped 2-bedroom villas offer stunning ocean views from your private lanai, perfect for unwinding and enjoying breathtaking sunsets. With 1750 sq ft of space, each villa features a king bed in the master, two twin beds in the second bedroom, a living area with a queen sofabed, and a fully equipped kitchen complete with stovetop, microwave, refrigerator, and coffee maker.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the ultimate Maui getaway in this spectacular beachfront condo located directly on Charlie Young Beach in Kihei. Nestled in one of the most sought-after locations, this peaceful retreat offers breathtaking ocean, sunset, and island views from every room.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kihei
Experience a memorable stay at Waikiki Royal Garden with two comfortable studio suites, each offering a king bed, partial kitchen, and private balcony. These suites comfortably accommodate two guests and feature amenities like a hairdryer, in-room safe, broadband internet, DVD player, stereo, television, and Wi-Fi access, ensuring a convenient and relaxing stay.Registration Number: GE-068-663-8592-01, 260160390000, TA-068-663-8592-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
Experience the ultimate Maui getaway in Wailea Beach Villas Penthouse 403, a luxurious oceanfront retreat offering breathtaking views and spacious comfort. Spanning 2,000 square feet, this beautifully appointed penthouse features three bedrooms and three full bathrooms, making it perfect for families or groups seeking an upscale vacation experience.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kihei
Discover the perfect tropical escape at Kaanapali Alii 462, a luxurious one-bedroom ocean view condo on Maui's stunning coastline. This beautifully appointed residence blends comfort and elegance, offering an ideal setting for your Hawaiian vacation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Royal Kahili 401A by Gather, an exquisite oceanfront retreat in Kailua-Kona. This top-floor corner suite offers breathtaking panoramic views of the Pacific Ocean from its private lanai and spacious living areas. Designed with comfort and style in mind, the condo features a modern open floor plan filled with natural light.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Welcome to your perfect Maui escape! This stunning oceanfront condo offers breathtaking views and a private lanai where you can enjoy your morning coffee while soaking in the sunrise. Located just steps from a beautiful sandy beach, it provides easy access to Maui’s turquoise waters and tranquil surroundings.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Escape to the tropical paradise of Maui with a stay in the Studio Premium Island View Villa at Westin Ka'anapali Ocean Resort Villas North�a perfect blend of luxury, relaxation, and family-friendly fun. Surrounded by lush island landscapes, this retreat is ideal for creating unforgettable vacation memories.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Sunset Shores, your oceanfront haven in Waialua on Oahu's scenic North Shore. This spacious one-bedroom, two-bath condo offers 860 square feet of comfortable living space, ideal for a relaxing getaway or a remote work retreat. Inside, the unit comfortably sleeps four guests with a luxurious king bed in the bedroom and a queen-sized Murphy bed in the living room. Registration Number: 1680110460023, TA-031-084-6976-01, GE-031-084-6976-01
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Waialua
Experience the beauty of Kauai's North Shore from this tastefully furnished one-bedroom apartment, perfectly located near beaches, golf courses, shops, and trails. The upstairs condo offers a peaceful retreat with stunning garden and mountain views, a private deck, and modern amenities.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Princeville
