Some good mentions here. I would add Ico along with the already mentioned Shadow of The Colossus. While the game had a happy ending, seeing Yorda as stone while beating up all of the shadows in anger was pretty cool, and rather sad to stand right next to her and have Ico call out to her.
I would also nominate guilt in Metal Gear Solid by pressing select in the torture part. I mean come on, because you can't press the button fast enough, you have to sacrafice Meryl's life. Well, at least getting stealth for the bad ending is a good reward to compensate.