[SOLVED] MYSQL ranking calculation

[SirChick]

Hey i am wondering if some thing like the below is possible and how it is achieved, as im having difficults of trying to find an efficient way to do this.

 

Imagine a table of names and numbers in order:

 

John :  134

Sarah : 463

Dave : 1000

Steve : 500

What im trying to do is make a query to sort them firstly in order by the numbers ( I can do this by ORDER BY DESC simple enough) once this is done the names should be in place:

 

1) Dave : 1000

2) Steve : 500

3) Sarah : 463

4) John :  134

 

With this sorted, im trying to return from the query only the position they came out the list.. so for example if i queried for Sarah it would reply with "3" or Dave it would reply "1".

 

What can i do to achieve this in MYSQL?

 

 

 

 

 

 

[Ken2k7]

Well if position is determined by how many are before it, then you can do this -

 

SELECT COUNT(*) + 1 AS position
FROM tablename t1
LEFT OUTER JOIN tablename t2
     ON t1.num < t2.num
WHERE t1.name = 'Sarah';

 

That should return the position Sarah is, which is 3.

[SirChick]

Im confused on why there is a + 1 and the left outer join when there is only one table in this situation ?

[haku]

Both those questions are answerable with one answer. What this query does is checks the number of rows in the table, that are less than the value of the row you are looking at. You do this through the join - first you find the row in question, then you check that same table for the number of rows less than that value. And since the number of rows will be one less than the position, you add one to the number of rows to get the position.

[Ken2k7]

Im confused on why there is a + 1 and the left outer join when there is only one table in this situation ?

haku sort of gave the answer to the +1 except it check for the number of rows in the table that are *more* than the row selected, not less than.

 

As for the LEFT OUTER JOIN, it joins the same table. You just replace tablename with the table name so you should replace both instances of tablename with the actual table name. And replace num with the column name for the scores. Then replace the name with the column name for the name of the member and change the name as well.

 

It's doing a JOIN on the same table. I need that to calculate how many people are before the person you want the rank for.

[fenway]

More about this here.

[SirChick]

Hmm with only one row in the database it retuns "2"  which is not making sense for me...

 

This is the table:

name | Rank | ID

Dave | 1000 | 14

 

<?php
function position($Field,$ID){
$Get = mysql_query("SELECT COUNT(t1.$Field) + 1 AS position FROM listings t1
			LEFT OUTER JOIN listings t2
					ON t1.$Field < t2.$Field
			WHERE t1.ID = '$ID'")	
				Or die(mysql_error());
$row = mysql_fetch_assoc($Get);
Echo $row['position'];
}

//query to get ID etc etc
//row fetch assoc

$ID = $row['ID'];
Echo position('Rank',$ID).'<br>';
?>

 

 

The returned result is :

2

 

But theres only one row in the table ?

 

[SirChick]

bump

[fenway]

COUNT(*) = 1

plus 1 = 2.

 

Where's the confusion?

[SirChick]

Well if theres only one row in the table and it returns 2 means your 2nd out of one entry which is illogical in rank aspects..so if i removed the plus 1 ? Wouldn't that make the positions correct ?

[Ken2k7]

Well if you're going to do that, change COUNT(t1.$Field) to COUNT(t2.$Field).

[EchoFool]

EDIT : wrong thread please delete . Thanks

[SirChick]

Well if you're going to do that, change COUNT(t1.$Field) to COUNT(t2.$Field).

 

Cheers for that!

 

Thanks for the help guys!