Not super old but...in the original Rollercoaster Tycoon, when a rubber raft would come off a water slide, it would explode in a huge fireball just like a coaster train. What WAS in those slides...nitroglycerin?
3 pages of game death and not one person mentioned Splatterhouse? I loved playing Pickaxe Pete for the Magnavox Odyssey 2 so in turn I love the way he died... Gunfight for Odyssey was a good death as well...
Mission Impossible (and its sequel) remains one of the best games for me, and it definitely had some of the best sounds I have ever heard. I even have audio clips, but I don't know how to embed them here, so you'll have the FFW on the clip to 2:20 http://www.youtube.com/watch?v=PrViE9zT1wg&feature=related You might also recognise the death scream from Beach Head.
Not so old school, but I always loved Baraka's fatality where the body slid down his blades after being impaled. But as far as favorite goes, I have to agree with the earlier post about the splat from the Atari Frogger.
The most memorable for me is and will always be in the OG Zelda, the screen turning red and Link spinning around in circles and blinking out to black. Anyone ever play Solstice for NES? The shrieking when you die used to scare the shit out of me when I was a kid. Good call!! Splatterhouse came to mind instantly when I saw the thread title. There are too many sick enemy deaths to even call a favorite.
Colecovision Smurfs- a crunchy, short digital fart sound and your character goes horizontal. Brilliant, perfect.
I was just coming back to post Zelda as well, not only was his death sweet, but there was also sometimes a cool fairy resurrection... Also the points leading up to his death with the hearts blinking and that unforgettable beeping noise as his heartbeat wore out...
cant remember if it was the same in the first one, but Sonic the hedgehog 2 when you die underwater. That song that keeps getting faster is so nervewracking and then when you die and he makes that hickup noise and crazy expression. Love it!