In recent memory, there is Mass Effect:
Heavy spoilers follow.
I expected that when i had to decide who would be going with Captain Kirahee, the one who would go would bite the dust.
Since i had Ashley along me most of the game AND started a romantic plot with her, i instantly chose Kaidan.
In the End, the drama about this sacrifice was a bit downplayed, but since i talked much to my fellow NPCs, i knew most of Kaidans Story, and he was a pretty symphatic character.
So, for a moment, i felt pretty guilty to let him bite the dust so easily.
Spoiler End.
Also, the revelations about "The Boss" at the End of MGS3 made me sad, as well as the surprisingly dark ending of Dreamfall: The longest Journey.
And last but not least, i second Shadow of the Colossus. I remember just standing there at the chasm for one or two minutes, staring in disbelief at the screen.