Thats quite an hard question really, one of my personal favs is the rod firing crossbow in HL2, i just love pinning people to walls with it. Another one of my favs is the multi purpose rifles from a little know game 'Outbreak'.
Perfect Dark had a cool fly-by-wire rocket launcher called the Slayer. You hid yourself, and when you fired it you could control the rocket in first person view and even speed up and slow it down. It's probably my favorite rocket launcher ever.