Your guess is correct, DISTINCT works on the result set - i.e. if the result set contains two result rows that contain exactly the same values, it will eliminate the duplicate from the result set. SELECT DISTINCT is equivalent with using a GROUP BY without an aggregate function.
SELECT DISTINCT x, y, z Form Table is equivalent with SELECT x, y, z FROM Table GROUP BY x, y, z HTH, Tore. ----- Original Message ----- From: "Sheryl Canter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Frank Peavy" <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 11:50 AM Subject: Re: SELECT DISTINCT question > 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 > --------------------------------------------------------------------- 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
