For a 'pure' first-person shooter, the original HALF-LIFE, which I usually do in about 14-16 hours depending on the difficulty level and if I'm trying to explore very nook and cranny in the game. HL2 is a fair bit shorter (12 hours max) and most modern FPS are even shorter than that, FEAR coming in at less than 10 hours. BIOSHOCK I did in about 10-12 hours as well, and I don't think I missed anything major along the way.
For real-time strategy games, I remember TIBERIAN SUN having 50 missions (most modern RTS have about 20 if you are lucky), but TOTAL ANNIHILATION KINGDOMS had even more, about 70 IIRC, with some missions taking 2-3 hours to complete. I think that's the record in that genre. I was hoping STARCRAFT 2 was going to beat it but they decided to split the game in three instead.
Modern BioWare games (KNIGHTS OF THE OLD REPUBLIC onwards) have tended towards a more short and linear approach. I did KotOR, KotOR 2, JADE EMPIRE and MASS EFFECT in less than 40 hours each, the latter two in less than 20. In particular, I can't see how people can stretch MASS EFFECT out to ridiculous lengths as all the random side-missions are mind-numbingly repetitive.
I'm at 60 hours with FALLOUT 3, having finished the original game and all three DLCs. As far as I can tell I've done every named quest in the game, but there's still tons of sites I haven't visited yet, so I can see where people are getting the 100+ hours play-times from. I held off on doing that as I'm Level 28 and want to save my last two levels for the last two DLCs. OBLIVION with SHIVERING ISLES probably has between 2 and 3 times as many quests as FO3 with the expansions added, so I can tell where people are getting those play times from as well.
DAGGERFALL and FRONTIER are insanely huge games, but I'm not sure if procedurally-generated neverending games can really count here.
For a game with genuine, hand-crafted quests and design, the biggest game ever made is easily, by itself, BALDUR'S GATE 2. I spent around 150 hours just focusing on the main quest and a few side-missions (for comparison's sake you can do FALLOUT 3's main quest in about 6-7 hours if you focus just on it by itself). When I looked at a hint guide to see how many other quests I missed out on I was stunned. There was about three times as much content there as in the main quest by itself, plus you get totally different results for the make-up of your party. Then each NPC has their own storyline with different results depending on different permutations of them in the party. And that's without adding the THRONE OF BHAAL expansion or using the mod to add the BALDUR'S GATE 1 quests and locations to the game. All three put together must come in at around 500 hours of specifically designed game content. BioWare have even admitted that they went a bit mad making those games (and also that DRAGON AGE will be a lot longer than some of their other recent games, which sounds hopeful).
If there's another game out there that comes close to that, I'll be very surprised.
