Generally speaking, I really liked the ending of Metroid: Other M (Wii) and Radiata Stories (PS2; the non-human side story). More than any other ending.
In Castlevania, my favourite ending is Lament of Innocence, followed by Order of Eclessia and the alternate ending of Aria of Sorrow (the one with Soma and Julius keeping a promise... I don't want to spoil, in case anyone still did not play it).