One thing I notice time and again--games that try to be realistic almost always look dated after a couple of years. Games that are more stylized always seem to age better. Some games are good for their time. Other games are timeless.

Journey and Okami.

Absolutely loved the style of Valkyria Chronicles.  Imagine this at PS4 levels.

ICO was a game that completely blew me away at the time.  Even today, I probably have more vivid memories of that game than any other.  I remember the scene where you first emerged from the initial chamber and you found yourself way, way up on a crosswalk.  It was breath-taking.  It was one of many instances where I stopped "playing" and just took in the surroundings.  It was a total game-changer for me in a lot of ways, including the real power of "graphics".

I'd have to say Xenoblade. I spend a LOT of time just staring at the scenery, even the title screen.

Honorable mentions: Rogue Squadron II, Wind Waker, Cave Story, Machinarium, Ghost Trick, The DKC games (all of them), the BIT.TRIP series (excluding Void), Geometry Wars, Kirby's Epic Yarn, Mario Kart 8, Trine 2. I also think that once I get around to playing Okami, Metal Slug Anthology, and Ico, they'll likely be on this list.

Just looking at Final Fantasy VI gives me heart palpitations: the cozy little houses, cute character sprites, gorgeous battle backdrops, cool looking monsters, Amano's character portraits... everything.

I'm also very fond of the look I associate with Japanese games from the PS2/Dreamcast era where they were aiming for increased realism but the technology didn't permit them to become so slavishly devoted to it yet, so it was still rather stylized (and sometimes creepily animatronic looking).

I dont know if this counts but:
Fire Emblem Awakening, Professor Layton VS Phoenix Wright , Professor Layton and the miracle mask and Luigi Mansion 2 on the 3DS with....... 3D ON!

Seriously, playing those games on the 3DXL with the 3D on looks BREATHTAKING!!!! The Action Scenes on FE:A, the puzzles and the city while you move around on Professor Layton, and the interior house while you also move the camera around on Luigi Mansion. Just AMAZING!

Mirror's Edge hands down. I loved it since the first second I saw it. It's just perfect for me. Straight colors, sterile environments but still somehow very vibrant. Love the updated look of the sequel as well.

Of course those count! I've played Fire Emblem and Luigi's Mansion in 3D so I know exactly what you're talking about. They are so beautiful. I find the cutscenes in Awakening to be particularly breathtaking.

There aren't any limitations. Because certainly we've seen upcoming games that look stunning (Zelda, Bloodborne, Uncharted, Splatoon, etc.)

I don't really limit myself to a single artstyle but my favourite game graphically is Wind Waker due to how well it's aged.

Some other games that I find visually appealing are Last of Us, Metal Gear Solid 5, Mario Kart 8 and in the retro category Shovel Knight.