Here's a few I have played and I thought were pretty cool.
Ripper
There are a ton of famous actors in this game, including Christopher Walken. The most fascinating gameplay aspect of the game though is that the game randomly decides who the killer is at some point and adjusts the story accordingly. This way, you never know who the Ripper is for sure because it will be different each time you play through it.
The Last Express
This game takes place on the last orient express. You are suspected for murder and you are trying to escape on a train. The man you were supposed to meet up with has been killed. What's really cool about this murder mystery is that not only can the story be affected by your actions, but the game occurs in real time. Conversations among train passengers continue whether you are there to hear them or not. There is a concert duet which lasts for 20 minutes real time and you can choose to spend your time simply sitting and listening to it in its entirety, or carry on with what you were doing and pop back in later.
Missing Since January (aka In Memoriam)
This has you trying to decipher the riddles of a serial killing mastermind. He has given you clues in the form of the CD-Rom you put in your computer. It's up to you to figure out what happened to the victims. This game does away with the fourth wall, forcing you to use real websites to track down the killer and sending your e-mails at your real e-mail account from the killer himself.
Persona 4
This is a role playing game, but the storyline follows a series of murder mysteries that involve people from your small japanese town. I don't think the story will ever let you botch up your detective work, but I love how you have to balance your time between your personal life and Scooby-Doo-style adventuring.
Tex Murphy: Under a Killing Moon
The Tex Murphy games take place in a sci-fi neo-noir setting with the wise-cracking Tex Murphy shlubbing his way through life and trying to solve some cases. It's a rare case of FMV adventure gaming done right.
