Ah, tragedies. One of my favorite "I wonder if I could do this" topics.
A successful tragedy is a powerful thing. But it's nigh-impossible to do properly in tabletop games, because it relies so much on very particular factors working together. With a good group it can be more doable, but tragedy works best when the character tries their best to prevent it but fails, and the audience knows they're going to fail. Thing is, the character and the audience are the same, so you have to have the player know that the game is a tragedy more than anything, and just go with it. The addition of more PCs also makes things more difficult, since tragedies are nigh universally focused around single characters, which detracts from the spotlight of everyone else and can lead to slight resentment.
There are, of course, systems that try to emulate the tragedy genre, often giving the possibility of winning as well. Becoming is one game that comes to mind.
Edit: There's also a lot more about the structure of classical tragedies that just don't fit tabletop games very well, but that is a bit longer of an issue than I'm willing to go into right now. Let's just say that you need a very good GM to provide proper catharsis for the players.