On 22/5/02 at 4:02 pm, Ciprian Trofin <[EMAIL PROTECTED]> wrote: > Hi, > R> Alternatively, you can compute your order statistic explicitly by joining > R> the table against itself, and then filter based on that: > > Why did you use max(curEntry.family) and max(curEntry.member) ? I > tried the query w/out max, and it works just the same.
MySQL can be a bit flaky if you combine summary and non-summary functions in the same field list. The same query may work if you've got a GROUP clause and the non-summarized fields are based on those listed in the GROUP, but I think I've also seen it not work, and the behavior is totally inconsistent with what happens if you leave the GROUP clause out entirely and just summarize the whole table. Further, if you combine summary and non-summary fields in the SELECT used to create a temporary table, absolutely completely totally bizarre things happen. My most recent experience involved my first query on the newly formed temporary table silently providing the wrong answers, but if the same query is run a second time it gets the answer right. (This is completely reproducible and consistent- apparently MySQL has a few optimizations in the temporary table code, and throwing questionable SQL at it might not interact with these well...) Save yourself a lot of headaches and never combine summary and non-summary fields. I do, however, kind of wish that MySQL had something more descriptive than MAX() for when you know that you're summarizing something that's the same everywhere; let's call it ANY(). Superficially, this would just pick an arbitrary value in the set and not waste cycles computing a MAX(). You might then be able to switch on an option to actually make sure that all values *are* the same- reporting a warning or error if not. > The following question is: how did you managed to write this > query? I'm a newbie, and only after I write the whole sequence down, I > succeeded in understanding how it works, but I couldn't understand what > stands behind it. Is there a place on the Internet where I can find some > documents to help my skills in writing complicated queries ? I think the main goal is *not* to write complicated queries, or at least not to think of them as complicated. Think of this query as just an attempt to compute 'orderstatistic' by joining each row against every row less than itself. Add a GROUP to count them, add a few of the original fields to get the output you want, add a HAVING to select the output rows you want, and add an ORDER to sort them. The idea of using a JOIN this way is a little spiffy, but it's a standard technique and with time you'll collect things like this in your toolbox. With enough experience playing with the other clauses you'll feel comfortable adding them to simple queries to refine exactly what your result set looks like. In many ways I think learning is easier on more robust database with sub-selects: first you write each step as a separate select, then you combine them as much as possible. SQL can often look like a write-only language: much tougher to read than to write. > R> select max(curEntry.family) as family,max(curEntry.member) as > R> member,max(curEntry.score),count(*) as orderstatistic > R> from theTable as curEntry, theTable as greaterEntries > R> where curEntry.family = greaterEntries.family and curEntry.score <= > R> greaterEntries.score > R> group by curEntry.family, curEntry.member > R> having orderstatistic < 3 > R> order by family, orderstatistic; --------------------------------------------------------------------- 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
