Ok, I don't post a lot, but had to get in on this. All time Classic Intro, definity Mule. Love that song. Oh, if anybody knows where I can get an audio file of it, the original, not one of the remakes that's all I can find, I'd be grateful.
For more modern computer game, I gotta go with Mechwarrrior 3, absolutely hands down, the best Intro I've ever seen. It's the ONLY intro that I thought, "Make it 2 hours, gimme some popcorn, and I'll pay $10 to see this in a theater." It absolutely captured the Mechwarrior vibe.
On an off-topic, but related note. Music on a title screen, Final Fantasy IV. Not any of the remakes, the SNES version. Much like Mule, this is a game I would turn on and leave running just to listen to the music at the title.
http://www.youtube.com/watch?v=y8gOOTAWKY8
The very first piece is from the Title Screen. Oh, another piece of music that I think is just awesome from FFIV, it played in, I believe caves on the moon. It's in this list of Top 10 favorite music from FFIV, at 3:38.
http://www.youtube.com/watch?v=hu5gNfFAIIQ
Anyway, had to get in my 2 cents, and say thanks for all the memories from these links 
Ripper
Ooh, Had to add to awesome Title Screen Music, Civ IV. A Neat tribal drum kinda piece. Another one that I like to turn on and just listen.
