There are actually quite a few good space titles in various Genres.
In 4X I would definitely say Master of Orion 2, and it is truly sad that since 10 years there is no game that can match it's class, just as with XCOM.
Space Sim is imo tied between Privateer and Tie Fighter (both excellent games) - Freelancer had too little long term motivation for me. Also Privateer 2 was quite good, although it lacked the "magic" of part 1.
(True)RPG-wise it's a bit harder, since there are only a few of those (Megatraveller would come to mind), but all in all I think Star Control 2 would be a good choice, that comes close to an RPG, although I also heard interesting things about Whale's Voyage, but I never played it myself.
In RTS it's the Homeworld Series without any doubt (Starcraft would beat it, but as others said, I too don't consider Starcraft or Dawn of War (another excellent RTS) a "space" game) - although Conquest: Frontier Wars and Star Command have their merits too.
Action games wise it's a lot harder, since there are so many titles, some entertaining, some well desinged, some simply fun, but none I would put above all others.
EDIT: forgot MMOs: There is not much to choose from here really, but it's definitely EVE-Online.
