SQL: Compute the number of test scores that are LOWER?

[mrherman]

Mysql table of about 2000 rows.

 

How would one write an SQL statement that does the following in a table of test scores?

 

For each record (student), compute the number of records in the table that had a test score LOWER than the score achieved by the student, and UPDATE the table with the "LOWER" data for each student.

 

Example

SCORE    LOWER

50        56

90        1700

80        1132

...      ...

 

Of course, I want to use PHP (which I'm struggling to learn) to do this work, so a point or two there would hurt either. 

 

Thanks!!

[Third_Degree]

<?php

$score = mysql_result( mysql_query( "SELECT score FROM students WHERE `name`='joe'" ), 0 );
$lower = mysql_query( "SELECT * FROM students WHERE `score` < '" . $score . "'");
mysql_query( "UPDATE `students` SET `lower` = '" . mysql_num_rows( $lower ) . "' WHERE `name`='joe'" );

?>

 

Obviously without knowledge of your db structure, i had to make some things up that you will need to change accordingly. Hope this helps.

[mrherman]

Thank you so much for the code!  I'm working through it now, trying to understand how it works.

 

I'm especially interested in "mysql_result."  I have not used this before.

 

Thanks again!!

 

 

[fenway]

First, you can do all of that in single query -- second, why not COUNT()?