I'd have to say Earthbound's ending was pretty touching.

Out of the more recent games, I liked Max Payne 3's ending. I'd say it was a good way to close the series, unless there will be a Max Payne 4, of course.