pure story is ffx, after that comes a list of games that made me feel involved in the story pretty much equally.

FF4 (2 US)

I liked Twilight Princess, Path of Radiance and MP3.

PoR I liked because it left questions for the sequel. Who killed my father!?

Lunar Silver Star & Eternal Blue. I actually don't remember the story that well, but I remember loving it :P. Now I'm going to have to replay :D.

@super_etecoon
I totally dug the original Blood Omen

Final Fantasy 10 for the emotions it gave me... I still cry at the end. TT_TT

Star Ocean 3 for the twists and really well thought sci-fiction style story.

Grim Fandango & Lunar Silver Star Story. To a certain extent Indigo Prophecy and Dragon Quest 8

Tetris! Oh, wait... what is this thread about?

 FFX had a good story and good characters, but I couldn't get behind it completely because Tidus was such a whiny moron.  For me I have to be able to care about the characters for a story to be good.  That's why FFVI is the best FF of all time for me.  You can really care about everyone of those people from beggining to end.

Im a sucker and rarely like the stories of the games my friends like. Recently, Uncharted is nice. Of all time...thats a really hard one...I'll have to go with a final fantasy but please dont make me pick! I just remember being really attached to some characters.

The Squall and Rhinoa story in Final Fantasy Viii captivated me. Yes, Final Fantasy X was good, but Tidus was a horrible charachter as he was way too feminine.