Basically, an abrasive is an abrasive. As long as the abrasive is harder than the steel in your blade, it will wear away metal and that's how you form or sharpen an edge. Quite simply it is a question of speed, cost and longevity.
Personally, I use ceramic waterstones because they cut pretty fast. On the downside, they wear pretty fast and need to be replaced relatively often. They tend to dish in the middle and need to be flattened regularly. Diamond cuts even faster but wears even faster and is expensive. I have diamond stones I've worn out in a few weeks. Since diamond stones have the abrasive on a steel base, obviously they don't dish so there is no maintenance required. Carbide abrasives cut more slowly and wear longer. Wet/Dry sandpaper is such an abrasive. If you glue it to a piece of wood it will make a perfectly fine sharpening tool. It wears pretty quickly on a paper base so don't expect it to last a long time and stay with the finer grits or you'll be replacing it quite often. Carbide oil stones are slower yet and wear more slowly. Silicon oxide ("dry" sandpaper) is even slower and lasts for only a very short time when abrading steel (because it is relatively soft.) Arkansas stones are not agressive at all but will last nearly a lifetime. That's a pretty simple explanation of the different abrasives available to sharpen an edge. Just buy what fits your budget and the amount of time you have to get the job done.
The sharpness of the edge is a function of its bevel angle. The quality of the edge is a function of straightness and smoothness of the bevels. If you can maintain the bevel angle accurately throughout the sharpening process and if you apply the right amount of polish (size of grit) to the bevels for the kind of work the knife has to do, it will perform as well as it can.
Knives don't really benefit from a lot of polish, in my experience, at least - not like chisels, leather parers and other things I sharpen. If I want to impress someone with how cleanly an edge will slice through unsupported newsprint, I'll polish it to around 3000-5000 grit. If I'm sharpening a knife edge to use for its intended purpose, 220 or 320 is about right. If you apply too acute a bevel angle, the edge won't last long. If you apply to obtuse an angle you will be wasting some of the steel's ability to hold an edge. Basically, that's all there is to it.
To recap, I consider diamond to be a good choice only for people who sharpen only occasionally and are in a hurry because it wears fast and is expensive. I consider natural stones to be good for people with a lot of time on their hands. The abrasives in between are best for most people. My choice of Japanese waterstones is my choice because I sharpen a lot, do it for money and time is money. Diamond would be too expensive for me and the other abrasives too slow for me. If there were one abrasive best for everyone, then there would be only one abrasive. Take care.