Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1852 AND i.igate IN ('usahud','flhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'flhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1852 AND i.igate IN ('usahud','flhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'flhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1852 AND i.igate IN ('usahud','flhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'flhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

45 Clearwater Avenue
Massapequa, NY 11758

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $2,249,999
Beds: 5
Baths: 4
Status: Pending
SqFt: --
Agency: Realty Advisors

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 1966
Notes: YOU DON'T NEED TO TRAVEL TO EUROPE TO HAVE YOUR OWN TUSCAN STYLE VILLA...INTRODUCING THIS ONCE IN A LIFETIME OPPORTUNITY TO OWN THE MOST PRESTIGIOUS 4 BEDROOM..3.5 BATH PRIVATE GATED ESTATE IN ALL OF NASSAU SHORES..FLOOR TO CEILING WINDOWS SHOWCASE BREATHTAKING VIEWS OF THE OPEN BAY FROM EVERY ANGLE...FEATURING JUST ABOUT EVERY AMENITY YOU COULD DREAM OF...DRAMATIC CUSTOM BUILT GRAND WOOD STAIRCASE WITH CLASSIC CHARM..SUBSTANTIAL SIZED GRANITE EAT-IN-KITCHEN W/ISLAND, SUBZERO & WOLF APPLIANCES & BREAKFAST ROOM..FORMAL DINING ROOM W/VAULTED CEILING & WOOD BEAMS..GREAT ROOM WITH LIBRARY..GUEST BEDROOM WITH FULL BATH ON THE 1ST FLOOR...THE 4 SEASONS ROOM IS THE PERFECT PLACE TO LOUNGE & ABSORB THE PICTURESQUE VIEWS OF YOUR OUTDOOR PARADISE...SITTING ROOM W/BUILT IN GRANITE BAR & DUAL FIREPLACE..FORMAL LIVING ROOM..WOOD FLOORS WITH MAHOGANY INLAY..NEW ROOF..MASTER BEDROOM EN SUITE OFFERS A GAS FIREPLACE, WALK IN CLOSET & SKYLIT BATH CONSISTING OF A SOAKING TUB/TOWEL WARMER/RADIANT FLOORS/SHOWER WITH BODYJETS..BALCONY SPANS THE ENTIRE REAR PERIMETER OF THE HOUSE ACCESSIBLE FROM EVERY BEDROOM ON THE SECOND FLOOR..SPACIOUS GYM CAN ALSO BE A MEDIA ROOM..MASSIVE SKYLIT OFFICE W/SEPARTE OUTSIDE ENTRANCE..UNBELIEVABLE & ENORMOUS 270 FEET OF WATER FRONTAGE WITH YOUR VERY OWN BEACH & NEW NAVY BULKHEAD & MULTIPLE BOAT SLIPS W/BOAT LIFT ALL OF THIS IN YOUR COUNTRY CLUB BACKYARD W/PANORAMIC SUNRISE & SUNSET VIEWS W/A GUNITE SALTWATER HEATED IGP & BUILT IN WOLF BBQ ISLAND GREAT FOR ENTERTAINING OUTDOORS..PAVERS & STONE DRIVEWAY W/2 CAR ATTACHED GAR & ADDITIONAL 3 CAR DETACHED GARAGE..PRIVATE SHOWINGS UPON REQUEST..AS PER HOMEOWNER PRE APPROVAL OR PROOF OF FUNDS REQUIRED PRIOR TO MAKING YOUR APPOINTMENT..
MlsNumber: 3530161


Listing Provided By: Realty Advisors, original listing
Name: Realty Advisors
Phone: (516) 826-1111
Office Name: Realty Advisors Inc
Office Phone: (516) 826-1111
Agent Name: Laura Dupkin Memisha
Disclaimer: Copyright © 2024 OneKey MLS. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


Local Real Estate Expert

HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1852 AND i.igate IN ('usahud','flhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'flhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;
Real Estate Expert Photo for Suzanne Rivera
Suzanne Rivera
ASCIIRE/MAX Superior
Call Today!: (631) 300-0947

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App