Well, it depends on what you're after I suppose. I generally dislike adventures for quote-unquote "serious" games. Dunno what else to call them. However for a good time for all, they can be great. So most of my suggestions will be AD&D and forward, because if I'm playing realistic medieval stuff or even sci-fi or horror of whatever else, I generally won't be using adventures.
As you can see in the Let's Play, I am currently running
Way of the Wicked by Fire Mountain Games (for Pathfinder), and I must say, it is fantastic. However search for "best pathfinder adventure paths" and you'll most certainly find the cream of the crop of those. And they're really good. Jade Regent, Kingmaker, Rise of the Runelords (the new one), they're all good.
I also agree with others who said
Tomb of Horrors. However I can't believe no one's mentioned
Whiteplume Mountain yet. In some ways I actually think that module beats Tomb of Horrors at being Tomb of Horrors. Both have 3.5 make-overs.
Also worthy of mention is Dungeon Crawl Classics' "
Crypt of the Devil Lich":
http://www.goodman-games.com/5012preview.html <---- also 3.5.
It's a modern take on Tomb of Horrors, with the difficulty ramped up. A personal favourite of mine is
The Cormyr Trilogy, but that one requires a GM willing to do a lot of preparation. If you got that, it can be a great Forgotten Realms romp & roleplay. I can also recommend Expedition to the Demonweb Pits in its 3.5 edition, but that one also needs some serious GM work to shine.
In terms of modern takes on the classic module though, NOTHING beats
CITADEL OF PAIN or
THE BANEWARRENS. Seriously, play those. Entertaining, crawley, roleplayey, explorey action in a weird setting. Experienced roleplayers will wonder why all the rules just changed and what they're gonna do about it, and new roleplayers will marvel at all the weird shit. Great either way.
Citadel of Pain is for Pathfinder, while The Banewarrens i 3.0 (but easily convertible - I played it in the Mulhorandi town of Skuld in 3.5).