#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    15
    Rep Power
    0
    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.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    10
    Rep Power
    0
    <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
  4. #3
  5. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    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).]
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    10
    Rep Power
    0
    <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

Similar Threads

  1. Selecting all columns EXCEPT some.
    By peenie in forum MySQL Help
    Replies: 3
    Last Post: November 18th, 2003, 11:28 AM
  2. Excluding columns when SELECTing
    By Shrimp in forum MySQL Help
    Replies: 5
    Last Post: September 13th, 2002, 07:18 AM
  3. Querying multiple columns
    By maxr in forum MySQL Help
    Replies: 2
    Last Post: April 10th, 2002, 03:17 PM
  4. selecting unique columns
    By lphillips in forum MySQL Help
    Replies: 2
    Last Post: November 29th, 2001, 08:05 AM
  5. selecting columns with identical values
    By Otis in forum MySQL Help
    Replies: 4
    Last Post: November 24th, 2000, 05:58 AM

IMN logo majestic logo threadwatch logo seochat tools logo