The first Max Payne, love the noir movie vibes with the gangster families and all that stuff. The game's very well written and the comic-esque story telling is great and makes it even more fun to follow.
LA Noire was very interesting to play through. There's a overarching story but what I really enjoyed was the story of each case. Game's pretty unique, definetly recommend checking out. Great ending as well imo, quite ballsy.
Asura's Wrath, play it one chapter every day and it's like watching an anime. Cool story about doing anything to protect those you love.
The Yakuza games can have nice stories as well, very entertaining, although some of them have some twists and turns that I would never consider an example of good writting (rubber bullets). Still, their stories are super fun, with a lot of drama, action, comedy, romance and everything in between. Definetly recommend those and, if you want something more serious, check out the Judgment games as well, which I actually prefer over Yakuza when it comes to story.