I can't believe nobody's mentioned the Precision Shooting kills on Stranglehold. There's something fantastic about seeing a man clutching at his face as a bullet tears through his cheek, all in slow motion.
Zapping the water that a group of enemies are standing in in Bioshock.
Stealth stabbing someone in Assassin's Creed, then doing the same to the guard who wanders over to investigate the corpse, then stabbing the guard who comes to investigate HIS corpse and so on. You'd think one of them would notice the suspicious, weapon-covered man in white walking away from the scene of each murder.
My favourite death was in Resi 4. No, not the chainsaw deaths (they were fun to begin with, but if you played the Mercenaries game for any length of time they became exceedingly frustrating) but when I was fighting two of the El Gigante things.
I dropped one into a pit of molten metal, and moved on to shooting madly at the other one. I was dodging around, keeping the pit between me and him, when suddenly the one I'd sent to his firey death rose, roaring from the pit, molten metal cascading from him, grabbed me, and dragged me back down with him.
It was fantastic because it was so unexpected.