I liked the rain from Gears of War 1 when you were outside the factory.

Uncharted 3 has great rain effects.

Somewhat off-topic - It never rained in Infamous 1 or 2. Another reason I hate those games and the idea of an electric powered hero/anti-hero as a whole.

When I'll finish Darksiders 2, I'll probably get Sleeping dogs.

Now for the rain :
Ofc : Metroid prime, Wind Waker...
But at the moment the most stunning wheather effect I experience at tyhe moment are in Arma II/DayZ. When in the forest, looking for zombies, you can't hear them as easily and your LoS seems reduced... Amazing.

Not sure if I remember correctly, but the rain effects in Crysis 2 were pretty good. I have just started a second playthrough after almost a year so I am not sure.

@bananaking21
Thanks for the link. I'll be sure to check the game out.