>What would be the best way to do this in a SELECT statment?
>I have a table with scores. Each person may have more than one score but I need to select only the highest for EACH person.
>
>I am not sure how to do it in a subquery. I tried something like this but of course is not good:
>SELECT * FROM myCur WHERE id = id;
>AND score IN ;
> (SELECT MAX(score) FROM myCur)
>
>thanks in advance
If I understood you well, you want to select the records which hold each persons highest scores (i.e. not just any record with the max of the score). I figure it would go like this
select id, max(score) as maxsc from mycur into curs maxcur
SELECT myCur.* FROM myCur, maxsc WHERE ;
mycur.id=maxsc.id and mycur.score=maxsc.score
If someone would have two records with the result equal to their max, you'll get them both, unless we include GROUP BY mycur.ID in the second select.
There'd probably be someone who can do this in an one-liner, but I'm just too tired at this moment :)
