There's way too many awesome game tunes to really go into from my perspective, both in old games and in modern games, but I'll mention a few of my favourites.
Batman on the NES, along with virtually every good and bad game made by Sunsoft had some of the best music on the system because they put very percussive drum and bass effects on the single digital sound channel supported by the system.
The Castlevania series itself, across multiple systems has some incredibly energized music considering the nature of those games, and is perhaps the only reason I have any interest in them as I'm more fond of the music in those games than the gameplay. ;D
One Must Fall 2097 had some awesome music for the time considering it was all digitally tracked. (I actually have the original soundtrack files, can't remember how I obtained them.) But I must also mention that its sequel, OMF Battlegrounds, despite not being too great a game had a dynamic digital music system that would actually run into different parts of a tune depending on the overall feel of the current game.
The Mega Man series has always been hit and miss, with lots of great tunes but lots of lousy ones too. Mega Man 2 and 3 are nothing BUT great music, (the title sequence for 3, rarely listened to for its entirety, is actually really long and fun), and the music for Mega Man Battle Network on the GBA is actually very catchy overall.
Many of the Final Fantasy games have great music too, with my favourites for music being IV, VI, VII and VIII. (FF4 is actually my most favourite RPG of all time, followed by Dragon Quest II.)
In terms of modern games, much of my favourite music is coming out of Nintendo, especially considering how a lot of 3rd-party companies aren't developing their own music for their games anymore. Super Mario Galaxy and the Super Smash Bros. games really hit the mark, not only with original tunes but with remixes of old tunes as well.
Since I grew up with video game music, not traditional lyrical music, I learned to beatbox early on, so to me, the mark of a good game tune is if I can sing the entire thing and actually have fun doing so. ;)
--- Kris Asick (Gemini)
--- Pixelmusement Website: www.pixelships.com
--- Ancient DOS Games Webshow: www.pixelships.com/adg