Thank you for your responce, you have guessed it correctly the only difference is the usage of MAX which I cannot use (see my explenation on what I want to do in a seperate message)
I'm not sure IF I can do it with sub-selects either, but when subselects is present in mysl, I could try that one.. ( do not have any other database to test with, so I cannot try it out) but thanks anyway.. ----- Original Message ----- From: "Carl Troein" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 13, 2001 1:23 PM Subject: Re: order by, group by > > 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
