I don't think a single game stands out, but notable ones are.
Splinter Cell Chaos Theory was the first game I remember to feature rain effects, it would make Sams suit all glossy.
Mass Effect 2, has very nice rain effects on character armor and windows.
Fear 3 has the most realistic look rainfalls I have ever seen, and sky boxes too.
watch http://www.youtube.com/watch?v=XlSdKV61zR0
Sleeping Dogs also have very nice looking rain, and puddles.