The Cliff Notes version:
940 for server/workstation
754 for people on limited budget
939 for everyone else
Which socket and CPU you get depends on your budget and your needs. If all you do is email and office applications, then a socket 754 Sempron with integrated graphics will work just fine. Socket 754 with a good video card (either AGP/PCIe) works great for gaming on a budget. If you need faster-than-PCI for certain RAID cards and such, then socket 940 may be your only option. If you are doing super-CPU-intensive-mutlithreaded stuff, then dual socket 940 with dual-core CPUs may be your savior. Everyone else (most of us) should just get socket 939.
940 - If you need server/workstation level boards with potentially onboard SCSI, dual CPU, PCI-X (I do not mean PCIe).
939 - Mainstream/Performance. If you want "budget dual CPU" then you can get socket 939 dual cores. If you want to aim for benchmark glory, then you'd want socket 939 with SLI (SLI for 754 isn't out yet AFAIK). If you have a budget of more than $200 to spend (well, closer to $230-250 after tax/shipping) then may as well get socket 939.
754 - Budget. Everyone says it is outdated, not being made anymore, doesn't have PCIe, extremely slower than 939... all lies. If you honestly do not have at least $200 to spend on just a CPU/motherboard combo, then socket 754 is a perfectly sensible choice. What else would you get at that price, a Celeron combo? WTF people! The fastest socket 754 chips still in production (assuming all 130nm cores are discontinued) are Sempron 3400+ at about $125 (256k cache, 2GHz). Combine that with a $60 Geforce 6100 board to make an awesome office/internet/mild gaming rig (integrated video of Geforce 6100 is basically... take a 6600GT which is still pretty good, halve the pipelines, halve the clock speed and 2/5 the RAM speed... okay not the greatest but it will play stuff). Add a PCIe video card to make it play any game you want. If $185 is too much money, the Sempron 2500+ (256k cache, 1.4GHz) is $60 so you have a $120 combo. Still too much? Get in on a Fry's deal. I just picked up a Sempron 2600+ with a motherboard (Nforce3 with AGP, not PCIe capable unfortunately) for $70 plus tax. Yes, a $70 motherboard/CPU combo.