All weapons serve their own niche so really it's a matter of ones play style that decides what works best-
The Regular Weapons:
Pike & Spear - Useful armor busters, can penetrate deep, and make awesome support weapons. The only real downside of the Pike and Spear is that they both fall in two different categories of weapon use. Still, they work well.
Hammer - Precision bashing best describes Hammers. Use them to get through the armored opponents and break their bones, before finishing them off with a strike through their Helmets. Mauls are also two handed so if you want to just crush something you'll have that option.
Mace - Ye old Mace vs Warhammer may favor the Warhammer but Maces include much more then just the simple Mace. Flails and Morningstars make Maces an extremely versatile weapon class; with Maces working best on unarmored opponents and Morningstars crushing armored ones, Flails coming somewhere in the middle. The only downside of the Mace user is that no mace is Two Handed.
Axe - Arm and leg choppers is what an Axe is all about. An Axe will dismember and disembowel, especially if made of good quality. There is also a lot of diversity in the type of Axe you can use, with the Greataxe being an extremely devasting Two Handed Weapon for unarmored opponents, Battle Axes being One Handed and good with Shields, and the Halberd being a little less effective but decent with the stabbing ability for those armored opponents.
Whip - The lightsabres of Dwarf Fortress, a whip will quickly incapacitate an enemy causing them to give in to pain, before a strike to the head will finish them off - with or without armor or helmets! Hammers and Maces might do this but the Whip truly focuses on doing it.
Sword - The most versatile of the weapon classes, great for slice and dice with quick sword thrust to the face! Swords shine for their ability to inflict multiple types of attacks, and coming in various sizes along with the Two Handed Sword makes them a winner for all types of strategies that use them.
The Irregular
Bow & Crossbow - While they work the same for now, the Bow and Crossbow are 2 of the 3 launches in game that players can easily access. Bow's and Crossbow's are perhaps some of the most deadly weapons, only faltering in the fact that it takes a while to reload. They can also be used in melee but if your going to do that you should pull out a different weapon. Bow's and Crossbow's are invaluable support weapons.
Knife - Don't underestimate the simple knife, Knives make for one of the lightest weapons and they can be used in a simple stabbing attack. This makes them a great secondary weapon for say Bowman or Crossbowman. It's also a fairly nice weapon for Wrestlers who want to forgo the Shield. You start with a Knife in the beginning when you make a character in adventure mode, though only Human Outsiders can naturally begin training in the skill.
Pick - Picks technically fall under the Mining skill, but they are perhaps some of the deadliest weapons in game. They tear through solid rock so it is no surprise they tear through armor. They also can lob off body parts. The real problem though is finding them.
Blowgun - Never used them but I'll note they are here. Like picks they are hard to find.
***
Personally from the list I like Swords the best, being the most versatile weapon for any situation and being available to Dwarves, Humans, and Elves for training. However this doesn't mean it's the best - the best weapon will always be the one that wins out in the specific situation, and that could really be anything.