posted May 09, 2000 01:16 PM         

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?

posted May 09, 2000 03:29 PM            

quote:

---

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.

---

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,

posted May 10, 2000 03:01 AM            

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!!!!!

posted May 10, 2000 06:07 AM            

quote:

---

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.

---

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,