In recent memory, God of War 2 on Titan difficulty had to be one of the most frustrating, yet rewarding, games for me. I spent 4 days on the Theseus boss fight, and when I finally beat him and Kratos was slamming a door on his head, it felt so rewarding. The other part in that game was the endurance hallway before Clotho- took me 2 weeks. Looking back I feel pride that I was able to do it, but not sure I want to try again.
The last mission on Driver for the PS1 was nearly impossible for me. I made it right to the doorstep of where I needed to be, but ended up dying. I tried again many, many times, but was unable to do it. I ended up using a cheat code for invincibility, finishing the game, and never playing another Driver game again.
My most frustrating game for me was Rush-N-Attack on the C64. The game was badly made, hit detection was awful, bad graphics, etc. My characters knife would be poking out the back of the enemies, but I would still die. I smashed a controller over this stupid game.
And in Castlevania, the Grim Reaper boss fight was pretty much impossible.
I always was proud I was able to beat this game. That Grim Reaper fight was hard!