While this may be cheating a bit, I'd have to say the FarSight XR-20 from the orignial Perfect Dark. I mean, a sniper rifle that can shoot through walls and is actually a part of a game instead of some hack? Not to mention is a one hit kill when it hits any point on the target? That's pretty crazy.
To be legit (and to give my self appointed sniper title credit) my personal favorite sniping weapon is probably the Sniper Rifle from TF2. It's not the most creative weapon, but the bolt action gets you in a nice rhythm when you're taking down people from a distance.
Of course, there's also "shotgun sniping." That mainly applied to back in the days when the guns didn't have as realistic of range to them, but it's still pretty embarrassing to be taken down at long distance by a wayward shotgun blast.