Vagrant Story:
Hands down the coolest storytelling out there. it's what MGS would be like if set in a medieval fantasy setting instead. Extremely cool and well-defined characters and a story so full of backstabbing and plotting you'll need to spend some time unscrewing your brain after playing it. At the same time it doesn't push every tidbit of the story into you face, and there's plenty of room for personal interpretation. I almost enjoyed arguing over it on message boards more than I did playing it. Only thing lacking is actually som really good voice acting to go with it.
Final Fantasy Tactics:
Written by the same person if I'm not mistanken. Also really cool, running a lot on the same kind of themes, but on a much larger scale. I like the feeling that you're part of something much bigger, instead of being the focus of the worlds single most important event as is usual.
MGS:
Has to have a mention. Especially excellent storytelling. Can't really decide which one I like best though, but I think 3. It didn't feel quite as way out there as the other ones. Best example of a movie being shown in the form of a game.
Legacy of Kain:
While MGS is a movie, LoK is a stage performance. There are hardly any action scenes at all in the storytelling, everything is just the characters talking, often holding long monologues about the current state of affairs. I love it. The excellent voice acting does a lot to bring it to the level it's at. It's important to view all the games as one big story, as it's really nice how everything gets more and more tied together the farther the story progresses. Several twists that I did not see coming.
Xenogears:
Almost too much story here, but that makes it really cool. You are loaded with forty tons of storytelling, but you still feel like there's enough for 5 games, 3 movies and 7 books to follow. I like the political aspect and the take on religion that they use.
Tales of the Abyss:
A bit under the other ones, but I love the game so much that I had to mention it. It mainly stands on the fact that every singles character is incredibly interesting. While some parts of the story gets too sappy/old/whatever, it was overall a more interesting experience than most rpgs of late, certainly far more so than the older tales. Also, unrelated, but Best Battle System Ever!
Honorable mention for Baldur's Gate. I'm not a big fan of that kind of storytelling, but BG pulled it off quite well. Second mention for Suikoden 2. I really don't like the silent protagonist thing, but S2 is probably the only game where I really didn't care that much about it. Once again, I like that the story is only a small part of world events, and it's not even for sure that it would be best if the side you are on wins.