Well, for skilled players I would probaly rank them:
Berserker
Support Specialist / Commando
Sharpshooter / Demolitions
Medic / Firebug
For your general pub hero:
Berserker
Support Specialist
Commando
Medic
Sharpshooter
Firebug
Demolitions
Berserkers are overpowered since they are way too hard to kill, and one who has any rough idea what he is doing can easily survive on his own. In fact it isn't uncommon for the Berserker(s) to deliberately bait and abandon their teammates so they die, leaving them to kite around the map more easily. Generally disliked by seasoned players.
Support Specialist is extremely versatile and is a hard class to really botch up with. Most players like to see a good support specialist in the team, and so they are a pretty popular class.
Commando is a great all rounder, a fantastically well balanced class, widely respected by all. Everyone loves a good Mando
Medics are again respected if they actually do the job of a Medic, rather than try and do another classes role.
Sharpshooters are great if people know how to use them and are disciplined with their shots. Most Sharpies are competant, but fewer can really be a destructive force. Generally respected, but only as long as they do their job.
Firebugs are generally frowned upon by most players, not surprising given the spray for your life and stock pile ammo styles of alot of players. The general public Firebug is usually an embaressment to the class, so although a good firebug can be helpful, the player really needs to know what they are doing.
Demolitions are the pinultimate, don't play unless you know how class. A bad demoman will get the team killed everytime, whereas a good demoman will be a rock that that the team holds their back up against. Sadly in public very few players know how to play demo, so generally they are hated even more than the Firebug.
Hope this helps you in your decision