I know the game gets more than its share of good press around here, but the answer to this question is still Dark Souls.
Ni No Kuni (This one has an almost unfair advantage)
http://www.youtube.com/watch?v=7bE825afItQ
I don't care if you liked the game or not. The intro to Bulletstorm was freaking awesome. It got me really pumped to play. Too bad it didn't deliver as much as the title screen did.
So I totally agree with Mass Effect, Skyrim, Starhawk, and Double Dragon Neon.
But, I'm sorry, I'm gonna have to go with Heavenly Sword.
http://www.youtube.com/watch?v=ZDSPm8xkl1Q
Journey's start screen has no music... That's the intro. Beautiful, but not what the OP meant.How has Journey's opening not been mentioned? For shame...
http://www.youtube.com/watch?v=TLfj3pAlrs4
Folklore - The Mysterious Door
RIP, GameRepublic. They made a shit ton of mediocre games, but Folklore was something else.
As I said two posts above yours, that is not the title screen music.
My pick too
The Witcher 3's main theme hit YouTube several weeks ago and is great (assuming it's going to play at the main menu).
Fallout 3 was pretty memorable too