SPOILER ALERT! An article about game endings naturally spoils the endings to games. I know that there are more that should be mentioned, but there are quite a few games that I have never played, finished, or just don't remember enough of.
Mega Man 3
This ending touched me as a young child. This is significant because I swear that most of all NES endings consisted of nothing more than a spash screen with "THE END" written in coloful letters. Protoman redeems himself in the end, saving his brother from the debris, Mega Man gazes at Proto Man's sillouete in the sky...beautiful! Not to mention the music, the music is delicious.
Final Fantasy VIII
Final Fantasy VIII is the red-headed runt stepchild of the Square universe. But, despite whatever you may feel about the game the ending is awesome. It's my favorite Final Fantasy ending by a huge margin. It wrapped up the story with a touching 15 minute FMV sequence. The orchestrated version of Eyes on Me and the Final Fantasy theme were perfect.
Silent Hill 2
This game has no heroes. People don't get sent to Silent Hill because they were being good... James is a sack of shit who murders his dying wife, not out of mercy, but out of his own selfishness. I know that there's a happy ending, but the In Water ending feels most canon to the story. A tragic yet appropriate end to one of the finest game stories ever told. Oh: and some of the other ones are just fucking funny (YouTube the dog and alien ending).
Metal Gear Solid 3
A poignant ending that sets up Big Boss as a villain. Big Boss saluting The Boss' grave as a single tear rolls down his cheek, so sad OMG. Snake succeeds in his mission and is rewarded with medals/titles despite the fact that he has, effectively, lost everything. You are forced as a player to execute The Boss, who has been a mentor and a mother figure to snake for over a decade. Despite her vision and her sacrifice, she dies a traitor in the eyes of both the US and Soviet Union, for nothing. A true patriot.
Portal
Oh my God. I don't think a more perfect ending exists. Anywhere in any media genre. I mean, that song...There's no reason why any of you should not have played Portal.
Metal Gear Solid 4
I am still fawning over this game and cannot give a fair opinion without gushing fangirl goodness. The microwave corridor...the Ocelot fight depicting all four games...you don't know how hard I mashed the triangle button in the corridor. I am a huge sucker for cheesy wedding endings so screw all of you who didn't enjoy it! I would be lying if I said that I didn't tear up when Otacon says, "Snake...had a hard life" OMG so sad ;_;. Big Boss CQC's Old Snake into a hug, aknowledging him as a son.
Other special moments
Additional fond memories.