It should have been remade by now. If Rocket Jockey were made today, instead of in the retail-dependent marketing murk of the 90s, it would have stormed it. You ride rockets around an arena, using grappling hooks to steer, but also to yank other riders off their rockets. It's bloody great.
You unlock extra rockets by yoinking them from other riders. Unseated players are extremely vulnerable, with no hope but to run to the nearest downed rocket and dive away from incoming grapples and charges. They're fair game to be run over, mercilessly dragged, tethered to the poles that litter each level (anchors for steering, see), or even eliminated entirely by lashing all their limbs up. But before you do that you'll want to brutalise them as much as possible for maximum, Bulletstorm-style trick points.
There's also a sporty mode where you lob balls, pucks, or sometimes bombs, into goals. You can carry two balls at once if you're feeling brave, but you have to swing them round to get momentum up, and thanks to the robust early physics engine, they affect your rocket's motion too. This mode has a referee who punishes fighting... but only if he sees it. And you can attack him, too. One time I dragged him around for a while, and when I crashed he ran over and started kicking me around the floor. Fair cop, really.
It's an absolute nightmare to get running today. Back in my abandonware days I bought it on CD (Home of the Underdogs linked to a distributor instead of a download) and played it on my cheapo, nth-hand Windows 98 PC. It took a grab bag of particularly obscure revival techniques to get it working on windows 7 even with the CD, and you lose out on the perfectly fitting sound track by surf guitar legend Dick Dale.
Even with its chunky, early 3D graphics, it's still bloody great fun. It should be remade.
Update: And it turns out that it is! Thanks to reader havaren for the tip. I'll definitely be keeping an eye out for developments on this one.