I've played all of them besides Mothership Zeta, so let me give you a brief rundown of them:
Operation: Anchorage - Fairly boring quests, but you'll get some really good gear afterwards, including a set of glitched power armor that doesn't get damaged, and the Chinese Stealth Suit. I personally don't recommend it though, as it's actually pretty boring compared to the others.
The Pitt - One of the most fun of the four I've played, and gives you some really cool gear, like the Metal Blaster, the Auto-Axe, and the Tribal Power Armor. The missions and the story are really good and I highly recommend this.
Broken Steel - If you can only get one, get this one. It extends Fallout 3 so that you can play past the terrible ending, it raises the level cap to 30, adds perks, new quests, new locations, and a fairly cool extension to the story as well as some cool equipment.
Point Lookout - This has fun quests, but the equipment you get from it blows. It's sort of favoring horror a little more (It's still not really "scary", but it's trying to be), and it gives you a very big (Maybe about a quarter of the size of the Fallout 3 main world) new map to play around in.
If I had to rate them in the order of what to get it'd be:
1. Broken Steel
2. The Pitt
3. Point Lookout
4. Operation: Anchorage (While the equipment is good, the whole point is just that it isn't nearly as fun as the other 3)
If you're getting one of the retail DLC packs (Either Anchorage and The Pitt or Broken Steel and Point Lookout) get Broken Steel and Point Lookout. It'll give you much more bang for your buck, and while I LOVED The Pitt, Anchorage just weighs the first pack down too much. Go for Steel and Lookout.
I never played Mothership Zeta, although I heard it wasn't too good.