Frank, > Before you go any further, I would attempt to answer one question. > Are you trying to determine which Author had the highest royalties or are > you trying to determine which Author has the highest royalties in each > program(I am assuming an author can work on multiple programs)? This will > make a difference in how your structure your query.
I'm trying to determine which author has the highest royalty percent FOR EACH PROGRAM, not overall. I'm displaying a list of programs and authors, and when there is more than one author, I want to show the principal author (i.e., the one earning the highest royalty percent). > Also, I don't think you want SELECT DISTINCT * > I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES..... etc. is better > Also remember, a.AUTHOR will give you different results than r.AUTHOR...... How will the results differ? I guess I'm not clear on how "DISTINCT" works. Will it only look at combinations of a.AUTHOR and r.ROYALTIES if I write it that way? > I would try the MAX() function... see link... > http://www.mysql.com/doc/en/Group_by_functions.html#IDX1359 > If you use ORDER BY, it will give you all the authors in order (I don't > think you want that, do you?). I don't want all the authors in order of royalty percent. I want the them to be in groups by Program ID and ordered by royalty percent within that (or just take the max within each group). Another person on this list showed me how a subselect could achieve this result, but MySQL doesn't support subselects. - Sheryl >----- Original Message ----- >From: "Sheryl Canter" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Wednesday, February 26, 2003 11:49 AM >Subject: SELECT DISTINCT question > > >I need help with a query. I have a 'royalties' table that looks like this: > >AuthorID ProgramID Royalty >-------------------------------------- >Author1 Program1 0.15 >Author2 Program1 0.10 >Author3 Program2 0.25 >Author4 Program3 0.05 >Author5 Program3 0.20 > >The primary key of this table is a combination of AuthorID and Program ID. >Author information is stored in a separate table: > >AuthorID FirstName LastName >---------------------------------------- >Author1 Joe Smith >Author2 Brian Jones >Author3 Jeff Tucker >Author4 Michael Moore >Author5 Mark Mann > >The main page of my Web site has a program list that includes the program >name and author name (and other information). I want it to show the author >receiving the highest royalty amount. Right now I'm not considering >the possibility that more than one author can work on a program (since >currently none is), and my SELECT statement looks similar to this: > >SELECT * >FROM programs p, authors a, royalties r >WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID > >I could change this to SELECT DISTINCT * ..., but then which author would I >get? If it's always the first encountered row, then could I avoid checking >the royalty by always inserting the authors into the table in the correct >order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT >to choose which row to return? > >If I wanted to do it right and select the author receiving the maximum >royalty, how would I adjust the SELECT statement? > >TIA, > > - Sheryl > > > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
