Kerrigan getting left by that double-crossing bastert, Mengsk, and Raynor crying out in anguish.
I remembered that there was some sort of moment in Starcraft where I got outraged. Seems that was it. Kerrigan got hers back, though. I hope Raynor does in Starcraft 2.
Jackson's death (and almost right after he does a heroic thing and saves that nice Cobra helicopter pilot lady too) was also pretty depressing, although I was too caught up in the awe of watching the mushroom cloud and watching tall buildings disintegrate and crumble into ash. Call of Duty 4's strengths were that the Hollywood-type scenes were quite easily immersive, and the fact that you could actually control Jackson while he crawled around in futility with his last breaths gives the scene a lot more empathy than it actually needed.
I was also sad at Captain Price's death, but I was more sad at how Gaz and Griggs got so easily cut down (Zakhaev pretty much kills Gaz as if he's putting down a dog, which would make my blood boil). I thought of Price's death as bittersweet because of his redemption in actually having his sort-of-nemesis finally killed after failing the first time.
I was also sad that I would not be able to see his amusing facial hair in Modern Warfare 2.
Aerith's death was more of a shock than a tearjerker. The first time I saw it, I said "what the fuck? How?" rather than "WHYYYYYYY NOOOOOOOOOOOOO"
Oh, and Max Payne 2. MONA SAX DIES, YALL. (Max isn't really that great at keeping his loved ones alive. In fact, I'd say he downright sucks.)