Hi Frank,
I had two main questions:
(1) What is the rule that SELECT DISTINCT uses when deciding which of
multiple instances to return? Does it return the first one?
(2) Is there a way to write a SELECT statement to return the record for the
author with the highest royalty percent (a different field in the table)?
That is the one that I want.
Details on the database structure are below.
Thanks.
- Sheryl
----- Original Message -----
From: "Frank Peavy" <[EMAIL PROTECTED]>
To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, February 27, 2003 11:10 AM
Subject: Re: SELECT DISTINCT question
Sheryl,
Sorry I don't time to fully address your question but I will try to give
you some feedback.
The DISTINCT functionality will give you exact that, every distinct
instance of the columns you SELECTED.
SELECT DISTINCT a.Author FROM author a
Will give you each and every author but only once.
Since your Royalties table has pointers to both Authors and Programs, if
you run DISTINCT *, you will get every single instance of every combination.
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.
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......
Hope this helps.....
At 10:13 AM 2/27/03 -0500, Sheryl Canter wrote:
>Didn't receive answer to message below. Could someone please take a look?
>
>TIA.
>
> - 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
