I've seen one where promoting to a Queen creates stalemate so you gotta do a rook instead.
Promotion to a knight
I've never had a game where promoting to knight was worthwhile (or if I have, I missed it!).
But I have had a game where I had to promote to a rook instead of a queen to avoid a stalemate.

I once promoted to a knight to avoid checkmate on the next turn... his king was put in check immediately by the knight and he had to avoid it, and I managed to use the opening to escape the mate-in-1. (I still ended up losing, but it was a close thing.)
I've had I blitz game recently where I forked my opponents queen and king while promoting to a knight. That was kind of useful to trade down quickly.

I used to think..., why would you bother going inferior to a Queen at the point of promotion, but I saw an example in tactics trainer when a knight mated the opponent at conversion, that may not happen very often, but it's worth leaving the auto promotion off if you've got time to consider the alternatives. Its about using whatever strategy is best to achieve the goal.
The only two "good" choices when promoting a pawn are queen and knight.
Not true:

There are rare tactical positions where a queen would cause stale mate, or promoting to a knight causes a smothered mate, but on the whole you are more likely see thee them in a puzzle than in a real game. Still, keep your eyes open. Once in a while a queen will let the other guy get away with a draw.
Check out this thread. It's pretty amazing.
http://www06.chess.uo.zerolag.com/forum/view/more-puzzles/my-prettiest-loss?page=1
Just a curious question. The only two "good" choices when promoting a pawn are queen and knight. However, in most situations promoting a pawn to a queen is done. So, does anyone have experience or a professional game where a pawn was promoted to a knight?