For the most part, the losing conditions in games follow a pretty standard format. You lose all your health or lives, or the time runs out, or a plot-central character dies - and then the controller breaks because it's been thrown savagely against a wall. Something about a game over screen conjures up loads of negative emotions.
There's frustration, if you've just lost a particularly close fight; despair, if it's been a million years since the last save point; or even pure anger, when the AI is blatantly cheating you out of your rightful victory. It's even worse if it happens very suddenly - all that steady progress gone in the blink of an eye. However, some game overs stand out because they deviate from this format.
Sometimes, a game has a totally unexpected or plain bizarre sequence of events which lead to a game over - and those, although still slightly annoying because of lost progress, are often really interesting.
Like how Connor dies if he doesn't skin an animal he's killed in Assassin's Creed 3 - there's a lot less anger involved, and a lot more confusion, with that "WTF" feeling making those particular game overs memorable to players, rather than just another irritable failure to add to the pile.
With that in mind, there are some very weird ways to die in video games - see for yourself.