When picking the best places for living near the water, you have to consider two factors: beaches and good cruising and fishing for boaters.
The locations that combine both tend to be pricey, such as East Hampton, which has great ocean beaches, including one ranked in the top 10 by national beach expert Stephen Leatherman, aka Dr. Beach, of Florida International University. There are also great places to keep a boat or visit in one, such as Three Mile Harbor.
Bayville is a favorite - I live there - because you can have a house on Long Island Sound if you can afford it and enjoy a typical North Shore rocky beach even if you can't, because the village and Oyster Bay Town both have public beaches on the Sound.
Another great spot for living by the water is Greenport, which has homes and condos near the harbor and boasts excellent marinas, protected waters with great scenery and many nearby maritime destinations for boaters.
Long Beach has lots of condos and other housing with views of the Atlantic and Reynolds Channel, a great sandy ocean beach for swimming and surfing and a choice of protected boating and fishing in the channel or a quick trip out into the ocean.
The Huntington-Northport area is another magnet for water lovers. There are numerous town and village beaches - the rocky North Shore type again - and great places for boating and mooring a boat.