Answer: The lowest the better
I normally use ISO 100 with my most shots for best quality images, lowest ISO need is only in low light, I try to control it with indirect lighting like bounce flash, however sometimes I have to use higher ISO to capture more light in an image. I try my best not to use higher ISO because it compromise image quality however I use it when I have immense need like in indoor where light is pretty low.
How much ISO is usable depends completely what camera you are using. I was using Canon 40D and sadly 400 ISO was the maximum I used in professional shots and when there is no other choice 800.
I have upgraded to 60D now and I am quite happy with its ISO performance. I easily use 800 ISO when in dark and I never felt bad about the image quality. on 60D i can use even higher ISO but I never used it.
When you use AUTO shooting mode, it automatically chose ISO which is normally the higher in low light situations, you can set ISO manually and the camera will follow it but again instead of losing a perfect shot take it on the highest possible ISO because image is better than no image.