Reader Challenge #2 Solutions

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

This challenge was certainly more popular than our last one. I received over 70 solutions! That in spite of my not knowing how rank golf handicaps. Congratulations to our own Nigel Rivett (nr in the forums) for the first correct solution. I'll break down his solution and correct my error with golf handicaps.

Nigel wrote in his email: I hope there is a better solution ot I am wrong - otherwise .... naf question.

Fortunately Nigel's SQL is better than his typing :) Here's his solution:

select	g1.Player, 
	g1.hcap, 
	Rank = (select count(*) + 1  
		from SQLTeam_Golfers g2 
		where g1.hcap < g2.hcap)
from SQLTeam_Golfers g1
order by Rank

which returns

Player               hcap        Rank        
-------------------- ----------- ----------- 
Fred                 8           1
Jo                   5           2
Tim                  5           2
Tony                 5           2
Barry                4           5
Bob                  3           6
Frank                3           6
John                 1           8

(8 row(s) affected)

and is exactly what I asked for. Unfortunately I got my golf handicaps reversed but we'll talk about that in a second. The next five people to submit a nearly identical solution were DMoloney, Onamuji, David Pardoe, Colin Overton and Ilya Zaltsman. Many others submitted the same solution but those were the next five to send it to me.

First let's break down this solution and see how it works. His solution is composed of an "outside" query and a correlated subquery. We wrote an article on subqueries a loooong time ago.

The outer query looks like this:

select g1.Player, g1.hcap
from SQLTeam_Golfers g1

It just selects the golfers and their handicaps. The next piece is the corrlated subquery. A corrlated subquery is a SELECT statement that runs for each row in the main query. It returns a single value that becomes a field in the main query.

select count(*) + 1  
from SQLTeam_Golfers g2 
where g1.hcap < g2.hcap

It's a correlated subquery because it uses a value from the outside or main query in the subquery. This query will count the number of golfers that have a handicap greater than the handicap of the player for a particular row. I think it might be easier to read if you reverse g1 and g2 in the WHERE clause so you get this:

select count(*) + 1  
from SQLTeam_Golfers g2 
where g2.hcap > g1.hcap

Notice that you also exhange a less than sign for a greater than sign to make this work. In this case we're using g1.hcap to limit our count. This count of golfers becomes our Rank that we can sort by.

Handicaps

I probably should have included a little more information on what a golf handicap is since it might not have been clear to some readers -- including me. Basically a golf handicap allows players of differing skill to compete fairly. Each players score is adjusted according to their handicap. If you're really curious you can read more about it at the United State Golf Assoication Handicapping Page. The key thing to remember (which I forgot) is that a low handicap is better than a high handicap.

If you want the query to return the golfers in the correct order this is what it looks like:

select g1.Player, 
	g1.hcap, 
	Rank = (select count(*) + 1  
		from SQLTeam_Golfers g2 
		where g2.hcap < g1.hcap)
from SQLTeam_Golfers g1
order by Rank

which returns this result

Player               hcap        Rank        
-------------------- ----------- ----------- 
John                 1           1
Bob                  3           2
Frank                3           2
Barry                4           4
Jo                   5           5
Tim                  5           5
Tony                 5           5
Fred                 8           8

Notice that the only change is in the WHERE clause of the subquery where we changed a greater than sign to a less than sign. Our subquery is now counting golfers that have a lower or better handicap than the current golfer.

Performance

When Tim Graham first asked this question we sent him a quick solution to get him started while we ran the reader challenge. When we started the challenge Tim responded with "Your solution works really well, even over the 10,000+ golfers in my database." It looks like nr's solution should be about 30% faster than the one we provided.

I've run quite a few of your solutions through Query Analyzer and haven't found a faster one yet. Unfortunately with 70 of them to test and some people using differnt column names I'm not going to test them all. If yours tests out faster, please post it in the comments section for this article.

Naf Question?

I had a few comments that this wasn't hard enough. I also had quite a few comments that people were challenged by the question. And I have no idea what Nigel meant by "naf question" even though somebody told me what that meant before. I guess that's how many people feel when I use an American idiom. I'm guessing Nigel didn't think the question was hard enough -- at least not for him. This question was easier than the previous challenge in my opinion. I'm glad enough of you found it challenging enough to take the time to send in a solution. The next one will be harder though. It will also be published in the newsletter before it appears on the site.

Thanks to everyone that participated. I'm sorry I couldn't publish and evaluate each solution individually. I'm also sorry I couldn't respond to each person that submitted a solution or asked a question but there were just too many emails.