DL Neil writes:
> "All keywords used must be given in exactly the order shown above. For example, a >HAVING clause must come after > any GROUP BY clause and before any ORDER BY clause." As far as I understood the original post, the question was if there's a way to get MySQL to perform an ORDER BY prior to doing the GROUP BY. In ANSI SQL this would be pointless, since you have to group by all returned fields that are not 'functions for use with group by'. In MySQL you can do something like SELECT a,b FROM table1 GROUP BY a to get the value of b for an arbitrary row for each a. This is mostly useful when you want a large number of columns returned and you know that whatever you're grouping by is unique, i.e. COUNT(*) would be 1 for every returned row. However, it would sometimes be useful to be able to extract the top value of something for each value of something else, _along with additional information about that top value_. For instance, you might want to know the top score for every week's quiz, and along with that the name of the best student. This would typically be done with a subselect, along the lines of SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT date,MAX(score) FROM quiz GROUP BY date) but this will give you more than one row per date if several people have the same score that day. I think what the poster was looking for was a way to do this with something like (and this is very broken): SELECT date,MAX(score),(name with score=MAX(score)) FROM quiz GROUP BY date which could be written as something like SELECT date,MAX(score),name FROM quiz ORDER BY score GROUP BY date ORDER BY date since you want to pick the name that matches the highest score. Unfortunately there is no way to do this in MySQL right now. You will have to first SELECT date,MAX(score) FROM quiz GROUP BY date and then loop over the result and SELECT name FROM quiz WHERE date=... ORDER BY SCORE DESC LIMIT 1 Once MySQL gets subselect you'll probably be able to do it like SELECT date,score,name FROM quiz WHERE (date,score) IN (SELECT date,MAX(score) FROM quiz GROUP BY date) GROUP BY date,score which would return an arbitrary person for each date's top score. I hope this makes sense and clears things up. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. --------------------------------------------------------------------- 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
