Masks of Nyarlathotep is probably the most common choice for greatest campaign ever. Orient Express is on the list, along with some of the shorter Call of Cthulhu stuff. Crack’d and Crook’d Manse? I may have gotten the title wrong, because there are so many of these. The Condemned, perhaps. Couple more. The Burning Stars is less widely known, but often pops up in best scenario ever conversations.
Enemy Within for Warhammer.
Great Pendragon Campaign.
For D&D, Tomb of Horrors. Barrier Peaks. Ravenloft. Choose what you like from White Plume Mountain, Temple of Elemental Evil, Tamoachan, etc. Mud Sorcerer’s Tomb.
And speaking of choose what you like, if you count solo gamebooks: Sorcery!
Incomplete list, obviously, and insert inane comment about depending on taste here. But those are things around which there’s some sort of rough consensus, at least as close as you can get in RPG matters.
(just my opinion)
you have to change the names for those who can understand German though 
at least in the original print
- Tomb of Horrors is just awful and very bad
- Barrier Beaks was innovative back then … from today’s perspective it’s solid
- Ravenloft is great, and also replayable
- White Plume Mountain was innovative back then (but you won’t recognize that today) … also if you do not like dungeoncrawls, you won’t have fun
- Temple of Elemental Evil has some stuff that was just plain bad … if you take the core concept and build your own stuff, the adventure will be
better (Return to the ToEE was a bit better) - Tamoachan is solid … if you love dungeons
- Mud Sorcerer’s Tomb … is like Tomb of Horrors but beatable … so if you love dungeons it’s fine, but won’t consider it great from today’s perspective
we played that and it did not work … maybe our players / GM’s fault … dunno
(I was a player)
(again … just my opinion)
old but still good:
-
D1-D3 Descent into the Depths of the Earth / Vault of the Drow (1978)
solid
adventures
… but please do not run Q1 Queen of the Demonweb Pits after those, that is unsalvageable
… instead you could use The Harrowing from Dungeon Magazine #084 as the finale -
Dark Tower (1980)
finally a dungeon with an interesting level design -
N1Against the Cult of the Reptile God (1982)
low-level investigation in a rural village
has some neat ideas/twists that still work today
a certain NPC is a disaster for the story … but that is fixable -
B4 The Lost City (1982)
very neat sword & sorcery adventure - inspired by a Conan short-story
to be fair: it needs heavy lifting to make it work
(the 2nd part of the dungeon is basically unuseable)
I run this right now -
UK1 Beyond the Crystal Cave (1983)
non-combat adventure
has some problems but is fixable
obviously WotC added combat in the 4E (2011) reprint
-
I-6 Ravenloft (1983)
replayable heist adventure in a gothic-horror castle -
RM2 The Created (1993)
takes a very specific DM AND gaming group to make it work
still awesome -
Red Hand of Doom (2006)
especially the @BufoBufo version
war-campaign against a goblin army
… I suggest that you switch the last two chapters -
Reavers of Harkenwold (2010)
fight an evil merc. army in a points of light setting -
Way of the Wicked (2012)
evil campaign of Asmodeus cultists taking over a paladin-loving island nation -
Kingmaker (2010; re-releases for PF 2E 2022)
hexcrawl / sandbox / kingdom building
sooooo much work for the DM, but def. worth it, if somone pulls that off
the re-release is actually better and fixes a few issues
Regarding Ravenloft 5e / Curse of Strahd as a must-play, it depends. Yes, if you’re into gothic horror. No, if you’re looking for more light hearted games.
In my experience CoS games can also vary widely, depending on how the GM runs them (lethality, lightheartedness, how depressing it gets, etc) which in turn can influence how people experience the game. with a GM that runs the game in a way the players enjoy and at least a little interest in the genre, it is an amazing campaign.
compared to other wotc campaigns the quality is quite good. stuff is a little all over the place organisation-wise, but that’s often the case with sandbox games with a limited page count. and while there are amazing community contributions out there (and a lot of them), it’s still a decent campaign if run straight from the book.
I might be a little biased though, I’m a sucker for gothic horror in general 
Another really good adventure is “Shadow of the Sun” from the Radiant Citadel book, i ran it yesterday. For me it’s the best I’ve read and GMed in 5e. It’s level 11, which might be a turn off for some, but if you’re willing to play/run tier 3, i can warmly recommend it.
It has actual choices that influence the ending in a substantial way, with both being completely valid courses of action (and only small adjustments needed for a third option). That’s something i have yet to see in any other 5e adventure (full disclaimer, i have not read everything).
Fixing Enemy Within is practically an industry. But yeah, the bit you’re talking about is funny. 
(Still a stone cold must-play.)
In any case, this isn’t necessarily my list. It’s the modules that will probably pop up in these conversations with the greatest frequency. If it were just my opinion, I’d tell you that our 4th Tournament was a must-play because it featured the greatest puzzle ever found in an RPG (and it’s not close), but that’s just me.
(Somebody called me humble on discord. Have to restore balance to the Force.)
As for the other stuff, Tomb is really good at what it sets out to do. It’s the single most iconic D&D module for a reason. Agreed about the others, for the most part. There are some weaknesses there - ToEE drags, etc. - but this style of adventure writing was just so good at creating memorable moments. Like Tamoachan: you came away from that with three of the best moments I’ve seen in this group. That’s quite something. Same with the ones on your list, or Isle of Dread, or Castle Amber …
(Regarding the if you like dungeons qualifier, well, if you don’t, you might not exactly be the target audience for Dungeons & Dragons in the first place …)
you should join us for those adventures!
it’s different, it’s fun, it’s deadly
I have the new version, you’re welcome to borrow them. They went all out on historical background so it’s interesting.
I did a lot of research into running Orient Express. There are some acknowledged critical flaws where entire campaigns can fall apart if Keeper isn’t careful, and if things don’t go exactly right at times, there’s a lot of “it happens this way because it has to.” Lots of promise, but when reading through it, I could easily imagine a campaign going badly.
I met two of my best friends through playing that campaign … so in the long run it was a win
not sure If I want to run it myself … but thanks for the kind offer
I’d add that you should probably play Ravenloft if you’re interested in this stuff in general. It’s not just a good module; these Hickman designs are, for better or for worse, something of an inflection point in the way scenarios were written.
As to horror, similar to Darth’s comment about liking dungeons, we’re playing a game upon which Lovecraft was one of the main influences …
Scifi scenario gems:
-
A Star Beyond the Stars (Star Trek; 2018)
This gem has everything:
Romulan spies, classic villains from the tv-series, and very high stakes -
A Cry from the Void (Star Trek; 2019; is in the Strange New Worlds anthology)
while doing boring stellar-cartography your ship is hit by a tetryon wave,
… when investigating where that came from, things get weird
my players really liked that one -
A Doomsday Like Any Other (Star Trek; 1986)
How bad could it be: Doomsday Machine version -
Back to Reality (Star Trek; 2020)
works best with a Federation (late) TNG crew:
really brutal choice-and-consequences -
Border Dispute (Star Trek; 2017; is in the These Are The Voyagers anthology)
Investigation scenario; cat-and-mouse game in the Romulan Neutral Zone -
Gravity of the Crime (Star Trek; 2019)
Solve a closed-room murder mystery while keeping the Prime Directive -
The Outcasts (Star Trek; 1985)
Recruit a renegade Romulan for Federation Intelligence -
Signals (Star Trek; 2017; from the Living Campaign)
works for Klingons as well as Federation crews:
“bad things happen at once” - the scenario -
Termination: 1456 (Star Trek; 1984)
made for a Klingon crew
Klingon Intelligence investigation / spy thriller scenario -
The Jewel of Yavin (Star Wars; 2014)
written for smuggler/pirate characters:
heist set in Bespin’s Cloud City to acquire an “unstealable” jewel during a Grand-Prix -
Operation Elrood (Star Wars; 1996)
written for Rebels:
undercover spy-thriller set in a backwater region of the galaxy -
Scarvenger Hunt (Star Wars; 1989)
written for Rebels:
really funny adventure - love the NPCs, and the locations -
Starfall (Star Wars; 1989)
escape your imprisonment on a Star Destroyer,
… while the capital ship is torn apart by a Rebel Fleet -
Dragon Hunt (Shadowrun; 1991)
“Watch your back, shoot straight, conserve ammo, and never, ever, cut a deal with a dragon.”
best SR adventure imo -
Stealth Train (Paranoia; 2004; is in the Crash Priority anthology)
best Paranoia adventure imho
[you do not have the clearance to see the synopsis^^]
@H and others:
do you know any Traveller “you-gotta-play” adventures/scenarios?
I don’t think there’s anything on an Enemy Within or Masks level. Drinax, perhaps? That one gets a lot of love. Traveller Adventure’s also supposed to be quite good. I’m not the best person to ask, though - all we did was homebrew stuff.