I love a game that does good water. Some games still put effort in the water while others tend to forget it as its not as important. Seeing games from Wave Race 64 to Crysis to Wind Waker etc. 

A game I revisit often is Sea of Thieves, a game that arrived on the market 2 years ago in 20/3/2018 which has arguably the best water effects we have ever witness in a video game.. and its not just how the waves and water looks, but how it interacts with the player. The physics of the water and how it moves your ship from a calm sea to entering a storm with waves bigger than the ships themselves. You can honestly just stare off the balcony in the far distance hypnotised by the amazing animations of the water in SoTs.

I wonder how long would it take to see a game achieve better water than Sea of Thieves? Water already looks so good now, it will be interesting to see what is the next step or evolution for water going forward. Some really good watches are from Digital Foundry's videos of their insights and history of great water in video games.

DF - Part 1

DF - Part 2 

The first water effect that I remember loving was on FF IX opening scene. Then I totally got nuts with FF X.
Nowadays water is so well represented that I don't think of a specific case of being best. But yes I have seem SoT mentioned as best representation of water by quite a few people.

I honestly haven't played those games, I might just check them out to see what implementation of water they used.

Mario Sunshine was the first time I appreciated water visually in a videogame. It's fairly simple to render good looking water these days and now being a 3D Artist it's easy to understand, but back then it blew my mind. Of course as of now the best of the best is Sea of Thieves. Simply gorgeous.

I seem to remember thinking Star Fox Adventures had great water effects back in the day.

@CGI-Quality 

Metro Exodus is such a good looking game, I haven't gotten up to the part where there is water yet, however I am not surprised that game would be in discussions for best looking anything. I basically call the game the Crysis of today. Everything is just done so well and detailed.

First water effect I remember being impressed by was Unreal, but it was not as much an effect since it was just an animated texture.

First real water effect was Morrowind (I had a DX8.1 video card (Radeon 8500) back then).

Water in Sea of Thieves is - as it should be - pretty spectacular. I haven't spent too much time with the game overall, cus even after all the updates, it's frankly not quite my cup of tea, but I'm not gonna deny it has moments that are hard to forget. The first time you sail through a heavy storm, while fighting another ship, while being chased by a Megalodon shark...pretty epic, and the water effects just make it all sing.

For me it was Unreal -> Far Cry -> GTAV?

I wanna go with Metro and BIoshock.