So, what kind of think do you like to see when you fail in a game? Try starting with Castlevania.
I've always liked the one in Lament of Innocence. Sure - it's pretty simple, buit reflects the conditions you died in and it's not too long. I especially like Walter and Death's victory taunts.
"GAME..OVER!"
"Now you join her"
"Death overcomes all"
I guess when you fail for the last time, you'd just like to continue the game as soon as possible. Therefore I'd say Dracula's Curse, because it's quick and simple like: "That's that. Continue?". The music also reflects this, being epic at the same time.
I've always been of the mind that a simple Game Over is good enough, though I hate seeing those screens unless I finish a game. Because if not, it meant you failed.... miserably.
I just remembered the Game Over screen from TMNT 2: The Arcade Game, it was plain black screen with Game Over text in rainbow colours. With a Konami-like theme, even. I remember it well, because the exact same screen pops up after you beat the game. So wether you failed or not, you still get the same Game Over Screen.
As for my favorite Game Over -screen, I think Bayonetta has a good one.
Can't have a Game Over screen topic without mentioning Silent Hill. Here's what's probably the creepiest in the series:
https://www.youtube.com/watch?v=KTwZ-vvnbps