This is a reprise of a question I asked some months ago. Bruce Feist and Tore Bostrup 
gave me some untested code to try. I've only now been able to try it, and it's not 
quite working right. Here's a recap of the problem I'm trying to solve:

My Web host is running MySQL 3.23, which doesn't support subselects. I have a Web site 
that displays a list of programs for sale (shareware). The list displays the authors' 
names. In some cases, more than one author works on a program. In this case, I want to 
display the name of the lead author. I define this programmatically as the author who 
earns the highest royalty rate. I have tables like this:

royalties table (primary key is a combination of AuthorID and UtilityID):

AuthorID    UtilityID    Royalty
--------------------------------------
Author1      Utility1      0.15
Author2      Utility1      0.10
Author3      Utility2      0.25
Author4      Utility3      0.05
Author5      Utility3      0.20


authors table:

AuthorID    FirstName    LastName
----------------------------------------
Author1      Joe               Smith
Author2      Brian            Jones
Author3      Jeff              Tucker
Author4      Michael        Moore
Author5      Mark            Mann


utilities table:

UtilityID    ProgramName
----------------------------------------
Utility1      ProgName1
Utility2      ProgName2
Utility3      ProgName3


This is my SQL code:

DROP TABLE IF EXISTS ProgramRoyalties;
CREATE TEMPORARY TABLE ProgramRoyalties 
  SELECT AuthorID, UtilityID, Royalty, max(Royalty) as MaxRoyalty
  FROM royalties
  WHERE Royalty = MaxRoyalty
  GROUP BY UtilityID;

Unfortunately, the above produces a table with nothing in it. If I take out the WHERE 
clause, I get one line per group (per utility), but the author isn't necessarily the 
one with the highest royalty rate. It seems like the first author in the list is being 
selected.

The SELECT statement for using the above table (once it's properly populated) is:


SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version, u.ShortDescription, 
u.MinorReleaseDate, 
      a.LastName, pr.UtilityID, pr.AuthorID
      FROM utilities u, authors a, ProgramRoyalties pr
           $WhereClause
     u.UtilityID = pr.UtilityID AND a.AuthorID = pr.AuthorID;

How do I get the correct data into the temporary table?? Using the above sample data, 
I'd want it to look like this:

ProgramRoyalties table (primary key is a combination of AuthorID and UtilityID):

AuthorID    UtilityID    Royalty
--------------------------------------
Author1      Utility1      0.15
Author3      Utility2      0.25
Author5      Utility3      0.20

Thanks in advance for your help.

Sheryl Canter
Permutations Software
www.permutations.com

Reply via email to