Water has been an elusive and challenging subject for me. There are many methods to create water in 3d, all with their own advantages and disadvantages. Ultimately though, all anyone wants is something that looks convincing, fits the art style, and doesn't reduce the framerate to 5 fps (unless you are into that sort of thing). Flowmaps - as described in this Siggraph presentation from Valve - can be quite convincing, have a low overhead, and are quite flexible to an art style.
After reading the presentation, I knew i had to give it a shot. After a few weeks of struggle and experimentation, these are my results (The rocks you see come with UDK). We can modify the direction, as well as the speed of the water by simply modifying the color of the texture. This shader uses vertex colors as the flowmap, which can later be baked out into a texture (The shader has toggle-able options for both for completeness). I also wrote another version in Unity for Olive which you can see here:
I like the result, and it's only one extra draw call vs plain scrolling UVs! In the future i would like to research how to generate these flowmaps dynamically. Painting them is a bit of a pain and i'd rather my computer do it for me XD.