RDR's narrative? Good? eh... I'll agree to disagree if you will.
To me, any game can have a good plot. I thought Absolution's story was endlessly entertaining for a B-movie. With great acting and good enough writing in a wrestling heel sense. Lollipop Chainsaw is entertaining as I walk along. Spec Ops The Line, too. Ghost Trick had hands down one of the best plots I think I've ever seen in gaming. System Shock 2, Bioshock 1, 2, and Minerva's Den... I won't pretend that my favorites are all platonic ideals, avoiding cheesy poignance and heart string plucking but I think there are lots of games that have good stories and good gameplay.
If you're looking to "blend" the two then you need to think really, REALLY hard about what you are asking. That's not a simple question and often involves you staring at the screen yawning for far too long while the game bleats simple, unimpressive truisms at you (Oh hi MGS2 and Max Payne 3.).
And I'd rather have awful plot and great mechanics than any alternative. Give me a one shump and that outweighs a thousand of your crummy dating sim VNs. (Okay at this line I'm full into trolling. I can't help it.)
Haha, yeah I know that you don't like the story in RDR, but I thought it was the best story I had played in an open world game at the time, so I guess it has a special place in my heart. Since then, I think I prefer the ridiculousness of SRTT better anyway...
I do not count MGS2 or Max Payne 3 as good blends; I hate having story crammed down my throat, especially when I despise the characters and the overall cheeseball plot. I think my biggest pet peeves are stories that take themselves too seriously but are full of ridiculous situations and plot holes (like Heavy Rain and MGS3), though this may be ironic for you since I think that's how you feel about RDR.
Ghost Trick is an excellent example. This was my surprise favorite hit last year, and I agree it has an excellent mix of unique, challenging gameplay with a great narrative. Bioshock is another good example... why didn't I remember that one...
I'm able to overlook bad narratives sometimes if the gameplay is good enough, but there's something about pandering games like Bayonetta and Lollipop Chainsaw that I can't enjoy (even though I completed Bayonetta, I had to eventually start skipping all the stupid cutscenes). I prefer games with good narrative and acceptable gameplay, but I do admit I don't play a ton of adventure games since I find most of the puzzles rather obtuse and the barrier to entry quite high. Some may bemoan the simplicity of TWD, but it allowed my wife and I to focus on just making dialogue choices and enjoying the story