Pokémon Mystery Dungeon: Explorers of Time and Explorers of Darkness's climax comes to mind. No, I'm serious.
You'd gone to the past to fight what amounts to the god of time and space, kicked the crud out of it, and stopped its batshit crazy rampage. You head back down to resume your life of harrowing adventure with your cute Pokemon buddy that you've had with you the whole game, but along the way you experience some unexpected side effects: because you've stopped the time god's tirade, you've changed history, and since its actions were directly responsible for your being in the first place, you've retconned yourself from existence. Your friend, whom you've given strength and courage, made into a great adventurer and finder of mysterious treasures, helped make them who they always wanted to be, watches you fade from existence, and balls their little eyes out, begging you, their best friend, the only person that didn't treat them like a craven loser, who gave them a chance to make something of themselves, not to go. You try to reassure them and make them be brave, but you ultimately just leave them there sobbing, alone.
It was the fucking saddest thing I'd ever seen in a video game that I can think of, and it happened in a Pokemon game, for crying out loud, a series that lives off of useless excuse plots. Color me shocked.
Ugh, I need to go blow my nose. Damn you, Pokemon Mystery Dungeon!