I hate to be Mr. No Surprises here, but, yeah -- it's
Twilight Princess. The ending of that game in particular serves as the most heart-wrenching moment that I've experienced from a work of fiction. Even setting that scene aside, though, TP is full of emotionally-resonant moments, from Midna's Desperate Hour, to rescuing Colin, to Link and Telma's "conversation" in Kakariko Village, to the recovery of Ilia's memory. 'Tis great stuff.
That being said, there are other games out there with similarly powerful moments. Here are a few...
- Conker's Bad Fur Day (that ending -- what?!)
- Gears of War 3 (Dom's death)
- The Halo Quadrilogy (including
Reach -- Kat's death and the ending of
Halo 3 stand out, especially)
- Mass Effect 2 (the most accomplished that I've felt upon completing a video game)
- Mass Effect 3 (too many endearing characters died for me to list 'em
)
- BioShock (Atlas' betrayal)
Oh, and
Ocarina of Time and
Skyward Sword get props for being so freakin' whimsical.