Couple major character deaths (all in JRPGs) come to mind and the ending of Mafia 1 had several of best twists in a game story ever.
But the top one is Final Fantasy III, how in the point where general game structure makes you assume - I was in about 40 hours at that - the end is near and suddenly whole world is rearranged and turns out you're only halfway of the game.
Final Fantasy V did the same - twice! - and that might be just as dramatic, I just had played III first so it was such shock anymore.
And kind of the same thing with second gen Pokemon games. Actually those might be even better, if you had played first gen before it.