I've got a few that haven't been said here yet.
First off, one of the better ones in my lifetime: Chrono Trigger
Honestly, seeing the main character sacrifice himself not even halfway through the game blew me awawy. That just doesn't happen in games. The fact that you could finish the game without him is even more twisted.
Second one has to go to Final Fantasy 6/3.
That was the first game I remember where you try to battle the archvillain to stop him from disfiguring the world... and you're too late. Even though you do take him out in the end, the fact that his plan works and you can't stop him was a grand twist I hadn't seen until then.