Basically, the more pins you have, the better your processor is.
Today's main choices include the following.
Socket A (462) - Athlon XP, Duron
Socket 478 - Pentium 4, Celeron
Socket 754 - Athlon 64 up to 3400+
Socket 939 - Athlon 64 above 3400+
Socket 940 - Athlon FX and Opteron