My favorite yay moment ever has to be the ending in Prince of Persia: Sands of Time. It's as simple as it is amazing. I love how he dramatically runs off leaving Farah with something of a "WTF?" expression, and then the beautiful song as the credits roll. It's just perfect.
I agree that Ryan's scene was intense, but my yay moment in Bioshock would have to be when Sander Cohen snaps and the subsequent battle with all those splicers with Tchaikovsky's waltz in the background. That was crazy.
Like someone said up there, HL2Ep2 is pretty much a 6-hour-long yay-ride; but if I have to choose the yayest yay moment, it would be in the "This Vortal Coil" episode, when you fight the swarms of antlions alongside the vorts and those Abbott & Costello knock-offs.
And I remember Metal Gear Solid 2 having a bunch of great yay moments too. Taking down the harrier sort of single-handedly, and then the cutscene that follows, with the Ray coming up from under the ocean, and that whole Vamp's running-on-water-and-up-the-wall thing... that entire part was pretty cool.