If I really wanted to rip the combat system I could easily do it. In reality it isn't that bad. The spearman vs. archer debate could go on ad nauseum. There are a lot of other factors: terrain, fortification of the unit, fortification of the square, etc.
Do I always attack with my strongest unit first? No. If I'm attacking a city, I might attack with my weakest unit first to wear down the defender a bit, even though I know I'm going to get the attacker eliminated result. But that might leave the defender in yellow or red for the next onslaught. Better to lose a weak unit than to have your strong elite unit worn down into the red and then have to defend against a counter attack with that weakened elite unit.
If you're engaging in a major war in ancient times, you might want to attack the archer with your warrior first, then attack with your spearman.
I generally try to avoid wars. They are costly and detract from pursuing the cultural victory. I've never initiated a war, but usually keep a strong military as a deterrent.
