Man, this brings back memories. I've got quite a few for you.
Spiderman 2: Tried playing it again after not doing so for about a year. I climbed to the top of a tall building as soon as I started it up and jumped off intending to begin swinging through the city. Problem was, I forgot which button you use for the web and couldn't remember in time to save him. Even worse, I hit the pavement right in the middle of a gang fight.
Smash Bros 64: I'm better by far than any of my friends at this, and when I have multiple friends over I often let them fight against me on a team and I still win. However, two people I know have been playing together a lot, and while neither of them are very good alone they somehow managed to learn some amazing teamwork skills. I didn't know about this, so I challenged them as usual. One plays as Kirby, the other plays as Captain Falcon, and I use Fox. Match starts and we fight for a bit as usual. They fight well, and we all end up with one stock left. I jump at them intending to kick both of them since they're standing together. Both of them dodge away in opposite directions so I land in the middle and am stunned for a second since I screwed up the kick. Both then step forward and pummel me with their standard A combos, then Kirby hits me upward with cutter while Falcon starts a Falcon Punch. Falcon hits me with it as Kirby brings me back down and ends the match. That was easily the most embarrassing loss I've had and I swear they'd been practicing that exact combo just for me...
Hey You Pikachu!: I've played the game...what more do you want?
Zelda, Wind Waker: Got to the point in the game where you're climbing a ladder and there's fire traps along the way. My first thought while climbing it was "hmm, this section looks burnt..." and of course, I stop to investigate. You can guess how that ended. This seems to be a common mistake, as my girlfriend told me she did exactly the same thing and I've seen it made fun of online.
Armored Core 3-Silent Line: This one happened to someone else, but it's funny enough that I have to post it. I'll do my best to explain it so that those who haven't played can appreciate this while still keeping it short. I start a match with a friend on the bridge level (long platform, bottomless pit underneath). I'm using a lightweight mech wielding the Karasawa (one of the heaviest weapons in the game) and a blade. My friend has a lightweight that has back weapons and a blade. We both boost under the bridge and begin shooting at each other. I fly above him and drop my Karasawa since I just ran out of ammo. When I turn around, he's out of energy and is falling to his death. He starts laughing hysterically, and when I ask what happened he just shakes his head and starts the replay. What happened is this- as I dropped my weapon, he tried to attack me with his blade. Since the blade uses the same button as picking up a weapon, he grabs the Karasawa that I just dropped on his head instead of attacking. Because the weapon is so heavy, he immediately goes over the limit for how much his mech can carry and is no longer able to fly well enough to get back onto the platform, making this one of the strangest and most embarrassing losses I've seen happen in a game.
I'll post more later if I can think of them. So far this has mostly been about horror games and I'd like to mix it up a bit.