Reader Challenge #2 (CLOSED)

By Bill Graziano on 10 October 2001 | Tags: Reader Challenges

Earlier in the year we held a Reader Challenge. We found a really hard question and asked our readers to try and solve it. We've decided to to it again. Think you're good at SQL? Read on and try this problem! This Reader's Challenge is CLOSED.

This one comes to us from Tim in Australia who writes "I have a record set that contains a name and a golf handicap.

I want a query to rank the golfers based on their handicap.

eg:

Name, Hcap, Rank

Bob,  3, 1
Tim,  5, 2
Jo,   5, 2
Fred, 8, 4

all help appreciated."

Thanks for the question Tim! We already sent Tim a solution that seems to work. At least he hasn't complained. Can you do better than we did? I've got a script that will create the table and load it with some test data. Also included in the script is the exact result set your query should produce.

Your answer should be returned in a result set from a SELECT statement. For bonus points work in a Yak reference somewhere, somehow.

I'm looking for a couple of different solutions:

• Single SELECT statement. I know this can be done in a single SELECT statement. It will probably be a pretty complicated statement.
• Fastest solution. I'm looking for the solution that uses the fewest system resources. I'll use the Query Plan to figure this out.
• Most Obtuse. I'll also post the ugliest, slowest, hardest to read solution that I get. Whether you itended to write this type of code or not.

Please email the solutions and any notes or thoughts about them to me (graz@sqlteam.com).

The discussion thread is locked. If something about the question is unclear please email me and I'll post your question and the answer in the discussion forum.

I'll publish each of the solutions above with some commentary on each. I'll also publish any other interesting solutions or commentary that is submitted. If two people submit the same or similar solutions I'll give credit to the first one to reach me. The deadline for submissions is Tuesday, October 23rd 5PM Central Standard Time in the USA. This Reader's Challenge is CLOSED.

Unfortunately I don't really have anything to give you if you win except fame and glory. If anyone out there belongs to an organization (or is a book publisher) that would like to provide prizes I'll be happy to give you a mention and my thanks. I'm guessing there will be four winners or so. SQLTeam.com authors aren't eligible to win but I'd love for you to submit solutions. I'll certainly publish anything interesting from them.

If you have any questions, please email them to me. I'll post an answer for all to see in the locked thread. The decision of the judges is final. I also reserve the rights to make changes to this if it isn't working out the way I'd hoped. "Void where prohibited. Limited time offer." Good luck and happy coding.

This Reader's Challenge is CLOSED.