I like the ones in Dragon Age: Origins quite a bit. There are a few that are just "Kill X amount of Darkspawn", but you'll do that anyway. Most of them give you credit for making an irrevocable decision (highlighting that a discreet choice has been made), or finishing a particularly noteworthy part of the campaign. Often both at the same time. All the DLC have achievements too, which are more or less along the same lines, with the last two adding "Kill the boss of this DLC on Hard or Nightmare". In Golems of Amgarrak this is practically fucking impossible if you're not an archer because The Ravener is a motherfucking beast.
Anno 1404/Dawn of Discovery has a hojillion achievements to unlock, and they're mostly in the nature of genuine achievements, like building both of the most difficult structures in the game in one map, or having 10,000 nobles and 10,000 Envoys in the same game, and so on, and on and on. I like games that take that approach too. WoW kind of does this. There are two distinct groups of achievements in WoW. Fuck Around achievements, which get you a title, and MAYBE a mount or a pet at best, and the Super Cereal achievements that get you 310% mounts and genuine prestige titles, but the main thing is that there are fucktons of both.
I think my favorite style of achievement though are the kind that actually make you play better, like many of the later TF2 class update achievements that, by their descriptions, lead you to a style of play, or a use for a new weapon that you may not have thought about. Earlier class updates actually had the opposite kind of achievement, like Photostroika, that basically encouraged you to waste an Ubercharge posing like a cunt.