How can I do this with MySQL??

[Vivid Lust]

Thanks for the help if you can with my little problem below.

 

I really have no idea how do create this query in MySQL.

 

What I want to do:

 

Order table so that the greatest 'score' is at the top of the table. Find the position of X id in that table

 

Any help. appreciated...

 

[toplay]

I follow you on the first part, but lost you on the second part.

 

select * from table_name order by `score` desc

 

or maybe you mean:

 

select * from table_name where `id` = 'x' order by `score` desc  # Assuming multiple rows returned

 

 

Be a bit more clear when posting. Thanks.

 

 

[Vivid Lust]

Ok, let me re-explain XD

 

Say we had this table:

 

id    score

1        5

2        8

3        16

4        25

5        4

 

I want MySQL to re-order the table so it looks like this:

 

id    score

4        25

3        16

2        8

1        5

5        4

 

Then, say I wanted to know the "rank" or id 1, i would like to have mysql query to find how far down from the top it is, so the position of 1, would be 4th, or 4

 

Thanks if you can help.

[toplay]

Use this query: select * from table_name order by `score` desc

 

and as you read each row from the table keep a counter and use that to display the ranking.

[Vivid Lust]

What/how do you mean keep a counter? Please note that I'm an absolute No0b...

[fenway]

With user variables...

[Vivid Lust]

With user variables...

What do you mean?

[toplay]

// run the query: select * from table_name order by `score` desc

// check for errors

 

$rank = 0;

while ($row = mysql_fetch_array($result)) {

    $rank++;  // adds 1

    echo 'Rank: ', $rank, ' Score: ', $row['score']; // put HTML break to end line

}

 

[Vivid Lust]

Wouldnt that echo all of the ranks???

Cant I just get the value of the rank of X id ???

[toplay]

Just put an 'if' before the echo of which ID you want to display. Example:

 

$id_looking_for = 1;

 

// run the query: select * from table_name order by `score` desc

// check for errors

 

$rank = 0;

while ($row = mysql_fetch_array($result)) {

    $rank++;  // adds 1

    if ($id_looking_for == $row['id']) {

        echo 'Rank: ', $rank, ' Score: ', $row['score']; // put HTML break to end line

        break;  // get out of loop

    }

}

[fenway]

With user variables...

What do you mean?

Increment a @rownum variable in your query, like this:

 

if(@a, @a:=@a+1, @a:=1)-1 as rownum