Select row based on max value of a field

[hvass]

I am trying to select a particluar row with the maximum score from a group of records - sounds simple maybe it is but it has got me so any suggestions would be very much appreciated

from myfile
------
fkey,urn,score
1,1002,80
1,1003,100
2,2004,100
2,2005,80
3,3006,100
4,4007,80

I want to get

1,1003,100
2,2004,100
3,3006,100
4,4007,80

select
fkey,
urn,
max(score)
from myfile group by fkey;

gives me max score of 100 but with an arbitrary urn

select
a.fkey,
a.urn,
a.score as ascore,
max(b.score) as bscore
from myfile a inner join myfile b using(fkey)
group by fkey
having ascore=bscore;

seems to give me a different problem it only picks cases where the first record in the set happens to be the one with the max score

Thanks in advance

 

[r937]

see 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

rudy

 

[hvass]

thanks