Both Gothic's has great soundtracks (but I don't know whether I think this because I love the games or if they're really that good).
As many of you have mentioned already, Deus Ex has a really memorable soundtrack.
GTA: Vice City and GTA: San Andreas has awesome soundtracks too, but I don't know if they count since they're just radio stations playing famous songs.
The best soundtrack I think I've ever heard in a game must be from the game Bully (Canis Canem Edit). It's a custom written soundtrack, composed and played (all instruments!) by one guy. But he does an awesome job and hits all the key moments with such precision I've never heard in a game before.
Oh, and an honorary mention has to go to Boiling Point and STALKER too. Both had some really original and atmospheric tunes.