...
FFVII: Well, I personally think Aerith's death in FFVII takes the cake here. I didn't know she was going to die when I first played the game, and found it really shocking.
...in distant second place...
FFIX: Vivi's inevitably short life I think was alluded to throughout the story, so wasn't that surprising he actually died in the end, but still sad.
FFX: I think it was also alluded to that all the dead people would be "sent" in the end throughout the story, but given (at least in western tradition) such inevitabilities are generally broken, it is a bit of a surprise they went through with it (though iirc, the happy ending comes in FFX-2?).
FFXII: I didn't want Reddas to die
... but given the lack of time to get emotionally connected with the character, it is less tragic. If the story was written with him as a more central character from the beginning, it would have been almost as shocking as Aerith's death (though he willingly sacrificed his life, rather than it being taken from him, which is a bit different).