Cave Story has some fantastic music. The Far Cry 2 soundtrack is great as well mainly due to authenticity (Baaba Maal), but I don't listen to the actual game soundtracks very often. For most adventure/RPG games I tend to listen to Pink Floyd, spacey stuff like that, but depending on the game I usually just listen to whatever's on my iPod at that time, though I have done a couple of gaming playlists-for Unreal I listen to Muse, At The Drive-In, Machine Head, Fucked Up, Unsane, System of a Down.
And of course some of the songs from FFX. The names escape me but the music for some of the more cinematic cutscenes I really loved.