You might need to wet sand if its hard water that collected on your car.
+1 on that.
I've never dealt with any other products except Meguiars. It all depends on how long you had those water spots on your car and how deep they are.
Ultimate Compound from Meguiars can take most of them off when using DA polisher with yellow pad.
I only use UC on the door handles to remove scratches that were created by nails or keys and it does an amazing job. As for the water sports, I've never used it by hand for the whole car, though I could experiment and tell you what results I get, but I doubt it will do anything if you work by hand and once again it all depends how deep they are and for how long water spots were there.
Ultimate Compound is the strongest compound from Meguiars for Consumers where M105 is strongest for professionals but it is not sold in stores and really expensive.
If you can take a picture of your water spot, it could help seeing how bad it is. If its just on upper surface of clear coat, UC will remove it even with hand, if its more deeper, you would require DA polisher (not Orbital polisher, two different things) and in some places might even require Rotary which WILL burn through paint if you don't know how to use it.
If you have more specific questions, feel free to ask and if I know the answer, I would help.