Khan was a great tragic villain, not to mention well written and well acted. After him, i'd say Gul Dukat (pre-paw'wraith storyline) is next, especially since he was developed to be more complex, not just out and out bad guy, he had his good moments too. The borg queen, although an absurd concept (why would a collective need a queen) was downright creepy (and moist!). Weyoun after them, he was just amusing, and so insincere, and spineless (when it came to the founders), he was a joy to watch.
Nero, on the other hand was a weak villain, who had a 100 year advantage over the heroes, he was psychotic with grief so he was gonna do great damage. Again, i call him a weak villain not because his villany was weak (he destroyed vulcan, obviously). Still, i would not rate him second after Khan, he'd be after the above mentioned villains, most of them from ds9, a series that tended to develop secondary characters so their motivations made sense and such.