Re: SELECT DISTINCT question

[Frank Peavy]

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