If you mean the most well-fitting track I'd say those from the Metal Gear Solid series. It's not the kind of music you listen outside the game, but it contributes greatly to the game's atmosphere.
If you mean the "best" song, as a song that I would actually put on my mp3 and listen to it even when not playing, I'd say Fallout's. Any Fallout will do. Really.
Epic rush battle music? Battlefield 1942 / 2's theme song. "The real picture" kind of atmosphere can be demonstrated a couple of ways, when it sounds revolutionary then Crysis 2's theme can suit this -- especially if you put it in context. (This is the ending of Crysis 2 so yes -- there are spoilers, you've been warned. That, and apologies for being unable to find a video without the gameplay at the start -- not bad given how good the graphics is anyways )
If you want inspirational music... no idea, honestly. Trailers have this sort of music the most if you ask me, for example Mass Effect 2's trailer music.
Two Steps from Hell - Heart of Courage is the music, used in other trailers -- kind of overused if you ask me but nonetheless, awesome.
Yes, the beginning of the music is sad as well... When I go onto this video I listen / watch it all before I go to another. <3
One more thing - it's cliche like hell, do I care? Yes, it's darn awesome. You realize cliches have been hated on so much that they usually do the exact opposite of cliches now? Having one every once in a while is outstanding.
Truthfully there is no best video game theme song, even for atmospheres you're after variety helps, those are ones that stuck through.
Fable I music is awesome I might add -- hell, that could well be one of the biggest reasons of why it is considered better than 2 and 3!
It does give a lot of atmosphere, as kind of silly it may sound... Love it.
