Multiple Value IN clause

[Welshbird]

I can't decide if I'm just having another dense day (a distinct possibility!) but I just can't see how to do something that I'm sure must be really obvious.

So, I place myself at the mercy of the much brighter people on here for help and guidance.

I have a table (results) which is created from a cross join query with the following structure:

UK_Flag number,
Panel_Flag number,
SCORE number

and all UK_Flags have a score against all Panel_Flags from the cross join that created this.

All I need to end up with is a distinct UK_Flag and a distinct Panel_Flag which got the highest score.

Looked really easy at first, but I've tried the following:

select UK_Flag, Panel_Flag, max(score)
from results
group by UK_Flag, Panel_Flag

but this doesn't give me the distinct occurrence of the highest score for the pair of ID's.

I tried the following

 select uk_flag, panel_flag, max(score) 
 from results where
 panel_flag AND max(score) in
 (select panel_flag, max(score) from
 results group by panel_flag)
 group by uk_flag, panel_flag

But I can't have two columns in my IN statement can I?

I'm sorry for posting something that must be really obvious to lots of you, but I'm pulling my hair out here!

Thanks in advance for any help guys.

Oh - some sample data might be helpful?

8460935         5687   12.15102
8460924         5687 11.8744467
8460923         5687 11.8743681
8460913         5687 11.8824033
8460912         5687 11.8764777
 860129         5687  13.381793
 860126         5687 13.3271756
 860132         5687 13.2044873
 860128         5687 13.2897236
 860127         5687 13.1587425
 1366680       821300 13.3099313
 1312941       821300  13.616784
 1312939       821300 13.6348753
 1235704       821300 13.5510036
 7334334       821300 13.3966796
 7334326       821300 13.2640535
 1334151       821300 12.9467158
 1334140       821300  12.969111

from which I would hope to get

 860129         5687  13.381793
 1312939       821300 13.6348753

thanks again

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[taupirho]

You CAN have two columns in your IN statement

here's one way

select *
from results
where (panel_flag,score) in (
select Panel_Flag, MAX(score)
from results
group by Panel_Flag)

 

[Welshbird]

Ah - I'll give that a go then.

Thanks for such a prompt response!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]