Without deleting the maximum value of the column

[neo_phyte]

How do I write a query that deletes the record and never deletes the maximum value of a certain unique field?

 

Example:

 

Shall we say I have 50,000 records of my table composed of fields such as first_name, last_name and scores, and I have 100 unique value of a 'last_name' field and each has a maximum value of column 'score' field. And I want to delete the repetitive 'last_name field' but not deleting the record that has the maximum value of column so that at least one record of that particular person remain in my table and that is the maximum value. How will I do that?

 

 

Thanks in advance.

[clearstatcache]

i pressumed ur table structure is sum wat lyk ds....

ScoreKey      LastName  Score

  1                Reyes        5

  2                Cruz          10

  3                Reyes        3

  4                Reyes        6

  5                Cruz          2

 

try dis....

 

$score = mysql_query("select LastName, ScoreKey, max(Score)

                                from score_table

                                group by Score");

while( $max_score = mysql_fetch_array($score)) {

      $LastName = $max_score['LastName'];

      $ScoreKey  = $max_score['ScoreKey'];

      mysql_query("delete from score_table

                          where LastName = '$LastName'

                          and ScoreKey != $ScoreKey ");

}

 

not sure wd the syntax...but hop ul get the idea from this....

godblez...