Due to the list being full of spoilers for games you may or may not have played, I'm going to put the article a click away. Most recently BioShock, while a flawed game, contains two of my favorite moments in recent gaming memory. The first is when you finally confront Andrew Ryan. The game has been building up to this meeting, and finally after many difficult tasks have been accomplished you meet the mastermind behind Rapture. It's entirely out of your control. The speech Ryan delivers is excellent in pretty much every way, and the culmination of his brutal and violent death leaves you with feelings of awe, sadness, shock and even perhaps a little admiration of Ryan for sticking to his ideal until the bitter end. Also the event turns out to be somewhat of a precursor to later events.
Which brings me to the second moment in BioShock, that is, the reveal of the player. His purpose, his perceived freedom, his very being, all brought out into the open and smashed to pieces. It's a moment that sticks with me and has also shaped many of my opinions about the game. Usually video game 'twists' are lame or easy to figure out well beforehand, but BioShock's was subtle enough that it worked really well but also wasn't entirely obvious.
Deus Ex is a game I enjoy thoroughly, for many reasons, and while the game itself has its share of really awesome moments, my favorite occurs after JC is captured and put into a holding cell. You have just failed at an attempt to escape New York and must now get out of this Majestic-12 bunker. After you finally escape you encounter a familiar looking door, which upon entering puts you back into the UNATCO facility. The gravity of the situation slowly dawns on you and the game's first really cool plot twist is revealed.
Also I'd just like to briefly bring up how amazing Deus Ex is, and how sad I am that we will probably never see another game like it again. The combination of the game's quirky AI, amazing multi-pathed level design, character customization and a combination of things that not only involved the talent and hard work that went into the game, but also the technology expectations and limits at the time (most of the silly stuff that happens in that game would never be put into a game with the same tone today) put together make it so perfect. I don't really like saying that I have a 'favorite' of anything, but if I had to have a favorite game, it would be Deus Ex.
This one is sort of odd, but in college I picked up Red Faction, which is a fun game, but overall nothing special. I spent some time playing single player with a friend of mine (we would swap back and forth) one night because we had nothing better to do. For some reason, when we discovered that not only could you attach the sticky explosive packs to the guards, but they would run around in comical terror, it was just about the most hilarious thing ever. Some guy running around screaming about how he is going to die, waving his hands over his head, and finally exploding, is just about one of the funniest things I can think of.
E2M8 of Doom. Playing this level for the first time was pretty spooky. The level of course starts with you in a tiny room, and stapled to every wall around you are the broken bodies of the formidable Baron of Hell, who once upon a time tried to rip you in half and seemed sort of hard to kill. Okay, so what exactly could have killed them like this?
Meeting the Cyberdemon for the first time is probably something everyone who cut their gaming teeth on Doom remembers. Not only was the fucker huge, but he fired rockets Fucking rockets. There are a few other moments in Doom that stick with me (I forget the exact map but there's a point in episode one where the lights switch off and some walls drop and you're attacked by imps), but that one is one that sticks with me the most.
Another game that's a big favorite of mine, one of my favorite moments in Half-Life is probably the time you first meet those crazy tentacle monsters. You spend some time circling around the silo they inhabit and you hear them clanging around loudly, wondering what the hell that could possibly be. You finally get inside only to witness one of them smash through a window and impale a poor scientist and drag him to his final resting place. Another amazing moment occurs shortly after when you inevitably realize that they track you not by sight, but by sound.
Similarly, another Half-Life moment I have always enjoyed was when you first get into Xen. I know many people hate Xen (even though, if you play it now, ten years later, it's quite easy), but the presentation of it was spectacular. It was supposed to be a foreign alien world, and it succeeded at that in every way possible. Granted the weird factory portion wasn't overly great, but the initial shock of being pushed into this world and the following "oh fuck" when you realize what you have to do are pretty cool.
Shadow of the Colossus is a beautiful game in pretty much every way, and the moments when you climb and finally defeat every single colossus are amazing. Inevitably you have spent some amount of time struggling to defeat this beast when you deal the final blow and he crashes to the ground and the game's stellar soundtrack kicks in and you wonder if you are really supposed to be doing this. It's quite epic and the combination of the colossus finally falling and the music only serve to make it even more so. I would say that perhaps not until the third colossus do you actually get a feel for what the game is, due to him not only being much larger than the previous two but also requiring multiple steps before you can defeat him (setting the stage for things to come).
Portal is a game which I would put into being a fully memorable experience, but if I had to point to my favorite moment in the game it would be when you leave the obstacle course world and enter the real world that exists behind everything else. Glados is pretty much done with you and is about to incinerate you when you cleverly escape into the real world. This was sort of the moment in the game where many of your suspicions are confirmed and also the moment where you seem free of the clutches of whoever has been talking to you this entire time. No longer are you in her control, and now you get to do things on your own terms, as is illustrated by her frustration due to your escape. Meeting your maker, as they say, is also quite memorable, but the moment I just described is easily my favorite in the game, which as I said houses a large number of memorable moments.
Finally defeating the final boss in Sonic the Hedgehog 2. back before Sonic spoke or started starring in crappy 3D games there existed a series on the Sega Genesis which was awesome. While I don't think Sonic 2 was the best one (that title goes to Sonic 3 & Knuckles, which I guess is kind of unfair since it's really two games), the final boss was something of an exercise in frustration. Not only are you thrust into a level with zero rings, but you have to defeat a metal version of yourself followed by a crazy Dr. Robotnick power suit. The boss itself wasn't too difficult to avoid, and his patterns were pretty predictable, but you were only left with a few quick moments in which you could actually attack him without getting killed. I don't remember how long I spent trying to defeat him all those years ago, but I do remember it took me quite some time, and that I was extremely happy and satisfied when I finally beat him. Now I can just hope that maybe one day Sega will make a good Sonic game again. I'm not too hopeful at this point though.
Finally, Psychonauts was another game with quite a few memorable moments. However my favorite overall as the acclaimed Lungfishopolis level. Raz is transported to the mind of a mutant lungfish and finds himself giant monster in a city populated by intelligent lungfish. The realization that overcomes you when you figure out what's going on is amazing, and stomping around the city is probably the most fun I had in that game (which is another case of a flawed game having some amazing moments).
That's all I have, though I'm sure there are more, but hey, at least video games can have cool moments. Hopefully there will be many more to come in the future.