Hydraulic brakes are top of the line. They don't lose force to things like cable compression so the force that you put into the brake levers is transferred directly to the calipers. They also self-adjust for pad wear, whereas with mechanical brakes you generally have to regularly turn a knob somewhere to move the pads closer to the rotor as they wear. Finally, most (but not all) mechanical disc brakes only move one pad, pushing the rotor into the other pad, while hydraulics move both pads together.
Options for full hydraulic braking with integrated shifters for drop bars are available from Shimano, SRAM and Gevenalle (though Gevenalle's are not what you typically think of when you think of integrated shifters).
"Cable actuated" is just another name for mechanical. Their primary advantages are that they are cheaper and that they work with brake levers designed for use with rim brakes (which is most brake levers for drop bar applications).
Another option is some sort of hybrid solution where a cable is used to pull an arm that operates on a hydraulic chamber. There are a couple of products available that put the hydraulic chamber near the handle bars. There is also the TRP HY/RD brake which puts the hydraulics at the caliper. I have the HY/RD's and they work great if you use them with full length compressionless brake cable housing. They aren't quite as good as full hydraulic, but they are much better than most mechanical disc brakes.
Among mechanical disc brakes, the TRP Spyre stands out in my mind. It is one of the exceptions to the statement I made above about mechanical discs moving only one brake pad. The Spyre moves both pads and by all accounts this makes a huge difference in how the brake performs.