Depending on how you qualify a story as being "good", I think there are a lot of different answers we could all come up with (and have. Just look at this thread).
For me, in terms of creating a complex narrative, FFVI probably did the best job. I think FFIX tried this, but fell short in a few places, namely the resolution of the overarching plot. (Keep in mind here, narrative =/= writing.)
By far, I think X probably had the weakest writing and narrative. Its characterisation was hammy and over the top (as most of the series is), and unfortunately lacked a lot of the subtlety of previous incarnations. On top of that, it was pretty hammer-on-anvil about Yevon being evil/false/somecombinationofthetwo, and it felt like a very poorly built revelation in the end. Add to that the rather off-key portrayal of some key individuals (Jecht, Auron), and I have to say I wasn't impressed with the cast. There are good things to FFX, like building a larger mythology through the Al Bhed, but it felt like the narrative came up short in a lot of ways.
FFXII is a stellar example of narrative like FFVI was, but it lacked character development for its principle cast. It's a shame too, because the archetypes they used are really strong. They just never went anywhere. FFXII is probably the closest any game has come to being legitimate in its mythology, given that it has environments and creatures explained in ways that largely make sense.
For character development, I think FFVIII has a lot of merit. Certainly, it dealt with far more human characters than is or was typical of RPGs. Some people complain about Squall's sudden turn-about in the third act, but I think it's actually quite reflective of how I've seen people change. They build and build and build, and then suddenly they make a decision that alters their outlook on life dramatically. Also, while there are certainly weak portions to the story, a lot of the isolated subplots are really quite well done.
For overall plot? I think FFVII did things well. The writing is kind of simple at times, and the cast doesn't grow nearly enough, but the plot is genuinely interesting and well developed. It's hard to feel that at times, what with the recent inundation of FFVII material and the distance from the original that I feel these days, but when I really take a look at the material, it's actually not bad.