I agree with Ico, SOTC, Widwaker, Mario Galaxy and all of those. Okami is another obvious one. Comix Zone is an older game for the Genesis, but it also had an interesting look. Super Paper Mario has a pretty unique style.
As far as indie games go, Knytt, Within A Deep Forest, or anything else from that Nifflas fellow are interesting in a minimalist sort of way.
I was actually a big fan of TP. I thought that the twilight realm had a pretty interesting visual flair to it, especially when you go to the Twilight temple near the end of the game.
Also, I like the art direction of Team Fortress 2. They really put a lot of work into it. He's a video of the graphic's people justifying and explaining their choices in depth:
http://www.youtube.com/watch?v=aswHCFUqprEThe new Prince of Persia game has a pretty cool look to it as well.
EDIT: Also, Samarost and it's sequel have an awesome style:
http://amanita-design.net/samorost-2/