selecting rank over time

[cedsn]

I have 4 colums in my table.. they are id(int), usrid (int), datestamp (date) and credits(float).

here's what i'd like to do.. select the numeric rank of a given userid over the past 30 days based on the most credits... ideally i'd like it all to be in one query but that may not be possible.. any ideas are welcome here, i'm stumped on this one.. so far all i've been able to do is select all the entries from the last 30 days..


SELECT *
FROM daycredit
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datestamp ) <= 30
and usrid = $usrid LIMIT 0, 30

[raf]

Hmmm.

SELECT userid, sum(credits) as totcredits30
FROM daycredit GROUP BY userid
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datestamp ) <= 30
ORDER BY totcredits30 DESC

Will return a recordset with the total credits of the last 30 days per userid. The highest on top and then further on descending.

You can then loop through the recordset and count the recordst you need to descend to get to the userid you want the position of. So you need an incrementng counter and somethin like


$i=1;
while ($row=mysql_fetch_array($result)){
if ($row['userid'] == $usrid ){
echo ('User is number' . $i . 'from the last 30 days. Total credits in this period=' . $row[totcredits30]);
break;
}
$i ++;
}