select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `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,
(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 and round(price)>0 and bedrooms>=0 AND (`headline` like '%North Shore Oahu%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This upstairs, 3-bedroom 2-bath home, with its spacious open floor plan, oversized rooms, vaulted high ceilings, and panoramic windows gives you sweeping views of the Pacific Ocean and is backdropped by the rugged Ko'olauloa Mountains. Sit ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
Welcome to the Green Pineapple, a gorgeous 3 bedroom estate on Oahu's North Shore. Take a walk down memory lane in style, as this gorgeous North Shore Oahu estate has all the charm of Hawaii's vintage era, in this two-story, luxury home. *400mb...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
**In compliance with Hawaii vacation rental laws, this property is held for your exclusive use for a period of 30 days or greater regardless of your actual length of stay. Please inquire for details or questions regarding availability.
E ...
- 4 bedroom
- 5 bathroom
- Sleeps 9
Hawaii> Haleiwa
This spacious modern ocean front homes is beachfront luxury on the north shore. Unobstructed views of the ocean welcome the guests from both floors of this two story home. The first floor features a large living room, a half kitchen and a two ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Wailua
Oceanfront, 3-bedroom, fully furnished home nestled directly on the shores of Waimea Bay, this home boasts a prime location with the best view on the North Shore. Lounge comfortably on the covered lanai just feet from the lapping surf, where you...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
Welcome to the Green Pineapple, a gorgeous 3 bedroom estate on Oahu's North Shore with an attached studio apartment. Take a walk down memory lane in style, as this gorgeous North Shore Oahu estate has all the charm of Hawaii's vintage in this ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Haleiwa
Located on the North Shore of Oahu, in the charming community of Mokuleia, 10 minutes west from Historic Haleiwa town, this is a one of a kind Hawaiian experience. Directly above where they filmed episodes of the hit TV show "LOST," this home is...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Mokuleia
You've found Hawaii’s Best Kept Secret! (And YES WE HAVE A LICENSE TO RENT SHORT-TERM! SEE BELOW) The cottage is adjacent to a whole mile of deserted beaches, where you’ll discover isolated sandy coves, driftwood and shells, and forested ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Waialua
This fully equipped, ocean front, condo unit is located in Waialua on the beautiful north shore of Oahu. The minimum stay is 30 days at a rate of $185 per day or $5,550.
The mountain side master bedroom features a queen size, comfort ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waialua
Looking for a serene, simple, and peaceful getaway?
Hale O Ka Kai Nani (House of the Beautiful Sea) is the perfect oasis for you. This one-bedroom, two-bathroom unit offers guests plenty of privacy and tranquility, while also being situated ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
Laie, Oahu, North Shore Hawaii, 2 bedroom 1 bath apartment on the side of our newly constructed home. Queen bed in one room and the second bedroom has two twin beds, or it can be set up as a bunk bed, or even pushed together for a couple. Sofa ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Laie
Shake your grass skirt & hula on over to the Luau Lounge for the Hawaiian Vacation of your dreams! This cozy condo is located at the exclusive North Shore of Oahu, on the famous Turtle Bay resort grounds just steps away from the most ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
4 bedroom home in Laie Hawaii. Will be newly renovated in Fall 2021. Perfect for a family vacation. This is your chance to see Hawaii and experience the beauty of the North Shore.
There are two A/Cs in the house. One in the master a big ...
- 4 bedroom
- 2 bathroom
- Sleeps 10
Hawaii> Laie
This spacious 2 bed is newly renovated with a private staircase leading down to the beach. It has a modern coastal design. It is fully furnished with a full kitchen and in-unit washer/dryer.
Check out our full outdoor shower after an ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Hauula
