"Hardest game evar!" is a subjective thing, honestly. Some games are easier for some people while others are harder. It all depends.
For example, bullet-hell games. Some people could never play them. They just couldn't adjust to the play style. While others master them to degrees that seem almost depressing.
Same goes for RTS games. Some people will find games like StarCraft exceptionally hard while others can dominate several players at once.
It's all relative.
As for me; and I've played a LOT of hard-as-hell games; the hardest game I've ever played was MechCommander 1. That game, on any difficulty above "normal", was damned near impossible to beat. I don't care how "good" or "tactical" you are, when a Raven scout mech can take down Atlas assault mech without trying, that's a God damned hard game. It's one of, if the only, game I've ever
needed cheats to beat.
Still, it's "theoretically" possible to beat the game on the hardest difficulties. Even if I've never seen it.
[as a side note, I don't count piss-poorly made games that have some game-breaking bug or terrible controls in the "hard game" category. Bad game design and bad programming do make a game "hard", they just make it a shit game. I'm looking at you Dark Souls. Seriously, screw you From Software. You always were and will always be a terrible developer.]
Combine Rustler said:
Team Fortress 2 when the enemy team has either a proper W+M2 pyro or one of those fucking megasnipers. You get in line of sight, you're dead. Fucking bullshit.
I hate to admit it, but I'm sometimes the latter. There've been plenty of occasions on maps like 2Fort where I kept the enemy sniper perch and choke points clear of everything. At times I actually felt so bad for the other team that I changed classes; usually to Spy. (course then within minutes half the other team goes Pyro and it's a clusterfuck of fire and fake spy corpses. hate that.)
[edit]
Might I add that, besides what I said above, some of the hardest games I've ever played were competitive multi-player games. Like Counter-Strike: Source, Quake 3, Left 4 Dead, and StarCraft.
I've competed in several high-tier tournaments for those games over the years and, as a result of the skill of the players I faced (team or solo), they made for the some of the hardest game-play experiences I've ever had. Not sure if you can attribute that to the "game" specifically, though.