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 3400, 50
Discover Aloha Valley Shores, a charming and light-filled island retreat ideal for long stays. Surrounded by lush tropical plants, this cheerful home offers a relaxing escape close to local attractions and scenic spots.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Pahoa
Experience the ultimate Maui getaway in this deluxe three-bedroom oceanview residence located on the 5th floor, offering breathtaking views of the Pacific Ocean and nearby Lanai island. The spacious layout comfortably accommodates up to eight guests with three king-sized beds (one convertible into XL twins) and a queen sofa sleeper.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Lahaina
This stunning and fully renovated cottage is nestled on a private, gated one-acre estate right on the shores of Hanalei Bay. As one of the few beachfront rental cottages in central Hanalei, it offers an unparalleled experience of tropical paradise.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hanalei
Paki Maui 414 offers a charming and comfortable stay on Maui's scenic west coast. This 1-bedroom, 1-bathroom condo is situated on the fourth floor, providing stunning garden views from your private lanai and large floor-to-ceiling sliding glass doors that fill the space with natural light.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to Kaimana Breeze by Gather, a sophisticated beachfront studio located on Honolulu's prestigious Gold Coast. Perched on the 14th floor, this elegant condo offers breathtaking panoramic ocean views, making it an ideal retreat for relaxation and exploration. Registration Number: 310320100111, TA-057-714-2784-01, GE-057-714-2784-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Sunset Hale is a luxurious tropical retreat perched above downtown Kona, offering breathtaking views of Kamakahou Bay. This stunning residence features contemporary design, chic decor, and expansive outdoor living spaces including a 900 sq. ft.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua Kona
Welcome to Hale Mauna & Moana, a stunning 3-story oceanfront home nestled on the Kohala Coast of the Big Island. This luxurious residence offers over 3,600 square feet of elegant living space, perfect for families or groups seeking a private, tranquil retreat just steps from the beach.
5 bedroom
4 bathroom
Sleeps 17
Hawaii> Waimea
Experience the beauty of Wailea from this 1400 square foot condo situated on the second floor of a premier resort area. Enjoy breathtaking views of the second green of the Wailea Blue Golf Course from the living room and master bedroom lanais. The condo is fully furnished with all amenities needed for a comfortable stay, including modern appliances, stylish decor, and thoughtful touches to ensure a relaxing vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
A luxurious, fully renovated condo in Waikoloa Beach Resort offers over 1400 sq. ft. of tropical elegance. Located just an 8-minute walk to a pristine white sand beach, this second-floor unit provides breathtaking ocean and garden views from its large wrap-around lanai.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa
Aloha! Welcome to your Maui paradise at Bougainvillea-109, a spacious 1-bedroom, 2-bath corner unit offering 1039 sq ft of comfort and style. This condo is one of the largest on the property, featuring a large main lanai of 250 sq ft plus a private grass area perfect for relaxing or outdoor dining.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Immerse yourself in the lush beauty of Maui from this charming single-level condo located at Kamaole Sands in Kihei. This well-appointed one-bedroom, two-bathroom unit offers a perfect blend of comfort, convenience, and tropical scenery. The condo features a beautiful lanai where you can unwind while enjoying garden views and glimpses of the ocean, creating an ideal setting for morning coffee or evening relaxation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
The top-floor (6th floor) corner unit at Koa Lagoon offers an unbeatable beachfront location with panoramic views of the stunning Pacific Ocean. Recently upgraded with an open floor plan, this spacious condo features two bedrooms and two bathrooms, making it perfect for families or couples seeking a relaxing retreat.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> KIHEI
Discover this stunning oceanfront condo perched on the cliffs of North Shore, offering spectacular views from every room. The spacious 2-bedroom, 2-bath layout features large windows that frame the crashing waves below, creating a serene and captivating atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Princeville
Experience the ultimate Maui getaway in this beautifully furnished oceanfront condo at Makena Surf. Sitting directly at the water's edge, this residence offers breathtaking views from nearly every room, creating an immersive ocean experience. The spacious 1,420 sq ft layout has been thoughtfully redesigned to maximize its prime corner location, featuring an open-concept living, dining, and kitchen area with floor-to-ceiling sliding glass doors that recess into the walls, seamlessly blending indoor comfort with outdoor beauty.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea-Makena
Welcome to Kaanapali Alii 241, a beautifully appointed fourth-floor ocean view condo located on the world-famous Ka'anapali Beach. This spacious residence offers breathtaking views of the Pacific Ocean and provides a perfect retreat for families, friends, or couples seeking luxury and relaxation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Embrace the laid-back island lifestyle in this beautifully located ground-floor condo at Maui Kamaole D106. Featuring a spacious open-concept design, the unit offers a bedroom with a plush King bed and an en-suite bathroom, along with a sleeper sofa to accommodate up to four guests comfortably.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kihei
Experience a tranquil escape on a private island with breathtaking jungle vistas. Located in an upscale Hilo neighborhood, this guest room offers complete privacy with its own entrance and bathroom. Relax in a cozy, well-appointed space just minutes from downtown Hilo, where you can explore local restaurants, vibrant farmers markets, and cultural attractions.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hilo
Enjoy a relaxing stay at our top-floor one-bedroom condo (#308) at Islander on the Beach, offering spectacular ocean views from a spacious lanai. This unique unit is one of only two true one-bedroom options in the resort, providing a private bedroom with a luxurious California King bed and a living area featuring a sofa-sleeper for extra guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapa?a
Experience the ultimate Maui vacation in this stunning beachfront estate located directly across from one of the island's most beautiful sandy beaches. Perfect for families and large groups, this property combines luxury, convenience, and top-notch amenities in a private gated setting.
6 bedroom
5 bathroom
Sleeps 20
Hawaii> Lahaina
Breathtaking, sensational, divine — Hale Nani offers an exceptional luxury experience in Kukui'ula. This newly completed 2022 villa features a spacious, elegantly appointed design with a distinctive �podular� layout that creates a private, intimate compound atmosphere, yet remains just minutes from the vibrant South Shore attractions.
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Koloa
Escape to the luxurious Ocean Villa at Turtle Bay, a perfect Hawaiian retreat on Oahu's North Shore. This spacious 3-bedroom, 2-bath villa comfortably accommodates up to 8 guests, making it ideal for families or friends seeking a tranquil beachside getaway.Registration Number: TA-208-597-4016-01, GE-208-597-4016-01, 570010130014
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kahuku
Experience the perfect blend of luxury and natural beauty at Hanalei Bay Villa 26, a 10/10 Traveler Award winner for 2024. This beautifully updated 2-bedroom, 2-bathroom home offers breathtaking ocean and mountain views, located just steps from Bail Hai and Hideaways Beach.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Step into this stunning newly renovated 1536 sq ft beachfront condo in Wailea, Maui, offering breathtaking ocean and Haleakala mountain views. The airy, modern interior features high-end mahogany furnishings and a fully equipped kitchen for your culinary needs.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Wailea
Alii Villas 303 – Top Floor Remodeled Retreat with Ocean Views
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua Kona
Experience the perfect Maui retreat in this beautifully remodeled condo at Kamaole Nalu, offering breathtaking ocean views from the private lanai, kitchen, and master bedroom. The unit features a spacious layout with two bedrooms, each with a luxurious king bed and private bathroom, including walk-in showers, Toto toilets, and custom vanities.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kihei
Welcome to our exceptional oceanfront property in Kona, located in one of the area's most exclusive neighborhoods off Kailua-Kona�s scenic Ali�i Drive. This luxurious single-level home offers breathtaking views of Holualoa Bay and spectacular ocean sunsets, seamlessly blending sophisticated architecture with the natural beauty of Hawaii.
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua Kona
Welcome to Sugar Beach Resort #329, an exquisite renovated beachfront condo nestled in North Kihei. This stunning property offers breathtaking views of the Pacific Ocean and easy access to pristine beaches, making it an ideal retreat for travelers seeking relaxation and adventure.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Kihei
Konea 416 offers a luxurious one-bedroom suite with spectacular views of the West Maui Mountains, perfect for experiencing Maui's renowned sunrises and rainbows. The suite features expansive floor-to-ceiling glass doors that open to a spacious lanai, blending indoor comfort with outdoor beauty.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the stunning ocean view studio at Westin Kaanapali Ocean Resort, perfectly situated on the 5th floor among the highest-rated beaches in the world. This 7569-unit offers a comfortable, fully furnished space ideal for a relaxing getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your Hawaiian paradise at The Islands at Mauna Lani. Nestled within a gated community along the picturesque fairways of the Mauna Lani golf course, this luxurious two-story townhome offers an ideal blend of comfort and resort style living.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kamuela
Discover our beautiful Hawaii home located in The Villages, a private gated community close to shops and amenities. This spacious 2,200 sq ft residence offers privacy, comfort, and stunning mountain views. Recently updated with new beds, quartzite kitchen countertops, expanded island, and modern furnishings including a new chaise sectional and recovered dining chairs with matching chandelier.
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Waimea
Discover this beautifully remodeled 2-bedroom, 2-bath condo in Princeville, perfect for your Kauai getaway. The unit features spacious living areas with modern updates and offers stunning mountain views, along with a peek at the ocean from the dining area, allowing you to catch the swell while dining.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Princeville
Discover the breathtaking beauty of Maui from Mahana Condo 301, a stunning oceanfront 2-bedroom, 2-bathroom suite located in the heart of Kaanapali's renowned North Beach. This luxurious third-floor condo offers sweeping panoramic views of the Pacific Ocean, with islands Lanai and Molokai on the horizon.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kaanapali
Experience unparalleled luxury at Wailea Beach Villas Penthouse 502, a stunning top-floor residence boasting sweeping ocean views and elegant decor. The private elevator landing opens to a spacious living area with floor-to-ceiling windows and sliding glass doors that frame the vibrant blue Pacific Ocean, creating a seamless indoor-outdoor living experience.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Wailea
Discover this charming Moroccan-inspired apartment designed by Twin Islands Interior Design Group, located on the lush slopes of Big Island. This spacious 1-bedroom, 1-bathroom retreat offers a perfect blend of comfort and exotic charm, making it an ideal getaway for couples, solo travelers, or small groups seeking tranquility and natural beauty.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kealakekua
Welcome to Sparkling Waters, an exquisite single-level luxury home nestled in the heart of Hawaii's Big Island. Brought to you by Paradise in Hawaii Luxury Homes, this stunning residence is part of our exclusive Hawaiian Luxury Collection, designed to offer the perfect blend of comfort, elegance, and fun for your Hawaiian getaway.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Kohala Coast
Experience the ultimate Kauai getaway at Puʻu Poʻa 313, a luxurious condo offering breathtaking ocean vistas from nearly every room. Perched closer to the cliff’s edge, the unit provides spectacular views of crashing waves, reefs below, and vibrant water shades.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Princeville
Escape to our stunning Coconut Villa located within the gated Coconut Plantation community in Ko Olina, Hawaii. 5-bathroom home offers a perfect blend of comfort, convenience, and scenic beauty. Step out onto the second-floor lanai for morning coffee or evening cocktails while soaking in the tranquil views of the lush golf course and mesmerizing sunsets. Registration Number: 910560040247, TA-018-887-0400-01, GE-108-887-0400-02
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Experience the perfect blend of comfort and natural beauty at Kuhio Shores 217, a stunning oceanfront condo on Kauai's sunny south shore. Recently upgraded with a new mini split air conditioning system, this one-bedroom unit offers a cool retreat after a day of exploring the island's lush landscapes and pristine beaches.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Koloa
Live like a local in this professionally designed two-bedroom, two-bath Hawaiian retreat that combines luxury with authentic island charm. Nestled in a tranquil area known for its stunning beaches, this home offers the perfect escape from city life.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Haena
Experience the beauty of Hawaii in this spacious three-bedroom Lako House, offering stunning panoramic ocean views from a single-level layout. The property spans 3056 sq ft and is fully compliant with Hawaii County HB 108 regulations, ensuring a smooth and hassle-free stay.
3 bedroom
5 bathroom
Sleeps 16
Hawaii> Kailua-Kona
Welcome to our spacious one-bedroom, one-bathroom condo nestled along the coast in the luxurious Kapalua Bay Villas. This oceanfront location offers breathtaking views of the Pacific, inviting you to immerse yourself in the beauty of West Maui.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Discover breathtaking ocean and West Maui Mountain views from this luxurious 11th-floor condominium, recently renovated for comfort and style. This family-owned 2-bedroom vacation home offers privacy within one of Hawaii's most sought-after upscale resort locations in Kaanapali.
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Lahaina
Welcome to Ko Olina Beach Villas B301 by Gather, a stunning three-bedroom, three-bathroom beachfront retreat perfect for up to eight guests. Situated on the third floor, this luxurious condo offers breathtaking views of the ocean and resort pool, seamlessly blending comfort and elegance with Hawaii’s natural beauty.Registration Number: TA-045-641-1136-01, 910570090180, GE-045-641-1136-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
This beautifully appointed two-bedroom, two-bath residence offers sweeping ocean vistas and a warm contemporary design, perfect for a relaxing Maui getaway. The spacious layout comfortably hosts up to six guests, making it ideal for families or groups.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the best of island living in this beautifully appointed 2-bedroom, 2-bath condo located directly across from White Sands Beach, also known as Magic Sands Beach, renowned for its dynamic shoreline. This second-floor unit offers stunning mountain and beach views from the covered lanai, making it an ideal spot for relaxation or outdoor dining.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Relax in this beautifully renovated, modern studio located at the Hawaiian Monarch building in Waikiki. This 18th-floor unit offers stunning city views, a queen-sized bed, AC, smart TV, WiFi, and cable, providing comfort and convenience for up to two guests.Registration Number: GE-083-430-4000-01, TA-083-430-4000-01, 260140320332
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the epitome of luxury in suite 465, a beautifully renovated 2-bedroom residence at Kaanapali Alii. Situated on the 6th floor, this corner unit offers breathtaking views of the West Maui Mountains and the renowned Kaanapali Royal Golf Course, providing an unrivaled backdrop for your Hawaiian getaway.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Experience the ultimate Hawaiian getaway in this beautiful end-unit condo located within Turtle Bay Resort on the North Shore of Oahu. Surrounded by swaying palms, lush greenery, and spectacular golf course vistas, this fully furnished condo offers a perfect blend of comfort, privacy, and natural beauty.Registration Number: TA-029-568-0000-01, GE-029-568-0000-01, STR 2428, 570010290000
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kahuku
Experience the ultimate island lifestyle in this stunning Kona residence located within a desirable gated community. This luxurious single-level home offers three bedrooms, each with en-suite bathrooms, and a half bath for guests. The spacious great room, dining area, and gourmet kitchen seamlessly flow to the large covered lanai and in-ground pool, providing breathtaking views of the ocean and Kona coastline.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
