Frank,
Sorry that I seem to be explaining this so poorly. I'll try to clarify what
I'm trying to do and what my questions are. Someone on this list did give me
code that I think will work. I haven't had a chance to try it yet.
My question about how DISTINCT works is this: does it operate on the *result
set*? So depending on my SELECT statement and which fields I bring in,
different rows may be distinct? It's my guess that it works this way. I want
to confirm.
I certainly don't want to display the author's royalty percent on my Web
site. I just want to use it to select the principle author, and display the
principle author's name with the program name. So I'll have:
Program Name Author Description Upload date
---------------------------------------------------------------------
Program1 Smith
Program2 Jones
Program3 Harvey
When more than one author works on a program, the royalty *percents* (not
dollar values) will be apportioned according to their contributions to the
project. On my Web site, I only want to display the name of the principal
author--defined as the author who is paid the highest royalty percent *on
that project*. Most projects will have only one author. A few will have
multiple authors when someone had to take over the code or two programmers
decided to collaborate.
> Which is it, grouped by Program Id and ordered by royalty percent
> Or
> Just the Max in each group.....
> You need to make up your mind....
I don't think I'm having trouble making up my mind here--just having trouble
explaining my purpose. I think you can see now that I want to group by
ProgramID and then find the highest royalty percent within that group so I
can identify which author name to display.
Thanks for your help.
- Sheryl
----- Original Message -----
From: "Frank Peavy" <[EMAIL PROTECTED]>
To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, February 28, 2003 11:22 AM
Subject: Re: SELECT DISTINCT question
Sheryl,
>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).
Ok, so your desired result will look like... what???
Program ID, Author, Royalty Amount
1, smith, $100
2, jones, $250
Right? Ok, so what should your SELECT statement look like...?
Shouldn't be too hard to figure out...
> > 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?
First of all, since you have tables, have you tried populating the tables
with data. What did you get?
> I guess I'm not clear on how "DISTINCT" works.
Think of DISTINCT as meaning unique.......
Or, another way of looking at it, it answers the question, "what are the
distinct (or unique) pieces of data in a column?".
>Will it only look at combinations of a.AUTHOR and r.ROYALTIES if I write it
>that way?
Yes, if you write it that way...
Assuming Table a contains this:
Author
----------
Smith
Jones
Johnson
Assuming Table r contains this:
Author Royalties
------------------------
Smith $100
Johnson $100
Based on the logic I described above, how do you think the queries results
will differ, depending upon if I used Table a or Table b in my DISTINCT
statement?
SELECT DISTINCT a.Author, will resulting in:
Smith
Jones
Johnson
SELECT DISTINCT r.Author, will resulting in:
Smith
Johnson
but... no Jones, because Jones has no record in Table b.....!!!!!!!!
I will say it again, it answers the question, "what are the distinct (or
unique) pieces of data in a column?".
>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).
Which is it, grouped by Program Id and ordered by royalty percent
Or
Just the Max in each group.....
You need to make up your mind....
---------------------------------------------------------------------
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
