I was thinking about this when I was watching a recent TNG episode, about how the Enterprise was the "best assignment possible".
So if the Enterprise was the best assignment for a starfleet officer, what would be the worst, and what would be other "good" assignments?
I would imagine any "explorer" type mission would be highly coveted. Conversely, any deep space station mission (a la DS9) may not be as highly coveted, or perhaps stuck on Spacedock.
I use the TNG/DS9/Voy timeframe for this.