In Reach it's a pretty decent gun. At medium range it's comparable to DMR/NR, at close range it's strong enough to basically make the AR a pointless weapon. I personally wish the clip could just hold one or two more rounds, as I sometimes come up just short and have to reload when I use it to fight two people at once or in a row. But I guess that would be pushing it.
Halo 2/3's magnums were a joke. If there's a gun that literally has no use in gameplay, it shouldn't be there. Although sometimes if a player in H3 was very poor at the game I found it entertaining to "out-BR" them with the pistol, just for the added embarrassment factor. It was also kind of funny to use dual magnums when idiots voted for Team Duals in Social Slayer. Having two magnums when everyone else was screwing around with spikers and plasma pistols was cool.
The Combat Evolved pistol will always be my favorite, mostly because I've played that game since its release on PC where its use is like a magical art form. A lot of people hear that it kills in 3 hits and go "omg overpowered," but the reality of CE PC is that you have to lead your shots by a rather significant amount to actually hit anything; if you just aim your reticle directly at a moving player's head (like you can do in Reach) you will miss every shot. It's extremely rare that people will just go "blam blam blam" and instakill you. I can do that, but only because my CE PC experience is rapidly approaching ten years. It's very difficult to use effectively despite its power.