Maybe not the "greatest" ending in the history of games, but Silent Hill 2 gets me every time, even when I think about it I kinda get a lump in my throat, and for a game to accomplish that is saying a lot. It's nothing more than a voiceover, but man does it hit the emotion button.
I'd have to agree that Portal's ending is pretty epic, too. I've always been a firm believer that when you finish a game, there should be a sreally cool payoff, and Portal did that most impressively.
La la la la la la la... Still aliiiiive...
Shit. Now I've done it, that's gonna be stuck in my head for the rest of the night now.