I'd have to say that my favorite game story of all time is in Super Nintendo's "Chrono Trigger."

One of my favorite stories are:
FFVII
FFIX
FFX
Resident Evil 4

Those are the games that really got me hooked to the story line and wanted me to play them so badly.

Here are a list of my favs.

Halo(Series)
Final Fantasy 7
Legend of Zelda(I know the story is almost always the same but I love it)

I'll edit my list when I think of some more.

Final Fantasy VII, although overrated, is a wonderful story. However, I think FFX is better by just a bit.

Actually, there are quite a few with a different story.

Now that I've actually played the majority of the games, Half-Life 2 and it's episodes have what is probably the best story I've seen in a long time. It keeps you guessing and everything ends in a cliffhanger.

Great story?

Knights of the Old Republic is one of the best stories I have seen.

Halo (the entire franchise) is one of the most detailed stories I have seen for a FPS. One thing that irritates me about Half-Life 2 is how you are always in FPS view. Watching a cutscene or cinematic from an onmiscient narrative perspective really captures the entirety of the story. Something I felt Half-Life never did.

Also Mass Effect.

I was compelled to complete Mass Effect, the Halos, and KoTOR. I was never compelled to complete Half-Life. Great game but that is my main complaint.

It must be resident evil, silent hill, FF VII and FF VIII...

I'd have to say Chrono Trigger. Granted it had like 12 different endings but that was the first game of its type to have a different plot based on the decisions you made. also I think it wins the award for best main character who never says a word

I love the Tales of stories. They're just so..awesome and wonderfully told. I mean, Tales of Symphonia, for example. Start off as Lloyd, typical idiot hero (how else will they teach you basics? xD) whose friends with an elf named Genis and a girl named Colette, who is actually called the Chosen. What is the Chosen? A person who must go on a journey to receive the blessings of the angels and bring prosperity back at the cost of her own life (Deja vu for FFX anyone? xD I promise it twists) This is to stop the Desians, evil half elves who enslave humans on a Human Ranch. If you play the game, you find out that a huge can of worms opens up and the story is a lot deeper than what i just told.
And I'm really excited because they're coming out with a sequel for it on the Wii ;D
And Tales of Phantasia, a remastered SNES game for the GBA, is the prequel to it, but it's very far back in time so none of the characters in Symphonia are in it, but there are some connections.