I don't feel like hammering out a distinct order, but here are some gems:
Metal Gear Solid 3 - Probably the best video game ending I've ever witnessed, and the boss fights are awesome even if most of the villains themselves aren't as memorable as those in the first MGS
Deus Ex - This game doesn't hold up at all today, but man when it released it was absolutely mind-blowing.
MGS3's ending blew me away. Looking at that series, I think that was still the best one followed shortly by Phantom Pain. I felt Snake Eater's story was more grounded than the other ones, at least Sons of Liberty and Guns of the Patriots, and it probably helped that I played Subsistence so I didn't have the shitty classic MGS camera.
Shame about Deus Ex. I played that game when I was about 12 and it stayed atop my favorite games ever for a long ass time. Like I said before, I still think it is, but I just know it hasn't held up over time so I'm afraid to go back and play it again.
We had everyone play Goldeneye back in grad school. Even the chicks including the hot ones packed our pad for goldeneye partiesI wouldn't say these are the greatest games i've ever played but as far as "holy sh*t" moments throughout my gaming life:
N64 and Goldeneye - Hours upon hours spent playing multiplayer.
PS2 and NCAA Football 2003 - first experience with a next-gen sports game. I remember i was completely blown away...that moment led to a love affair that lasted years. Damn you, NCAA!
PS2 and GTA 3 - I basically lived at my friend's house for an entire week playing this. We would literally play it in shifts, Nam style, with about 8 other people crammed in a basement. Sleep, wake up, play for 3 hours, pass the controller to the next player, rinse & repeat.
Now, that i have over 20+ years experience gaming, there is only one game i can go back to on old consoles and still love...Super Smash Brothers on N64. Still amazing that a game so simple in nature can lead to unique, one of a kind moments, over and over again. When playing this with skilled players, there are few games that recreate that kind of intense competition.
http://nerdist.com/final-fantasy-ix-coming-to-pc-and-mobile-platforms/
Apparently Square Enix read my post above.