The Halo CE pistol, namely because it wasn't actually a sniper weapon yet it could easily outperform the sniper rifle in the hands of someone who's skilled with it (you need a big ass long range rifle to take a dude down? You pussy! I only need a pistol!).
It could be used at close range to great effect, had plenty of ammo and I only ever considered dropping it in favor of something like a shotgun or rocket launcher (even then, that's only if I had another weapon I was more reluctant to drop) along with the fact that it was the first weapon you got in the game.
In short, it was the sniper weapon for people who don't do sniping.