Hard question! I guess Frog & Magus plot from Chrono Trigger is one of the coolest in RPGs. Ershin from Breath of Fire IV is another one which I'll remember for a long time. Vivi from FF9 as well.
I got a good laugh when I read Sephiroth, but I cannot really say that I disagree. There are so many good ones out there that is tough to pick one, but anyone with a good finishing move is a bonus. I'll just add another vote for Sephiroth.
I may get a little hate for this, but I'm currently playing Mass Effect so my choice would be Shepard. I feel like in terms of character story, it's definitely among the greatest stories in gaming, but what's making me say Shepard is that you can forge greatness any way you want it and that's really cool IMO.
I actually wasn't very fond of Sephiroth. While I don't hate his character or anything, I wouldn't really agree with favorite :[
Quina Quen, from Final Fantasy IX. No angst, no moral quandaries, no significant issues regarding the end of the world, ambiguous gender, some hilarious cut scenes, and a fairly decent moveset. Though I could lose the random damage with the forks. ... what? Don't look at me like that.
I feel very connected to Vivi from Final Fantasy IX. To quickly sum up his story; he is a manufactured humanoid weapon of destruction with a short lifespan. Something happened to make him (and a good handful of other weapons) have a change of heart about destroying things. Throughout the story, Vivi knows he will die one day, so he questions his own existence at every turn. I won't spoil anything about what happens to him, but you learn so much about humanity and coming to terms with things. He was such a well-written character and still holds up today.