Resident Evil 4 for the gamecube to this day still looks like one of the best games ever to me. So much attention to detail, its gorgeous (and the best Resident Evil game to boot). I would also like to nominate Silent Hill 3, it uses everything it has in a very cool, creative way:
[youtube]http://www.youtube.com/watch?v=Cm7dHtJI7v8[/youtube]
Presently I'd have to nominate Demon and Dark Souls, though this is a heavily biased vote. I'm not what you'd call a gamer anymore and I do agree that both games can look a tad muddy but the architecture is gorgeous as are all the monsters. Movement and attacks are conveyed so well, at the very least it has the best ambiance.
And before some a**hole comes and says "Oh gee look at all these people just including their favorite video games" I'll also add Crysis. I played the demo just to see how high my computer could run it and it was very beautiful. Battlefield 3 looks gorgeous as well. I don't like either of these games but I will give credit where its due.
For the psx I would nominate Chrono Cross, it has jaw droppingly beautiful pre-rendered backdrops and for the time it has very good character models and monsters. Valkyrie Profile is the most beautiful psx game I have ever played, visually. Best pre-rendered characters, wonderfully expressive 2d sprites and Motoi Sakuraba's best work. Resident Evil 2 and 3 look really nice too.
[youtube]http://www.youtube.com/watch?v=4_DnHy8DTYM[/youtube]
For the snes I'm going to have to pick Seiken Densetsu 3 or Tales of Phantasia. TOP is an amazing technological achievement, with full voice acting, full battle animations, a multitude of animated spells and a pretty good length. Chrono Trigger overall is the most beautiful snes game, visually and musically. For the sega megadrive I'm going to have to say Aladdin and The Lion King were the best looking by far. Beautifully animated and very colorful.
For the older dos games I'm going to have to lean toward the first 3 Heroes of Might and Magic games and the Darkside of Xeen has one of my favorite video game intros ever:
[youtube]http://www.youtube.com/watch?v=6vLSoU0E07M[/youtube]
Games just aren't what they use to be.