Query for the top

[bigtall]

I have a bunch of test scores for different people and what I was wondering....is there a way I can check to see if one person had the highest scores on all three tests? I figured I could just do three queries and grab the person with the top scores from each and compare the column that has the person's name to see if they are the same but is there an easier way to do this?

Sample Data:

Person || Test 1 || Test 2 || Test 3
John || 94 || 87 || 90
Hillary || 89 || 76 || 89
Bill || 92 || 82|| 68

[LordShryku]

Why not use sum() to add them all together? Then you could just order by that sum and do a limit 1 to get the top person.

[bigtall]

No as just because they have the highest combined score, it doesn't mean they'd have the highest on each test.

[sid]

if you worked with postgreSql or Oracle i would suggest to intersect the queries for the best test results. since mysql doesnt support this, you would have to manage a workaround like this:

 
SELECT person
FROM dbtable
WHERE test1 = (
    SELECT max(test1)
    FROM dbtable
    GROUP BY person
)
AND test2 = (
    SELECT max(test2)
    FROM dbtable
    GROUP BY person
)
AND test3 = (
    SELECT max(test3)
    FROM dbtable
    GROUP BY person
);

since i only used subqueries in oracle i cant tell you if that syntax is correct, but you should get an idea how to tackle this!

[bigtall]

That gets me started nicely, thanks!