Uh I think a game should look good within it's own terms.
Like, for me (in my opinion, you see) a pretty SNES game looks better than any N64/PS1 game simply because both consoles can't bring themselves to render some decent looks. I don't know, it's just that the 5th generation is the uncanny valley for me, and that totally ruins the gaming experience (I would not give a single N64/PS1 game a rating higher than 7).
In the other hand, both Zelda:WW and SMG are examples of games with amazing visuals, because they accomplish their intents, so to speak. Maybe one could add Wii-series games into this list...
For last but not less important, some kind of games - like shooters, action and RPG games - should always look for the best visuals unless they seek something else than photorealism (like the already mentioned Wind Waker).
