Selecting other columns in conjunction with max(column)

I have a table with 2 columns: score and person. I am trying to calculate the person with the highest score. I tried doing:

SELECT max(score), person FROM my_table GROUP BY person;

but that results in many returns, not just one. I also tried:

SELECT person FROM my_table WHERE score=max(score);

but that resulted in an error.

Is it possible to find the maximum score and the person associated with that score in one step? Or should I find the score first and then use the WHERE statement in second select statement to get the person that earned it?

Thanks in advance for in help you might be able to offer.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by Annie:
I have a table with 2 columns: score and person. I am trying to calculate the person with the highest score. I tried doing:

SELECT max(score), person FROM my_table GROUP BY person;

but that results in many returns, not just one. I also tried:

SELECT person FROM my_table WHERE score=max(score);

but that resulted in an error.

Is it possible to find the maximum score and the person associated with that score in one step? Or should I find the score first and then use the WHERE statement in second select statement to get the person that earned it?

Thanks in advance for in help you might be able to offer.[/quote]

Hi,

I guess SELECT MAX() is for grouping only.
Try the following query :

SELECT person,score FROM my_table ORDER BY score LIMIT 1;

Bye,

GBubani

hi,

you should issue the following command:


select person,score from my_table order by score desc limit 1;

<<sort all the rows descending by score and only get the first row using the LIMIT clause>>.

------------------

SR -
shiju.dreamcenter.net

Web developer from GOD's own country!!!!!


[This message has been edited by Shiju Rajan (edited May 10, 2000).]

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by Annie:
I have a table with 2 columns: score and person. I am trying to calculate the person with the highest score. I tried doing:

SELECT max(score), person FROM my_table GROUP BY person;

but that results in many returns, not just one. I also tried:

SELECT person FROM my_table WHERE score=max(score);

but that resulted in an error.

Is it possible to find the maximum score and the person associated with that score in one step? Or should I find the score first and then use the WHERE statement in second select statement to get the person that earned it?

Thanks in advance for in help you might be able to offer.[/quote]

Hi,

I'm sorry. In my previous e-mail I missed the DESC clause in the ORDER BY statement. The final command should be :

SELECT person,score FROM my_table ORDER BY score DESC LIMIT 1;


Bye,

GBubani