I found the ending to Sonic 2 incredibly satisfying. It was so classy, and that song was in my head for
years.
I also never get tired of Bubble Bobble's True Ending and staff roll, but that game has a lot going for it. The final boss is wicked fun, and you've put so much effort into the silly antics all along the way -- most likely with a good friend -- so you're just in a good mood the whole time. The overall effect is that you feel like celebrating with ice cream and confetti and a dance party afterwards. At least I do.
I will admit that I do get bummed when I've been sticking with a game that I only have so-so feelings for and get a lame ending, though. Sonic 3D Blast, for instance. Its music was awesome and I enjoyed it for what it was, but I found the gameplay too repetitive. I slogged through the last bit and was hoping the ending would help justify my obstinance. It didn't.
Oh, and I was kinda hoping to have my ego stroked for beating Dr. Robotnik's Mean Bean Machine on hardest difficulty. I played through each setting, and at the end of the credits for each it egged you on for the next level, saying "let's try normal difficulty!", "let's play hard difficulty!", etcetera. After completing the hardest setting I thought I'd get an extra little cutscene or something.