Ranking

aarontan78

Hi all,
I have a table with 3 fields: Name, score value1 and score value2 and it is ORDER BY Name.

I want php to rank the name base on the score value1 like:

Name Score Value1 Score Value2 Rank
Jane 8.2 3.2 2
Kent 9.2 3.2 1
Mary 5.4 9.3 3

How can I create the ranking like above?
If exist 2 same score value1, I would like it to look into score value2 to determine the ranking.
How can I do that?

Thanks.

justsomeone

A table isn't "ORDER BY Name". Name may be the primary index on the table (and it shouldn't be) but that's different.

"ORDER BY" is a clause on the SELECT statement. So, you could just insert "ORDER BY Value1 ASC, Value2 ASC" - asuming that the field names are Value1 and Value 2. "ASC" tells MySQL that you want the rows sorted in ascending order, (3.2 apears before 9.3). If you would like to start with the bigger values use "DESC" instead of "ASC".

aarontan78

Thanks for the reply. But how do I insert the RANK field automatically to the table? and have it ORDER BY name?

justsomeone

What do you mean by "the RANK field"? I don't understand. If you want to add a field to a table look up the "ALTER TABLE" command in MySQL. Once you have the field added, you can insert values into the table using the "UPDATE" command in MySQL. Finally, if you want your query ordered by name, add the clause "ORDER BY Name" into your SELECT statement.

If you don't understand any of this, then you need to read a good book on building a mysql database.

aarontan78

Thanks again for the reply.
OK let say I have all the above fields in my table.
How do I update the RANK field automatically whenever a new record (new score) is inserted?