From my header, it is easy to tell that Mario games, Assassins Creed and Dragon Quest 8 are amongst my favourite games, I do prefer games with fantastic stories or fantastic game play. Both together are preferable, yet rare.
I have made a few mistakes in games, where I've died but not like "oh I just died", like it was a surprise and I was like "oh snap".
One of such instances was recently during a run through Resident Evil 4 (again). I was near the beginning, I had just bust open a door, shot gun in hand and there was a guy coming right at me. I've such skill as an instant head shot and his brains burst all over. Now, I was not aware that this can happen, but it did. His headless corpse kept moving forward, I've seen this before. But I was close to it, and it grabbed me and threw me to the ground. I was quite shocked and dismayed, as he had died by the time I got up, so no revenge. He got the last laugh...
Also more recently, in Brawl. I was unlocking toon link and so was facing him on the pirate ship, first time. I wasn't aware of that nice catapult at the tip, and thus when it was coming out, went to investigate. I stood right in front of it and WHAM! I got shot right off the edge. Almost died.
Countless moments I've had with oops. I'm sure you guys have had some too, no need to name them all, just your fondest or most recent.