Note that you're ordering by "goals", not MAX(goals). That's why you're not getting the results expected.
Try: SELECT manager.name, position, MAX(goals) as goals FROM roster JOIN reference JOIN manager WHERE manager.idn=reference.idn AND reference.idp=roster.idp AND position like 'F' GROUP BY manager.name ORDER BY goals desc"; On Fri, 2003-02-07 at 19:14, C. Reeve wrote: > Hi again, > > After some struggling, I have managed to get the problem below 99% working, > the problem now is that I can't get them in descending order. Here is my > select statement. > > $query = "select manager.name, position, MAX(goals) from roster join > reference join manager where > manager.idn=reference.idn and reference.idp=roster.idp and position like > 'F' > GROUP BY manager.name order by goals desc"; > > Using the example below, this is what I get: > > Bill 70 > John 48 > Fred 87 > > This is what I want: > > Fred 87 > Bill 70 > John 48 > > TIA > ----- Original Message ----- > From: "C. Reeve" <[EMAIL PROTECTED]> > To: "MySQL List" <[EMAIL PROTECTED]> > Sent: Friday, February 07, 2003 1:57 PM > Subject: Select question > > > > Hi, > > > > I have a database with 3 names in it. In each of these names is 5 > > categories that have numbers in them. I want to be able to do a select > and > > get the top number from each category for each name and display them from > > most to least. I have checked all the docs on the select statement, but > > this is escaping me at the moment. > > > > TIA > > > > i.e > > > > This is what is in the database. > > > > Bill 36 20 50 46 70 > > John 26 30 32 48 46 > > Fred 87 40 19 62 42 > > > > This is what I want to be able to show: > > > > Fred 87 > > Bill 70 > > John 48 > > -- Diana Soares --------------------------------------------------------------------- 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
