ahem, well, I think that's covered it, thanks very much that's excellent. That has to be in the top ten of 'comprehensive replies'
I was with you on the first option. I wanted to avoid dipping into perl in this case ( or at least I was trying to get by without it.) I will go with the second option as there are only 6 members max per family so it should handle this efficiently enough. Thanks again Rich Rob wrote: >I see at least three different techniques, each of which performs better for >different datasets. First, let's formalize our environment: > >create table theTable (family int, member int, score int); >insert into theTable values (1,1,10), (1,2,15), (1,3,12), (1,4,17); >insert into theTable values (2,1,5),(2,2,7),(2,3,9),(2,4,10); >insert into theTable values (3,1,4),(3,2,8),(3,3,2); > >You'd like to effectively select values based on top "nth order statistic", >or the nth biggest number, which is equivalent to MAX() for n==0 (or n==1, >if you think one-based). Unfortunately, I don't believe MySQL has a general >"nth order statistic" operation. What's more, you want to join the selected >order statistic back to its original data row, which MySQL can't do natively >even with MAX(). > >The first technique is the one that's been suggested: Run a simply query for >each family and limit yourself to two results: > >select * from theTable where family=1 order by score desc limit 2; >select * from theTable where family=2 order by score desc limit 2; >select * from theTable where family=3 order by score desc limit 2; > >This is efficient only if you've got a very small number of families and are >willing to code a loop into your interface language. > >Alternatively, you can compute your order statistic explicitly by joining >the table against itself, and then filter based on that: > >select max(curEntry.family) as family,max(curEntry.member) as >member,max(curEntry.score),count(*) as orderstatistic >from theTable as curEntry, theTable as greaterEntries >where curEntry.family = greaterEntries.family and curEntry.score <= >greaterEntries.score >group by curEntry.family, curEntry.member >having orderstatistic < 3 >order by family, orderstatistic; > >This manages to pack to whole logic into one query, and is very easy to >change to compute the top three, top four, etc. with no additional code. >Unfortunately, MySQL is unlikely to be able to optimize this to look at less >than the entire table regardless of what indices you provide. (A 'having' >clause usually implies that this is the case.) If you have a very large >number of members per family, this is clearly inefficient since you should >only really need to analyze O(number of families) rows in order to compute >your result. > >Finally, you can use temporary tables to compute intermediary results, with >each step making use of MAX() in the hope that MySQL is able to optimize >MAX() by using an index. I'm not sure if MySQL does currently optimize this >or not (please post the answer, if anyone knows), but it's definitely doable >in theory if you've got the right indices lying around. >The SQL here would be along the lines of: > >create temporary table topScores >select max(family) as family, max(score) as score >from theTable >group by theTable.family; > >create temporary table topMembers >select max(theTable.family) as family, >max(theTable.member) as member, >max(theTable.score) as score >from theTable, topScores >where theTable.family = topScores.family >and theTable.score = topScores.score >group by theTable.family; > >create temporary table sndScores >select max(theTable.family) as family, >max(theTable.score) as score >from theTable,topMembers >where theTable.family = topMembers.family >and theTable.member != topMembers.member >group by theTable.family; > >create temporary table sndMembers >select max(theTable.family) as family, >max(theTable.member) as member, >max(theTable.score) as score >from theTable,sndScores,topMembers >where theTable.family = sndScores.family >and theTable.score = sndScores.score >and sndScores.family = topMembers.family >and theTable.member != topMembers.member >group by theTable.family; > >insert into topMembers select * from sndMembers; > >select * from topMembers order by family,score DESC; > >drop table sndMembers; >drop table sndScores; >drop table topMembers; >drop table topScores; > >The last temporary table, at the least, can be turned into a simple insert >in our limited example, but I've provided it as a distinct table for >clarity. This technique is certainly much more complex, but for very large >numbers of members per family it might be more efficient, and it still >requires no logic in the interface language: there is only one command which >returns a result set for processing, and that is the final result. This >approach does require additional logic to extend to more than the top two >scores, which requires another pair of SQL statements per result. If you are >looking for order statistics on the order of the average number of members >per family, then the single-statement approach will almost certainly be a >better choice. > >-Rob > >(because I'm bored at work, that's why...) > >On 22/5/02 at 10:36 am, R.Dobson <[EMAIL PROTECTED]> wrote: > >>Hi, >>thanks for all of the replies to my query. >>I'm not sure that I explained my problem very well as the solutions >>received are solutions to the problem I described, but not the one I >>meant :-) (I don't think anyway) >> >> >>I have a table in the format: >> >>family | member | score >>-------------------------------- >>1 | 1 | 10 >>1 | 2 | 15 >>1 | 3 | 12 >>1 | 4 | 17 >>2 | 1 | 5 >>2 | 2 | 7 >>2 | 3 | 9 >>2 | 4 | 10 >>3 | 1 | 4 >>3 | 2 | 8 >>3 | 3 | 2 >> >>I want the top 2 highest scorers for each family as in: >> >>family | member | score >>------------------------------- >>1 | 4 | 17 >>1 | 2 | 15 >>2 | 4 | 10 >>2 | 3 | 9 >>3 | 2 | 8 >>3 | 1 | 4 >> >> >>Thanks again, >>Rich >> >>mysql >> > > --------------------------------------------------------------------- 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
