Gears 2 for consoles or Crysis for PC. Im sry but the UE3 was optimized for more water effects and has certainly produced the best water visuals on a console hands down.

for me, RESISTANCE 2!!!!!

Jelly Water!!!!!!

Bioshock for console, it was a level over everything I've seen. Never played crysis or uncharted yet.

Can you find me one real like picture that actually looks like that though?  It may look nice, shiny, and super, er, reflecty but does water actually look like that?  Especially when inside a 50+ year old dark rusty sub?

Does it matter ? it's still the best looking and most interactive water i can think off. Resistance 2 claims second spot only because the water in it is so damn interactive unlike most fps games.

Anyways i would assume that the water cleaned itself during the 50 years the sub was located there, Afterall water comes in, water gets replaced with other water, water brings dirt out with it. Who cares though. It's a game :p and the op didn't ask for the most realistic water, although i certainly think that in some ways uncharted water is that aswell.

Actually, it kind of does.  If you're asking for the best looking water, you'd want it to actually look like water would look like. 

And that's a fine picture you supplied, but I still don't think the sub would look like that.  Sadly I don't remember what the area around the sub looked like (I'm sure it looked great), only have this to go by.

No game have given me the "WOW" feeling on water effects since Wave Race 64.

Area around the Sub was a waterfall in the middle of a jungle (how the sub ever got up there is anyone's guess.

Remember the area, just can't remember how the water looked :-p

GTA 4 water!!!!!!!!!!!!!!!!