To me, FF4 was probably the best. If you don't mind doing an ass load of grinding, the story is good. Not amazing or great in my view, but good. Some of the characters are excellent, and currently the only FF game in the main franchise that I've finished, as of now.
FFTA2 was slow as hell, and I'd rather play the original for the GBA over it.
FF3's story was meh with grinding.
Revenant wings was pretty good and I liked it. Though, it's not enough for me to fully recommend it over FF4.
Lastly, CC is pretty good. Decent voice acting, story, and since it's not a turn-based RPG, even better. The ending is weird, and an enjoyable game overall.
Personally, FF4 > FFCC:RoF > RW > FF3 > FFTA2.