Best non-RPG ending that I can quickly think is in Max Payne 2, it's so bittersweet. And again, the song also was great. Poets of the Fall - Late Goodbye
+1 Max Payne 2 was one of the first games that came to mind when seeing this topic.
For RPGs I found The Witcher, BG series, PS:T to be complete games.
NWN 2: I actually enjoyed it greatly but I had to drag me through the end. So perhaps not a terrible ending but not too memorable either.
(BTW: nice to see a fellow Dane on the forums )