My opinion about them:
Lightwave: One of the best for modeling. Interface is cumbersome (Takes time get used to) and texturing is in another part of the software.
Maya: Great modeling. Interface decent. Texturing insanely difficult, especially when dealing with alpha and multi textures.
Poser: For characters only.
3ds Max: Same as Maya.
C4D : The most easiest software. Easy interface and easy texturing. However, modeling has limits and doesn't have a decent texture map import/export.
CAD Autodesk: For architects
After Effects: 3D Text and websites
3Dmax: Got around it. You have some limitations with modeling, but not has extensive then Cinema 4D. Figured out textures. The best site i found to learn 3DSmax was this one: http://www.mrbluesummers.com/
I stopped using C4D, mainly because at how the software would corrupt a model when doing Boolean or trying to cut hole in a shape.
After Effects is also a software where you can put overlay effects on movies, like water, snow, lighting and more...