Selecting MAX Values

[bajanagent]

I would like to select the MAX of a value as well as the corresponding value in another column on the same row. Let me try to explain below:

Name NO Runs
Test1 1 200
Test2 0 100
Test1 0 30
Test2 1 110
Test1 0 210
Test2 1 150

A simple SELECT Name MAX(Runs) AS MAX FROM Table GROUP BY Name would give me:

Name MAX
Test1 210
Test2 150

I would like to select the NO value that corresponds with the Max value. So the below is what I ideally would want

Name Max NO
Test1 210 0
Test2 150 1

Right now by querying with SELECT Name MAX(Runs) AS MAX, NO FROM Table GROUP BY Name - it is only pulling back the first NO value it comes across, not the value that corresponds with the MAX value and giving me the following:

Name Max NO
Test1 210 1
Test2 150 0

Any ideas anyone has about getting the corresponding NO value for the Max(Runs) value would be appreciated! Thanks

[Dylan Leblanc]

I don't think there is a way to do this, atleast with one short query. I have tried to do similar things with data obtained by using GROUP BY and could not get it to work. There are other ways of doing this type of thing though.

[raf]

Well, a subquey would solve that quite easily. But you need MySQL version 4.1 for that.