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?