There is not much differences in RAM really besides the bandwidth that they provide. RDRAM and DDR will provide a lot of memory bandwidth for whatever specialty that you need, but DDR is the way to go, especially in dual channel.
In many cases they don't have any specialty area - one is just faster than the other. Rambus does mix up the equation a bit though.
In order it would be
DDR RAM PC200
DDR RAM PC266
DDR RAM PC333
DDR RAM PC400 (in theory this is supposed to be faster than PC333, but actual real world use has shown it slower because of other factors in both the RAM and the system.)
Added to the types mentioned above is RAMBUS 800 and Rambus 1066.
SDRAM can be found in P4 systems; but is rare for Athlon systems (I would think at least).
DDR is used in Athlon XP and P4 systems. DDR is the recommended RAM for Athlon systems.
Rambus is used in P4 systems only. Rambus 1066 with a P4 is the fastest combination (although DDR comes so very close last I check -- which was a while back).
Certain motherboards like the nforce2 motherboard will help with DDR RAM systems to give even better performance