I can only say from the games I've played, but I'm going to go with Metal Gear Solid 2. The way water drips down the tanker, the way they made the illusion of water hitting people and bursting into drops. And when you walk into the tanker the water drips down Snake; amazing. Uncharted 1 comes in second. (Uncharted 1 is better in the environment, but the water droplets don't quite hit the way they do in Sons of Liberty.)

Zelda: A Link to the Past, 'nuff said.

The origami killer murder his victims in rain water and leaves an origami on their chest

Yes, you know the game I'm talking about :D

I really like the rain in Halo: Reach. I was kind of stunned how natural it looked - and it really surrounds you with sound in 5.1.

Also, the rain in Gears of War.

And as far as clouds - Spoiler alert!

The clouds change in that city you fix up in Assassin's Creed II are quite impressive.

Wow! I did not expect so many replies. Thanks everybody. Keep them coming!

For those on the fence about Sleeping Dogs, don't hesitate if you love an open-world game with lots to do in a beautiful city. I have been promoting that superb game as much as I can.

Gears of War had that pretty cool rain level
the Beginning of Castlevania: LoS looked pretty good
FFXIII-2 when you're at Academia

I believe maxpayne was the first time I had a strong impression of rain. was so atmospheric

the rain in GT5 is also kickass

HR is good ..no shocker there

However, even if it's really early to say, the most impressive use of rain....is in 'rain' that was showed in gamescom this year. Clever and atmospheric.

It's true that the rain effect in the beginning of Casltevania Lords of Shadow was impressive. I forgot about that one.

I like how it feels when it rains in Minecraft.

Red Dead Redemption has some pretty damn good effects. Gears of War also has some good ones.