Final Fantasy VI is up there, but Final Fantasy VIII is up there for me too, along with Suikoden I-III and Chrono Trigger.
The Dragon Quest series is pretty good too, especially VII.
If we're talking about rpgs as a whole, I'd also include Planescape: Torment and Baldurs Gate II and the Fallout games.
But yeah um...aside from these games, I can't think of any rpgs that have good stories. I'm not too sure about Lost Odyssey yet because I haven't beaten it, but I think the game would be on this list if they had the story written by the dude who wrote the 1000 years of dreams.