Warning: tl;dr spiel ahead. Skip down if you just want my nominations.
I'm going to be a complete storyemofag and give a serious answer. I can't think of any 'great' crpg romance, but there's a few that have been good and a lot that have been terrible. The worst have been the Bioware ones, consistently. Every single one is a pathetic fantasy fulfillment. Firstly, they're almost all massively predictable - when did you ever play a Bioware game with a romance without knowing exactly how it was going to end up? There's never any question that they're going to get together after the token saving of the damsel in distress. And they're all the kind of thing that a dateless and lonely teenager might think of as his/her fantasy before they've ever actually had a relationship.
I've got a theory on that. Folks who are miserable about they're own love lives enjoy cliched fantasy-fulfillment because it's a replacement for their own pathetic state. Added to that is that such people, as a broad and probably unfair generalisation, often lack a lot of opposite-sex friends and so find it easier to swallow the games' cliched depiction of a relationship. Those who are in, or have had, long-term relationships, or even regular friendships with the opposite sex, simply can't swallow those depictions as 'real' relationships. There's nothing attractive to them about hooking up with a childlike wingless elf, or 'no-really-I'm-a-bad-girl' Morrigan, because you know how fucked up that kind of relationship would be in real life (not to mention a tad paedophilic, with the tendency of gaming females to act like teenagers regardless of their in-game age).
So people in the 2nd (overly generalised) category tend to like, to use Avellone's term, 'hatemances' - stories of doomed relationships and fleeting stands, because for someone in a happy long-term relationship, the fantasy of being single, emo-tastically in doomed love etc is appealing, even if you wouldn't want it in real life.
So with that aside, my nominations are:
(1) The threesome and the gay sex in Bioware's Jade Empire - what? A Bioware romance just after I ripped on them? Well what I like about the threesome and gay sex options in Jade Empire is that there isn't any pretence of 'romance'. Nope, it's just the hero getting laid. No improbable 'omg we've only been travelling for 4 days but I love you!' schtick, just 'hey, I wonder if I get BOTH these chicks into bed!'. If you can't do it well, at least don't take it too seriously. Bioware should have taken the same approach in Mass Effect - it would have fitted Sheppard perfectly for the 'romances' to be replaced with James T Kirk-esque 'strategic diplomacy'.
(2) Ravel/TNO: already been mentioned. A romance that has absolutely nothing to do with inspiring desperate 14 year olds to write fanfiction. That in itself is remarkable - add that it's one of the only game romances that ACTUALLY HAS SOMETHING TO DO WITH THE FREAKING PLOT, and subtley pulls off the 'epic love story over thousands of years', and it's lightyears ahead of anything else in gaming.
(3) Visas/Exile: Most crpg 'romances' fail because (a) they're too afraid to be anything but wish fulfillment, failing to realise that romance plots can't work unless there's at least a possibility of tragedy, and (b) they try to cram into a several day/week timeline an emotional connection that would ordinarily take many months to foster. Visas/Exile works because it never gets off the ground - there's no relationship per se, just an acknowledgment of what might have been under better circumstances.
(4) TNO/FFG/Annah: I'm including the two of these together, even though Annah falls squarely into the 'bad teenage fanfiction' territory.I wasn't a fan of the ingame 'romance' either - a bit heavyhanded and out of place in a subtley written game. As hatemances or doomed almost-romances, though, they work well. Every member of TNO's party suffers a torment that he's caused. Ravel points out that falling in love with TNO IS Annah's torment. But it isn't until the end of the game that TNO's curse upon FFG materialises (she has plenty of torments before then, but they all predate TNO's involvement) in the form of her swearing to return to hell in an impossible quest to find him.
(5) Under a Killing Moon (adventure game): Forgot her name, but you rescue the girl from assassination, head to a moonbase and take out legions of bad guys without weapons, fighting ability or basic hygiene, and at the end she hooks up with your millionaire arrogant git of an ex-boss. Bitch.
(6) Raynor/Kerrigan (yes, RTS): Ok, I was a bit younger when SC first came out. And it's been so overdone and overcopied that it's a bit lame now. But before all that, Kerrigan was popular for a reason. Mengsk's betrayal and Kerrigan's re-emergance (and repeated sparing of Raynor) were fucking awesome the first time I played the game.
(7) Alleycat / the female housecat in Alleycat (platformer): Ok, so it was a 1983 Atari game. With no dialogue, and the romance consisted of the player's alleycat sneaking into the housecat's room, out-manouvering the other 'suitors' and getting some feline action. But there's not many games around where getting laid is not only a sidequest but the game's main goal (there's multiple other challenges, but getting laid advances you to the next level).