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 14300, 50
Discover our luxurious oceanfront hotel rooms offering breathtaking sunset and sunrise views, along with the soothing sound of waves. Each spacious 263 sqft room includes two double beds, a balcony, coffee maker, mini fridge, microwave, and all essentials for a comfortable stay. Registration Number: TA-043-621-7856-01, GE-043-621-7856-01, 260211070000
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Escape to this tranquil ground-floor Maui bungalow nestled in the peaceful Napili Ridge community. The updated living space offers a cozy, tropical ambiance with charming aloha touches. Relax on the soft seating in the living room, stream your favorite shows, or step outside to the private patio where the gentle sway of palm trees and the fragrant tropical air provide a soothing backdrop.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Experience the vibrant heart of Waikiki from this charming 363-square-foot deluxe city view studio located on the 5th floor of Waikiki Grand #518. Designed for solo travelers, active couples, or small families, this tropical-themed space offers all the comforts needed for a memorable Hawaii getaway.Registration Number: GE-177-280-4096-01, 260270310076, TA-177-280-4096-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> HONOLULU
This charming, newly renovated condo in Koloa offers a perfect blend of historic Hawaiian plantation style and modern comforts. With air conditioning, vaulted ceilings, and tropical decor, it creates a serene and comfortable retreat for families, honeymooners, or anyone seeking a peaceful escape on Kauai's sunny south shore.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Koloa
Experience breathtaking panoramic ocean views from this stunning top-floor 1-bedroom condo nestled in the heart of Waikiki. This spacious retreat features an open, airy layout with original hotel finishes, large windows, and a private outdoor dining space perfect for enjoying the island breeze. Registration Number: GE-113-200-4864-01, TA-113-200-4864-01, 260230560133
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Escape to this luxury 7-bedroom beachfront villa located in the exclusive Hualalai Resort, Kanae Estates. Nestled in one of Hualalai’s premier residential communities, this home boasts oversized homesites, stunning drop-away golf course vistas, and expansive coastal ocean views.
7 bedroom
7 bathroom
Sleeps 14
Hawaii> Kailua-Kona
Immerse yourself in the beauty and luxury of this captivating estate nestled in Makalei Estates, offering unparalleled sunset vistas over the Pacific Ocean. 11 acres of lush botanical gardens and privacy, this residence combines elegance with comfort in every detail.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Discover this recently remodeled one-bedroom unit ideally located just steps from the Ironman start and finish lines. This condo offers a perfect base for Ironman participants and spectators alike.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua-Kona
Discover the vibrant allure of Waikiki from our beachfront hotel rooms, offering stunning ocean views and easy access to Kuhio Beach and Diamond Head. Each spacious 350 sq ft room features two queen beds, a private lanai, and thoughtful amenities including a mini-fridge, Keurig coffee maker, and free bottled water, ensuring a comfortable stay. Registration Number: 260260200000, TA-099-007-6928-01, GE-099-007-6928-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience stunning ocean views from this spacious 1-bedroom condo in Hawaii. The open kitchen features all essentials including a dishwasher, stove, oven, refrigerator, microwave, coffeemaker, toaster, blender, and tea kettle, along with a complimentary tea starter kit.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Lahaina
Welcome to your perfect Waikiki getaway at Marine Surf, a beautifully renovated studio located on the 22nd floor offering stunning partial ocean, city, and mountain views. This surf-inspired unit is fully equipped with everything you need for a relaxing stay after a day of sun, surf, or sightseeing. Registration Number: GE-006-206-8736-01, TA-006-206-8736-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the tranquility of West Maui with these two charming condos, ideally situated for a memorable island escape. The first unit offers 1,650 sq ft of comfortable living space, perfect for families or groups seeking spaciousness and convenience.
7 bedroom
7 bathroom
Sleeps 16
Hawaii> Lahaina
Discover this spacious oceanfront royal condo offering breathtaking views and luxurious comforts. The 2-bedroom, 3-bath layout comfortably accommodates up to 6 guests, making it ideal for families or groups seeking a relaxing getaway.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapaa
Immerse yourself in the serenity of our exclusive Kapalua villas, nestled amid lush gardens and just steps from pristine beaches. This 1350 sq ft, fully-equipped 2-bedroom golf villa offers a perfect blend of luxury and comfort, ideal for families, couples, or groups seeking an unforgettable Hawaiian escape.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to your Tree House, a magical and tranquil retreat nestled deep within the lush forests of Oahu, Hawaii. This newly renovated and uniquely designed home provides a private escape into nature while offering all the modern comforts you desire. Registration Number: GE-054-870-9888-01, TA-054-870-9888-01, 950130240000
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Millilani
Welcome to Kahana Village Residence 37, a beautifully remodeled 3-bedroom, 2-bath condo nestled in West Maui. This luxurious oceanfront retreat offers spectacular views of the ocean and the island of Molokai, providing an ideal setting for your Hawaiian vacation.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Lahaina
Welcome to our dreamy Kona getaway! This newly refurbished, luxury retreat is designed for relaxation in the heart of Kailua Kona. Perfect for group or family trips. Walking distance from Kona's best restaurants and incredible sunset views. It provides easy access to Ali'i Drive and all it offers, including beaches, snorkeling, sightseeing, shopping, and eateries.
3 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua-Kona
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
5-bath vacation home nestled in the heart of Poipu Beach Estates. This spacious two-story residence comfortably accommodates up to 10 guests, making it ideal for large families or groups seeking a luxurious island escape.
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Koloa
Welcome to your perfect Waikiki retreat at Marine Surf Waikiki, a bright and comfortable studio offering 392 sqft of open living space plus a 52 sqft lanai. Located in the heart of Waikiki, this unit provides easy access to iconic beaches, lively restaurants, shopping, and entertainment options. Registration Number: GE-076-033-3312-01, TA-076-033-3312-01, 260190010226
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Escape to this charming studio nestled in the scenic town of Hawi. The cozy space offers a comfortable queen bed and a fully private outdoor shower, allowing you to enjoy the soothing sounds of nature while bathing. Ideal for those seeking tranquility, the studio features a small kitchenette equipped with a full fridge, water filter, toaster oven, basic cooking supplies, slow-cooker, and water heater—note that there is no hot-plate or stove.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hawi
Welcome to Hale Kaimana Breeze by Gather, an exceptional beachfront retreat located in the prestigious Colony Surf building on Honolulu’s Gold Coast. This unique property combines two distinct units—Hale Kaimana and Kaimana Breeze—offering a flexible and spacious 3-bedroom, 3-bathroom setup with breathtaking ocean views and direct beach access.Registration Number: 310320100111, TA-057-714-2784-01, GE-057-714-2784-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Honolulu
Welcome to Puamana 153-1, a two-bedroom + loft townhome in the tranquil, gated Puamana community just south of Lahaina. Set on 30 private oceanfront acres, Puamana offers a peaceful alternative to high-rise resorts—just steps from the shoreline and perfectly located for exploring the best of West and Central Maui.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lahaina
Experience the perfect Hawaiian retreat at Island Colony Apartment 1520, nestled in the heart of Waikiki, Honolulu. This spacious one-bedroom corner suite offers stunning city views and a comfortable, cool environment with trade-wind breezes or air conditioning. Registration Number: TA-146-595-9424-01, 1260210260164000, GE-146-595-9424-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Enjoy a comfortable and conveniently located Maui retreat at Kahana Manor 608, just across from the ocean in the heart of Kahana. This spacious two-bedroom, two-bath condo comfortably sleeps six with a king bed, queen bed, and a sleeper sofa, making it ideal for families or groups of friends.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
This ground-floor, 3-bedroom, 2-bath townhome located in the sought-after gated community of Alii Cove, it’s just minutes from shopping, restaurants, and beaches. Enjoy the ease and flexibility of ground-floor living, including the rare ability to BBQ on your spacious garden-level lanai—a privilege not commonly allowed in Hawaii condos.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Feel a sense of serenity wash over you the moment you step inside Hale Malie, a luxurious private villa on Kauai that embodies the Hawaiian concept of malie—meaning calm, peace, and tranquility. Spanning over 7,000 square feet of elegant living space, this contemporary Hawaiian retreat offers four bedrooms and three bathrooms, providing ample space for relaxation and privacy.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Koloa
Extensively renovated, this contemporary 2-bedroom, 2-bath single-level upper corner villa offers a perfect blend of luxury and sweeping ocean vistas. The spacious primary suite features majestic ocean views overlooking the entire resort and includes a renovated 5-piece bath.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua Kona
Experience a unique Hawaiian escape in this charming 2+ bedroom, 1-bathroom treehouse-style cabin located in Kailua-Kona. Nestled on Alii Drive near Magic Sands Beach Park, this elevated jungle retreat offers a perfect blend of modern comforts and natural beauty.
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Kailua-Kona
Located on Maui's south side in a peaceful neighborhood close to the beach and famous shave ice spots, the THERE Maui Cottage offers an intimate, stylish space for your island escape. This detached, one-bedroom retreat is nestled on a quiet cul-de-sac and is part of the larger THERE Maui property.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kihei
Discover this spacious 10th-floor penthouse studio in Honolulu, offering breathtaking views of Diamond Head, the ocean, and nearby beaches. The apartment features a full kitchen, high-speed WiFi, air conditioning, and in-unit coin laundry, ensuring a comfortable and convenient stay. Registration Number: 260270310170, GE-208-244-0704-01, TA-208-244-0704-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
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 bathroom condo located along the island’s east shore. Situated just steps from the shoreline, the sights and sounds of the waves are palpable from the condo’s interior, which boasts a private lanai off the living room. This direct beachfront view condo will bring your beach vacation dreams to life!
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Kapa?a
Discover a beautifully redesigned 3-bedroom villa on Waiulu Street at Hualalai Resort, offering a perfect blend of elegance and comfort. This newly renovated retreat features contemporary interior design, high-end furnishings, and custom artwork, creating an inviting atmosphere for up to 6 guests.
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua Kona
Located within the prestigious Mauna Lani Resort on the stunning Big Island of Hawaii, Mauna Lani Point F303 offers a luxurious two-bedroom, two-bath retreat. Exquisitely renovated and decorated, this residence boasts panoramic ocean and golf course views, and seamless indoor-outdoor living for a true Hawaiian experience.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kamuela
Savor breathtaking views of the Pacific Ocean and downtown Waikiki from your private lanai at Sans Souci. Step outside to see majestic Diamond Head and the lush Kapiolani Park. Watch surfers, sea life, canoes, sailboats, and the vibrant lights of Waikiki, including Friday Night Fireworks. Registration Number: TA-006-211-1744-01, GE-006-211-1744-01, 310320070000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Experience a luxurious stay at Marriott's Ko Olina Resort and Beach Club, nestled on the stunning western shore of Oahu, Hawaii. This family-friendly resort offers a perfect blend of comfort, leisure, and natural beauty within a lush 642-acre gated community featuring seven shimmering lagoons and waterfalls that welcome your arrival. Registration Number: 91057012, TA-127-579-9040-01, GE-127-579-9040-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei-Oahu
Experience the ultimate Maui getaway in this luxurious corner condo at Kaanapali Alii. Spanning 1,807 sq. , this residence offers breathtaking mountain views and a prime location just steps from Kaanapali Beach.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Lahaina
Welcome to Kaanapali Royal H-302, a beautifully remodeled spacious two-bedroom, two-bath condominium offering over 1,400 square feet of comfortable living space in a townhouse-style layout. Nestled on the picturesque Kaanapali Golf Course, this property provides an ideal retreat for those seeking relaxation and adventure in Maui.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Lahaina
Discover the charming Aloha Vibes apartment located in the heart of Waikiki. This stylish and cozy space is designed to help you recharge, whether you're on vacation or working remotely.Registration Number: 260200330052, TA-178-486-7328-01, GE-178-486-7328-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Honolulu
Experience a relaxing getaway in this spacious 898 sqft condo featuring a partial ocean view from the lanai. The bedroom offers a luxurious king bed and an en-suite bathroom with a bathtub and separate shower. A separate guest bathroom with a shower provides added convenience.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapaa
Welcome to Wailea Beach Villas Penthouse 205, an elegant and spacious retreat offering breathtaking ocean views and upscale living in the heart of Maui. This beautifully appointed three-bedroom, three-bathroom residence provides a perfect blend of style, comfort, and convenience for families, couples, or groups seeking a luxurious island escape.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kihei
This exceptional offering combines four neighboring ground-floor residences at the prestigious Honua Kai Resort, creating an ideal retreat for large families or groups seeking luxury and convenience. With a total of approximately 4,600 sq ft of living space, these adjacent residences offer the comfort of a private home combined with the amenities of a world-class resort.
12 bedroom
8 bathroom
Sleeps 24
Hawaii> Lahaina
This impressive 4-bedroom accommodation at Honua Kai seamlessly combines two adjacent residences with a neighboring partial oceanview studio, providing an excellent value for larger groups or families seeking a luxurious Maui getaway. Overlooking the lush courtyard of Konea Towers, Suite 331 is an upgraded corner studio offering peek-a-boo ocean views framed by tropical foliage.
6 bedroom
4 bathroom
Sleeps 14
Hawaii> Lahaina
Stay in a comfortable condo located within the prestigious Ala Moana Hotel, right next to the renowned Ala Moana Center—the world's largest shopping mall. Guests enjoy access to the hotel’s amenities including a pool, gym, spa, and on-site Starbucks, providing a convenient and luxurious experience. Registration Number: TA-187-402-9056-01, 1230380020472, GE-094-938-0608-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience the beauty of Kauai from this spacious 2-bedroom, 2-bath condo at Hanalei Bay Resort, perfect for family and friends. With approximately 1,200 sq ft of living space, the unit offers a comfortable one-level floor plan featuring modern amenities and stunning views of Hanalei Bay and the Bali Hai Mountains.
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Princeville
Discover this beautifully renovated one-bedroom apartment in Honolulu, completed in 2024 with stylish designer furnishings. The unit offers a cozy queen-sized bedroom, a charming living room with a sofa bed, and a clean, modern bathroom with a refreshing shower. Registration Number: GE-112-393-0112-01, TA-112-393-0112-01, 270180150000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
Welcome to your perfect Maui getaway at Kihei Kai Nani! This charming 1-bedroom, 1-bath condo offers an ideal location just steps from the renowned Kamaole Beach II, making it a fantastic choice for beach lovers and explorers alike. Inside, the spacious living area features cool ocean blue decor, creating a relaxing coastal ambiance.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
Come to this Premier newly remodeled unit at the Kihei Kai Nani. Known for our desirable, ground floor unit. This unit has everything to make this your best vacation yet. Take in the tropical garden and pool view from this 2 bed, 1 bath unit with split AC.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kihei
