This is an interesting and kind of a bit of a complicated topic at the same time. If you've asked me this about a decade ago it would be a lot easier to answer. But these days, technology has reached quite high pevel and because of this many titles that are coming out now are quite impressive visually.
You see, I can't say that Hogwarts Legacy for example is better or worse looking something like The Last of Us 2 or whatever else. Simply because both games are very great looking visually but due to different factors, whether it's the universe itself, art style and the engine that they use.
The Last of Us 2 is def up there if we are talking strictly as video game technology and realism. But then we have God of War Ragnarok, it's also good looking but it's a bit more colorful and has a more stylized artstyle. So which one is better looking, GoW R or TLOU 2? Well the answer is neither, they are beautiful and unique in their own ways.
If you are asking what's the best art direction I've ever seen, then my answer would easily be the From Software titles, more specifically Bloodborne. From Software are just in a class of their own when it comes to art direction. As far as tech and optimization goes, they are god-awful at it.
World of Warcraft is also quite impressive, it's an ancient game at this point but it's art style and consistent engine upgrades make it a looker despite the game being more low poly than most modern titles.
Sea of Thieves even though it uses UE4 has a really unique art direction and it's vivid colors are fun and enjoyable to look at.
If I had to do a list of best looking titles I've ever seen it go like this in no particular order.
DOOM Eternal is quite amazing as well from its visual level design spectacle, to the gore system and the Demons. Very awesome engine and runs super smooth and fun to look at.
World of Warcraft
Bloodborne
The Last of Us Part II
Sea of Thieves
DOOM Eternal
Never played RDR2 so it's not on the list for me since I've never personally played it, but it does seem pretty good looking.