Page 1 of 1

[UPDATED] Get the position of a result in an SELECT ORDER BY

Posted: Sat Dec 15, 2007 11:54 pm
by Josh1billion
Say I have a query:

Code: Select all

$result = mysql_query("SELECT `name` FROM `people` ORDER BY `age` DESC");
That will order "people" by their ages, descending. So the first result will be the oldest person, second result will be the second-oldest person, etc.

Is there any simple way to determine which rank (X-oldest person) an entry with a certain `name` will be? Example, find out that "Bob" is the 32nd-oldest person? The only way I could think of doing it is like this:

Code: Select all

$i = 1;
while ($current_person = mysql_fetch_array($result))
{
    if ($current_person['name'] == "Bob")
        break;
    $i++;
}
print "Bob is the $i oldest person";
But of course, that would be inefficient and slow. There should be another, better/faster way.. so if there is, what is it?

Posted: Mon Dec 17, 2007 4:31 pm
by Josh1billion
...Anyone know if this is possible?

Posted: Mon Dec 17, 2007 4:52 pm
by John Cartwright

Posted: Mon Dec 17, 2007 5:45 pm
by Josh1billion
Thanks, that worked perfectly. :) I took that code and then added in a WHERE clause and it worked.

Here is some sample code for anyone trying to learn how to do this:

Code: Select all

$result = mysql_query("SELECT users1.name, users1.age, COUNT(users2.age) AS Rank
			FROM users users1 
			JOIN users users2 ON users1.age < users2.age OR (users1.age=users2.age and users1.name = users2.name)
                        WHERE users1.name='Bob'
			GROUP BY users1.name, users1.age 
			ORDER BY users1.age DESC, users1.name DESC
			;
			
			") or die ("error 3: " . mysql_error());
	
	$result2 = mysql_fetch_array($result);
	print "Bob is the " . $result2['Rank'] . " oldest person<BR>";
Quite a complicated-looking query, but this will work when you have a table named users with the columns name and age. It will tell you that Bob is the ___ oldest person in the table (stored in $result2['Rank'] on the last couple of lines of the PHP code).

Posted: Mon Dec 17, 2007 6:00 pm
by Benjamin
Interesting. It performs a count with a subquery. I think if I was going to do this I would store the results in an array and use the numeric array index. You could still calculate it accurately with pagination by doing some simple math and I think it would be less resource intensive.

I also wonder if there is a way to do this with a stored procedure of some sort that would be more efficient.

Posted: Mon Dec 17, 2007 6:35 pm
by Josh1billion
Hm...

Does anyone know how I could change that last query to add "gender" into the factor? So that it would take a field called "gender" and only deal with users where gender='Male'

I tried throwing a WHERE clause around a few times, and a bunch of other combinations, but nothing I tried worked.

Posted: Wed Dec 19, 2007 12:44 am
by Josh1billion
Anyone know in regards to my last reply?