Reader Challenge #2 (CLOSED)

By Bill Graziano on 10 October 2001 | 3 Comments | 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

note equal handicaps of Tim and Jo and so same rankings.

How can this be done with one query and no temp tables. I have a big list of golfers so I dont want a temp table if possible??

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.

Discuss this article: 3 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Another German Yak ... with a suprise (RC #3) (16 July 2002)

Das Yak ist Deutsch (RC #3) (14 July 2002)

Reader Challenge #3: Find the Yak! (22 March 2002)

Reader Challenge #2 Solutions (29 October 2001)

Reader Challenge #1 Solutions (Part II) (4 June 2001)

Reader Challenge #1 Solutions (Part I) (28 May 2001)

Reader Challenge #1 (16 May 2001)

Other Recent Forum Posts

Report spam here (916 Replies)

Help with XML (4 Replies)

Sql syntax suggestions help (1 Reply)

view to be indexed but has a subquery (7 Replies)

Copying Tables - Columns Do Not Match (2 Replies)

Visibility formula calculation (0 Replies)

Count for 2 fields-Help (16 Replies)

Find existing data within a date range change (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -