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
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
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
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.
The
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
Not a problem. I think the easist way to do it is
SELECT * FROM families ORDER BY score DESC
LIMIT 2 Of course you need your grouping statement in there but the ORDER
BY section shouldntmake a difference.
There are MAX and MIN functions but my understanding is that those will
only return THE
[snip]
I want to retrieve members of each family that have the two highest
scoring values for a column. i.e the max and second max.
Is there a function similar to max() or greatest() that will return the
top 2 values when grouping by family ID?
[/snip]
Try this query
select foo, MAX(bar)
from
