First thing that comes to mind is MGS3. Some shots in that game were just amazing, especially the final part in the end with the shagohad.
WoW is good looking in an artistic way. The art department over at blizzard did a damn good job with that game. The art is so consistent, and the first time I saw the outland it looked like a big painting. I hate how cartoony everything looks but damn does everything blend together nicely. If the more realistic looking games had that type of art consistency then we would have some damn fine looking games.
HL2: Episode 1 has some good graphics as well. But what really brings it to life is the animation. Whoever animated Dog deserves a pay raise, damn his movements are so smooth. When I first played the game I couldn't get over how awesome Dog looked. The facial animation is pretty awesome as well. First game that really pinned down facial animations. The expressions look so natural.
For upcoming games, I'm really liking the look of Episode 2 and the games that come with it, especially TF2. Like WoW, the art blends together so perfectly. But unlike WoW this is a cartoony style I actually like. Its a serious look... but not, you know? Ah, I dunno, I just like it. Watch the videos
http://www.gametrailers.com/player/12826.html
http://www.gametrailers.com/player/19522.html
As usual it appears the animators at valve have done a top notch job. Also I'm sure it will run just fine on all differnt types of computers.
Crysis also looks pretty good. Real time ambient lighting? Subsurface scattering? Interactive vegetation?Yes ma'am! Lets just see how it runs before we jizz our pants, though. Of course, video:
http://www.gametrailers.com/player/18588.html